Re: Totals on change
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 26 Aug 2006 20:35:04 +0000 (UTC)
brm6546545 (russell.allbritain@xxxxxxxxx) writes:
I have a tblTax. It has fields InvoiceNum, TaxAuthority, TaxableTotal,inv,auth,Taxable,nonTaxable,Collected,TaxableTot,NonTaxableTot,CollectedTot
NonTaxableTotal, TaxCollected.
Sample data
1,county,10.00,0.00,0.40
1,city,10.00,0.00,0.10
2,state,0.00,15.00,0.15
When totaling invoice 1 should have totals of 10.00,0.00,0.50 because
the 10.00 is the total for the invoice, but 0.50 is the total tax
collected. I nee these totals in a report. In crystal reports i could
just do a total on change of invoice number for the Taxable and
nonTaxable Totals. but i have to this on an Access adp. I was hoping i
could get a query to return something like
1,county,10.00,0.00,0.40,10.00,0.00,0.50
1,city,10.00,0.00,0.10,10.00,0.00,0.50
2,state,0.00,15.00,0.15,0.00,15.00,0.15
SELECT InvoiceNum, TaxAuthority, TaxableTotal,
NonTaxableTotal, TaxCollected,
CollectedTot = SUM(TaxCollected) OVER (PARTITION BY InvoiceNum),
GrandCollected = SUM(TaxCollected) OVER ()
FROM tblTax
I did not include TaxabltTotal and NonTaxableTotal, since I don't really
see the point with them. Can the taxable amount be different for different
authorities? In such case, I don't know the business rules to compute
them.
The solution requires SQL 2005.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Totals on change
- From: brm6546545
- Totals on change
- Prev by Date: Re: joins
- Next by Date: Re: Primary key generating
- Previous by thread: Re: Totals on change
- Next by thread: SSIS Standard Edition and Script Task
- Index(es):
Relevant Pages
|
