Sum Only Subset Records Included in Max Sale Date

[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;

 

[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;

 

[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

 

[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;

 

[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

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.

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

 

[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.

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

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.

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;