If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
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; |
#2
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
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. "dallin" wrote: 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; |
#3
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
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. "dallin" wrote: 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; |
#4
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
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. "dallin" wrote: 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; |
#5
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
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" wrote: 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. "dallin" wrote: 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; |
#6
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
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" wrote: 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. "dallin" wrote: 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; |
#7
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
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" wrote: 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. "dallin" wrote: 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; |
#8
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
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" wrote: 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" wrote: 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. "dallin" wrote: 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; |
#9
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
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" wrote: 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" wrote: 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. "dallin" wrote: 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; |
#10
|
|||
|
|||
Sum Only Subset Records Included in Max Sale Date
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" wrote: 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" wrote: 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. "dallin" wrote: 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; |
|
Thread Tools | |
Display Modes | |
|
|