Can/how sql sum returned rows then work out % of total on them?

[Markie76]

I am using a propriatetry product like MS reporting services to create an invoice.
Users enter an invoice into the system, specify the customer & invoice lines (each line can be tax exempt or assigned one of several tax codes).
Then they run the print process which queries the invoice header table and invoice detail table and retrieves all matching rows for that order/job number (created when you save your invoice entry).
The returned data is then inserted into the report template and produces a nice pdf invoice, showing customer details at the top, then listing the invoice lines showing tax amount, tax rate & net line total, at the bottom the tax is summed and an invoice total displayed.

Now, this is where my abilities end.

I now need to add a tax rate breakdown which breaks down the invoice by tax code and produces a percentage of the total attributable to that tax code. So that the customer can see how much they are spending in each tax bracket.

eg.

TC_____% of invoice total
0______0
1______27
2______64
3______9

My initial thinking was I could run two queries on the invoice detail table;
1. To return the total invoice amount
2. To return the total invoice amount grouped by tax code

Then to do a calculation which does 2/1 for each tax code returned.

But I can't figure out how to assign names to the grouped tax codes returned in the query AND then feed them into the calculation - if its possible to do with assigned field/column names.

So I would very much appreciate any help you can offer on this.

Is my thinking actually possible to do
OR
Is my approach incorrect.

Thanks,

Mark.

[ck9663]

Try this:

 
;With SummaryInvoice(TotalInvoice)
as
(
select count(*) from YourInvoiceTable
)
select TC, count(*), (count(*)/s.TotalInvoice) * 100.00
from YourInvoiceTable i
inner join SummaryInvoice s on 1 =1 
group by TC
 
 

Happy Coding!!!

~~ CK

[Markie76]

Hi ck9663,

Thanks for your response, but that code is way too advanced for me without an explanation.

My best guess is that your code creates a temporary table of all invoice data producing some sort of totals count?
Then it attempts the % calcualtion on everything?
And finally returns results only on invoices (invoiceID) also in the header table?

I have a feeling its good but its beyond my abilities (currently) to adapt it into our structure. :(

I will have a go and see if I get any results.

[ck9663]

It's called Common Table Expression. It's a way of re-writing sub-queries. If I rewrite that in a sub-query, it'll look something like this:

 
select TC, count(*), (count(*)/s.TotalInvoice) * 100.00
from YourInvoiceTable i
   inner join (select count(*) cnt from YourInvoiceTable) s on 1 = 1 
group by TC
 
 

Happy Coding!!!

~~ CK