October 13, 2009 at 2:21 pm
So I am building an aggregated table of our very large Invoice table. A requirement is to see day by day summaries over the last 14 valid business days (since invoice dates in our invoice table only are business days, I can just aggregate off of those).
To me it seems like I should be able to do this in one select statement, but I can't seem to get it to work outside of the below script.
declare @D14 int,@D13 int,@D12 int,@D11 int,@D10 int,@D9 int,@D8 int,@D7 int,@D6 int,@D5 int,@D4 int,@D3 int,@D2 int,@D1 int;
-- with tbl as (
SELECT TOP 14 InvoiceDate, ROW_NUMBER() OVER (ORDER BY MAX(invoicedate) DESC) AS RowNumber
FROM dbo.Invoice
WHERE InvoiceDate >= CAST(CONVERT(CHAR(8),GETDATE() - 21,112) AS DECIMAL(8,0))
GROUP BY InvoiceDate
)
Select
@D14 = (Select invoicedate from tbl where rownumber = 1),
@D13 = (Select invoicedate from tbl where rownumber = 2),
@D12 = (Select invoicedate from tbl where rownumber = 3),
@D11 = (Select invoicedate from tbl where rownumber = 4),
@D10 = (Select invoicedate from tbl where rownumber = 5),
@D9 = (Select invoicedate from tbl where rownumber = 6),
@D8 = (Select invoicedate from tbl where rownumber = 7),
@d7 = (Select invoicedate from tbl where rownumber = 8),
@D6 = (Select invoicedate from tbl where rownumber = 9),
@D5 = (Select invoicedate from tbl where rownumber = 10),
@D4 = (Select invoicedate from tbl where rownumber = 11),
@D3 = (Select invoicedate from tbl where rownumber = 12),
@D2 = (Select invoicedate from tbl where rownumber = 13),
@D1 = (Select invoicedate from tbl where rownumber = 14)
Link to my blog http://notyelf.com/
October 13, 2009 at 2:41 pm
What problem are you having with it? I just tested a variation (since I don't have your table or your data, I can't test exactly what you're testing), and it worked.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2009 at 2:44 pm
Oh this works fine, I was just trying to get this done in one select statement vs doing a CTE followed up by 14 sub queries.
Link to my blog http://notyelf.com/
October 13, 2009 at 2:46 pm
You could use the Pivot operator to turn the rows into columns and then you'd get rid of the sub-queries. I don't see a way to get rid of the CTE.
If I knew what the variables were going to be used for, I might be able to suggest something more efficient, but without some context, I'd be shooting in the dark.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2009 at 3:31 pm
Understandable.
This is a portion of the select statement using these columns. We will be building reports, and these columns represent the last 14 business days, and they are summarizing total net sales per day
select
SUM(CASE WHEN fsic.invoicedate = @D1 THEN fsic.netline_amount else 0 end ) as D1_Net,
SUM(CASE WHEN fsic.invoicedate = @D2 THEN fsic.netline_amount else 0 end ) as D2_Net,
SUM(CASE WHEN fsic.invoicedate = @D3 THEN fsic.netline_amount else 0 end ) as D3_Net,
SUM(CASE WHEN fsic.invoicedate = @D4 THEN fsic.netline_amount else 0 end ) as D4_Net,
SUM(CASE WHEN fsic.invoicedate = @D5 THEN fsic.netline_amount else 0 end ) as D5_Net,
SUM(CASE WHEN fsic.invoicedate = @D6 THEN fsic.netline_amount else 0 end ) as D6_Net,
SUM(CASE WHEN fsic.invoicedate = @d7 THEN fsic.netline_amount else 0 end ) as D7_Net,
SUM(CASE WHEN fsic.invoicedate = @D8 THEN fsic.netline_amount else 0 end ) as D8_Net,
SUM(CASE WHEN fsic.invoicedate = @D9 THEN fsic.netline_amount else 0 end ) as D9_Net,
SUM(CASE WHEN fsic.invoicedate = @D10 THEN fsic.netline_amount else 0 end ) as D10_Net,
SUM(CASE WHEN fsic.invoicedate = @D11 THEN fsic.netline_amount else 0 end ) as D11_Net,
SUM(CASE WHEN fsic.invoicedate = @D12 THEN fsic.netline_amount else 0 end ) as D12_Net,
SUM(CASE WHEN fsic.invoicedate = @D13 THEN fsic.netline_amount else 0 end ) as D13_Net,
SUM(CASE WHEN fsic.invoicedate = @D14 THEN fsic.netline_amount else 0 end ) as D14_Net
from dbo.Invoice AS FSIC
Link to my blog http://notyelf.com/
October 13, 2009 at 6:59 pm
Do you mean something like this?
SELECT
sum(case when invoiceDate = CAST(CONVERT(CHAR(8),GETDATE(),112) AS DECIMAL(8,0)) then 1 else 0 end) as d1
,sum(case when invoiceDate = CAST(CONVERT(CHAR(8),GETDATE()-1,112) AS DECIMAL(8,0)) then 1 else 0 end) as d2
,sum(case when invoiceDate = CAST(CONVERT(CHAR(8),GETDATE()-2,112) AS DECIMAL(8,0)) then 1 else 0 end) as d3
,sum(case when invoiceDate = CAST(CONVERT(CHAR(8),GETDATE()-3,112) AS DECIMAL(8,0)) then 1 else 0 end) as d4
-- etc, etc.
,sum(case when invoiceDate = CAST(CONVERT(CHAR(8),GETDATE()-13,112) AS DECIMAL(8,0)) then 1 else 0 end) as d14
FROM dbo.Invoice
WHERE InvoiceDate >= CAST(CONVERT(CHAR(8),GETDATE() - 21,112) AS DECIMAL(8,0)) -- Why -21?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 13, 2009 at 7:10 pm
If bob's solution doesn't work, can't you just join invoice to your CTE and use RowNumber in your case statements instead of the variables you're assigning now? No access to a server atm to test that out, but it seems like it should work fine.
October 14, 2009 at 4:10 pm
Bob,
I don't do that because that would include non-business days. the -21 was just a filter to cut off a large portion of the table that wouldnt be used. No matter what day it is, if you subtract 21 from today, you will always have 14 business days within that range. Just a quick fix filter.
I will give the joining of the CTE a try thanks Garadin 🙂
Link to my blog http://notyelf.com/
October 14, 2009 at 4:34 pm
So the Join ended up doubling the time it took to run, but I did find a single query solution to assign the values outside of a CTE :-D. It's also dynamic!
SELECT
@D14 = [1]
,@D13 = [2]
,@D12 = [3]
,@D11 = [4]
,@D10 = [5]
,@D9 = [6]
,@D8 = [7]
,@D7 = [8]
,@D6 = [9]
,@D5 = [10]
,@D4 = [11]
,@D3 = [12]
,@D2 = [13]
,@D1 = [14]
FROM
(SELECT TOP 14 InvoiceDate, ROW_NUMBER() OVER (ORDER BY MAX(invoicedate) DESC) AS RowNumber FROM dbo.Invoice
WHERE InvoiceDate >= CAST(CONVERT(CHAR(8),GETDATE() - 21,112) AS DECIMAL(8,0)) GROUP BY InvoiceDate) AS T1
PIVOT (MAX(InvoiceDate) FOR RowNumber IN ([1], [2], [3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14])) AS PT
Link to my blog http://notyelf.com/
October 14, 2009 at 4:43 pm
Thanks for the explanation Shannon.
I'm glad you found your solution with PIVOT, but keep the crosstab (CASE) solution in mind if performance becomes an issue. Crosstab solutions sometimes run a little quicker than PIVOTs.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 14, 2009 at 4:45 pm
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply