Sum Only Subset Records Included in Max Sale Date

D

dallin

I have the following query: The sum(taxcredit) is not working -- it is
summing all records by PED (period end date) where as I only want it to sum
the records associated with the sale date that equals Max(Sale_Date). How
can I do this?

SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED,
Max(TaxCredit.SALE_DATE) AS Sdate, Sum(TaxCredit.TaxCredit) AS TaxCredit
FROM TaxCredit
GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE
ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE;
 
K

KARL DEWEY

You can not have details (PED) and total in same query so change like this --
SELECT TaxCredit.COMPANY_ID AS Co, Max(TaxCredit.SALE_DATE) AS Sdate,
Sum(TaxCredit.TaxCredit) AS TaxCredit
FROM TaxCredit
GROUP BY TaxCredit.COMPANY_ID
ORDER BY TaxCredit.COMPANY_ID;
 
D

dallin

I see your point; however, I need to subtotal by period end date any sale
date record that is 60 days less than the period end date: example
Co PED Sale date TaxCredit
1 12/31/08 10/31/08 15.00
1 12/31/08 10/31/08 12.00
1 12/31/08 09/30/08 5.00
9 01/31/09 11/30/08 10.15
9 01/31/09 11/30/08 21.15
9 01/31/09 09/30/08 10.02

Result should look like:
Co PED SaleDate TaxCredit
1 12/31/08 10/31/08 27.00
9 01/31/09 11/30/08 31.30
 
K

KARL DEWEY

Try this --
SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED,
(SELECT TOP 1 [XX].SALE_DATE FROM TaxCredit AS [XX] WHERE [XX].SALE_DATE <=
TaxCredit.REPORT_DATE ORDER BY [XX].SALE_DATE DESC) AS Sdate,
Sum(IIF(TaxCredit.REPORT_DATE Between AND DateAdd("d",
-60,TaxCredit.REPORT_DATE), TaxCredit.TaxCredit, 0)) AS TaxCredit
FROM TaxCredit
GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE
ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE;
 
D

dallin

Wow -- Nice formula! Could you help me understand it a bit more. What does
the [XX] do? I get a syntax error on the Sum(iif formula at the AND point?
I would have thought you would sumiif on the SALE_DATE not REPORT_DATE --
please explain. Thanks

KARL DEWEY said:
Try this --
SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED,
(SELECT TOP 1 [XX].SALE_DATE FROM TaxCredit AS [XX] WHERE [XX].SALE_DATE <=
TaxCredit.REPORT_DATE ORDER BY [XX].SALE_DATE DESC) AS Sdate,
Sum(IIF(TaxCredit.REPORT_DATE Between AND DateAdd("d",
-60,TaxCredit.REPORT_DATE), TaxCredit.TaxCredit, 0)) AS TaxCredit
FROM TaxCredit
GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE
ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE;

--
Build a little, test a little.


dallin said:
I see your point; however, I need to subtotal by period end date any sale
date record that is 60 days less than the period end date: example
Co PED Sale date TaxCredit
1 12/31/08 10/31/08 15.00
1 12/31/08 10/31/08 12.00
1 12/31/08 09/30/08 5.00
9 01/31/09 11/30/08 10.15
9 01/31/09 11/30/08 21.15
9 01/31/09 09/30/08 10.02

Result should look like:
Co PED SaleDate TaxCredit
1 12/31/08 10/31/08 27.00
9 01/31/09 11/30/08 31.30
Click to expand...
Click to expand...
 
K

KARL DEWEY

sumiif is an Excel function. [XX] is an alias for TaxCredit as I am using
the same table twice but not the same records at the same time.

I found an error in field name - had TaxCredit.TaxCredit and should have
been TaxCredit.REPORT_DATE. Try it now --
SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED, (SELECT TOP
1 [XX].SALE_DATE FROM TaxCredit AS [XX] WHERE [XX].SALE_DATE <=
TaxCredit.REPORT_DATE ORDER BY [XX].SALE_DATE DESC) AS Sdate,
Sum(IIF(TaxCredit.REPORT_DATE Between AND DateAdd("d", -60,
TaxCredit.REPORT_DATE), TaxCredit.REPORT_DATE, 0)) AS TaxCredit
FROM TaxCredit
GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE
ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE;

--
Build a little, test a little.


dallin said:
Wow -- Nice formula! Could you help me understand it a bit more. What does
the [XX] do? I get a syntax error on the Sum(iif formula at the AND point?
I would have thought you would sumiif on the SALE_DATE not REPORT_DATE --
please explain. Thanks

KARL DEWEY said:
Try this --
SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED,
(SELECT TOP 1 [XX].SALE_DATE FROM TaxCredit AS [XX] WHERE [XX].SALE_DATE <=
TaxCredit.REPORT_DATE ORDER BY [XX].SALE_DATE DESC) AS Sdate,
Sum(IIF(TaxCredit.REPORT_DATE Between AND DateAdd("d",
-60,TaxCredit.REPORT_DATE), TaxCredit.TaxCredit, 0)) AS TaxCredit
FROM TaxCredit
GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE
ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE;

--
Build a little, test a little.


dallin said:
I see your point; however, I need to subtotal by period end date any sale
date record that is 60 days less than the period end date: example
Co PED Sale date TaxCredit
1 12/31/08 10/31/08 15.00
1 12/31/08 10/31/08 12.00
1 12/31/08 09/30/08 5.00
9 01/31/09 11/30/08 10.15
9 01/31/09 11/30/08 21.15
9 01/31/09 09/30/08 10.02

Result should look like:
Co PED SaleDate TaxCredit
1 12/31/08 10/31/08 27.00
9 01/31/09 11/30/08 31.30


:

You can not have details (PED) and total in same query so change like this --
SELECT TaxCredit.COMPANY_ID AS Co, Max(TaxCredit.SALE_DATE) AS Sdate,
Sum(TaxCredit.TaxCredit) AS TaxCredit
FROM TaxCredit
GROUP BY TaxCredit.COMPANY_ID
ORDER BY TaxCredit.COMPANY_ID;

--
Build a little, test a little.


:

I have the following query: The sum(taxcredit) is not working -- it is
summing all records by PED (period end date) where as I only want it to sum
the records associated with the sale date that equals Max(Sale_Date). How
can I do this?

SELECT TaxCredit.COMPANY_ID AS Co, TaxCredit.REPORT_DATE AS PED,
Max(TaxCredit.SALE_DATE) AS Sdate, Sum(TaxCredit.TaxCredit) AS TaxCredit
FROM TaxCredit
GROUP BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE
ORDER BY TaxCredit.COMPANY_ID, TaxCredit.REPORT_DATE;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum means empty query? 5
SUM NOT INCLUDING ALL RECORDS 4
Calculate Days between dates of a sale for a particular product 2
How do I select the maximum of a subset of records? 4
Min/Max Date only sorting by months? 0
Last records from a Group By query 1
Returning only MAX(date) not all dates 7
Include records even if date is null 4

Top