November 21, 2007 at 10:29 am
I have a table and here is a create and insert statement that will show a sample of the infomormation that is being stored in it:
CREATE TABLE #tmpreciepts (
cfrid int IDENTITY(1,1) NOT NULL,
prediction_date_startdate datetime NULL,
prediction_date_enddate datetime NULL,
prediction_week_startdate datetime NULL,
prediction_week_enddate datetime NULL,
prediction_week_number int NULL,
prediction_value money NULL,
actuals money NULL
) ON [PRIMARY]
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '4/22/2007 0:00', '4/28/2007 0:00', 1, 74140, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '4/29/2007 0:00', '5/5/2007 0:00', 2, 163622, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '5/6/2007 0:00', '5/12/2007 0:00', 3, 175397, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '5/13/2007 0:00', '5/19/2007 0:00', 4, 278627, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '5/20/2007 0:00', '5/26/2007 0:00', 5, 219463, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '5/27/2007 0:00', '6/2/2007 0:00', 6, 77807, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '6/3/2007 0:00', '6/9/2007 0:00', 7, 100454, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '6/10/2007 0:00', '6/16/2007 0:00', 8, 101930, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '6/17/2007 0:00', '6/23/2007 0:00', 9, 101080, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '6/24/2007 0:00', '6/30/2007 0:00', 10, 242439, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '7/1/2007 0:00', '7/7/2007 0:00', 11, 142411, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '7/8/2007 0:00', '7/14/2007 0:00', 12, 195337, 193113)
INSERT into #tmpreciepts VALUES ('4/22/2007 0:00', '4/28/2007 0:00', '7/15/2007 0:00', '7/21/2007 0:00', 13, 426583, 193113)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '4/29/2007 0:00', '5/5/2007 0:00', 1, 123535, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '5/6/2007 0:00', '5/12/2007 0:00', 2, 122371, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '5/13/2007 0:00', '5/19/2007 0:00', 3, 271529, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '5/20/2007 0:00', '5/26/2007 0:00', 4, 220594, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '5/27/2007 0:00', '6/2/2007 0:00', 5, 108376, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '6/3/2007 0:00', '6/9/2007 0:00', 6, 92848, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '6/10/2007 0:00', '6/16/2007 0:00', 7, 117516, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '6/17/2007 0:00', '6/23/2007 0:00', 8, 94386, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '6/24/2007 0:00', '6/30/2007 0:00', 9, 232713, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '7/1/2007 0:00', '7/7/2007 0:00', 10, 122097, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '7/8/2007 0:00', '7/14/2007 0:00', 11, 177035, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '7/21/2007 0:00', '7/15/2007 0:00', 12, 212996, 153320)
INSERT into #tmpreciepts VALUES ('4/29/2007 0:00', '5/5/2007 0:00', '7/22/2007 0:00', '7/28/2007 0:00', 13, 212982, 153320)
I have stared to create the following scrip that for each predicition end date will give me the sum of the prediciton for 1 week, 1 and 2 weeks, etc. So, in the end it will give me all of prediction totals for a given prediction week out to 13 weeks. Here is the beginning of that script:
SELECT tr.prediction_date_enddate,
SUM(CASE tr.prediction_week_number WHEN 1 THEN tr.prediction_value END) AS [1 Week],
SUM(CASE tr.prediction_week_number WHEN 1 THEN tr.prediction_value END) + SUM(CASE tr.prediction_week_number WHEN 2 THEN tr.prediction_value END) AS [2 Week]
FROM #tmpreciepts tr
GROUP BY tr.prediction_date_enddate
My question simply is, is this the correct way to go about it. I will have to construct a lot more to get out to the full 1 - 13 weeks and then I am going to do some comparisons as well to the actual to get the actual dollar amout difference as well as percentage difference. I should be happy that this returns what I want but I just have this nagging feeling that there is a better/more efficient way to do this.
Maybe I'm overthinking this but I thought it would't hurt to throw it out there and see what comes up.
Thanks
November 21, 2007 at 11:00 am
OK, the code below should get you what you've described.
First we use a correlated sub query to get the cumulative (progress) values and I put that into a CTE (common table expression) just for ease on the ease.
Now it gets complicated because you can only pivot one column. So we'll need to do some fancy work.
I'm posting the first part and will wrap everything together shortly....
WITH smy
AS (
-- first we wrap a query to return the progressive totals in a CTE, just for ease on the eyes
SELECT
prediction_date_enddate,
prediction_week_number,
progressiveTotal = (SELECT SUM(tr.prediction_value)
FROM #tmpreciepts tr
WHERE tr.prediction_week_number <= t.prediction_week_number
AND tr.prediction_date_enddate = t.prediction_date_enddate)
FROM #tmpreciepts t
)
-- now we'll pivot that result set
SELECT
prediction_date_enddate,
[1] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7] ,[8] ,[9] ,[10] ,[11] ,[12] ,[13]
FROM
(SELECT -- select everything we need here
prediction_date_enddate,
prediction_week_number,
progressiveTotal
FROM
smy) AS AA
PIVOT
(SUM(progressiveTotal) -- value to pivot from vertical rows/records to horizontal columns
FOR prediction_week_number IN -- what field defines a new column
([1] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7]
,[8] ,[9] ,[10] ,[11] ,[12] ,[13]) -- the column names in brackets
) as BB
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 21, 2007 at 11:14 am
Try this using the sample data you posted.
WITH raw_data
AS (
-- first we wrap a query to return the progressive totals in a CTE, just for ease on the eyes
SELECT
prediction_date_enddate,
prediction_week_number,
progressiveTotal = (SELECT SUM(tr.prediction_value)
FROM #tmpreciepts tr
WHERE tr.prediction_week_number <= t.prediction_week_number
AND tr.prediction_date_enddate = t.prediction_date_enddate),
actuals
FROM #tmpreciepts t
),
-- now we'll pivot the predictions
predictions
AS (SELECT
prediction_date_enddate,
[1] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7] ,[8] ,[9] ,[10] ,[11] ,[12] ,[13]
FROM
(SELECT -- select everything we need here
prediction_date_enddate,
prediction_week_number,
progressiveTotal
FROM
raw_data) AS AA
PIVOT
(SUM(progressiveTotal) -- value to pivot from vertical rows/records to horizontal columns
FOR prediction_week_number IN -- what field defines a new column
([1] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7]
,[8] ,[9] ,[10] ,[11] ,[12] ,[13]) -- the column names in brackets
) as BB
),
-- now we'll pivot the actuals
actuals
AS (SELECT
prediction_date_enddate,
[1] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7] ,[8] ,[9] ,[10] ,[11] ,[12] ,[13]
FROM
(SELECT -- select everything we need here
prediction_date_enddate,
prediction_week_number,
actuals
FROM
raw_data) AS AA
PIVOT
(SUM(actuals) -- value to pivot from vertical rows/records to horizontal columns
FOR prediction_week_number IN -- what field defines a new column
([1] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7]
,[8] ,[9] ,[10] ,[11] ,[12] ,[13]) -- the column names in brackets
) as BB
)
-- now put this all together
SELECT
p.prediction_date_enddate,
p.[1] AS [Prediction Week 1], a.[1] AS [Actuals Week 1], p.[1] - a.[1] AS [Difference Week 1], p.[1] / a.[1] AS [Percent Week 1],
p.[2] AS [Prediction Week 2], a.[2] AS [Actuals Week 2], p.[2] - a.[2] AS [Difference Week 2], p.[2] / a.[2] AS [Percent Week 2],
p.[3] AS [Prediction Week 3], a.[3] AS [Actuals Week 3], p.[3] - a.[3] AS [Difference Week 3], p.[3] / a.[3] AS [Percent Week 3],
p.[4] AS [Prediction Week 4], a.[4] AS [Actuals Week 4], p.[4] - a.[4] AS [Difference Week 4], p.[4] / a.[4] AS [Percent Week 4],
p.[5] AS [Prediction Week 5], a.[5] AS [Actuals Week 5], p.[5] - a.[5] AS [Difference Week 5], p.[5] / a.[5] AS [Percent Week 5],
p.[6] AS [Prediction Week 6], a.[6] AS [Actuals Week 6], p.[6] - a.[6] AS [Difference Week 6], p.[6] / a.[6] AS [Percent Week 6],
p.[7] AS [Prediction Week 7], a.[7] AS [Actuals Week 7], p.[7] - a.[7] AS [Difference Week 7], p.[7] / a.[7] AS [Percent Week 7],
p.[8] AS [Prediction Week 8], a.[8] AS [Actuals Week 8], p.[8] - a.[8] AS [Difference Week 8], p.[8] / a.[8] AS [Percent Week 8],
p.[9] AS [Prediction Week 9], a.[9] AS [Actuals Week 9], p.[9] - a.[9] AS [Difference Week 9], p.[9] / a.[9] AS [Percent Week 9],
p.[10] AS [Prediction Week 10], a.[10] AS [Actuals Week 10], p.[10] - a.[10] AS [Difference Week 10], p.[10] / a.[10] AS [Percent Week 10],
p.[11] AS [Prediction Week 11], a.[11] AS [Actuals Week 11], p.[11] - a.[11] AS [Difference Week 11], p.[11] / a.[11] AS [Percent Week 11],
p.[12] AS [Prediction Week 12], a.[12] AS [Actuals Week 12], p.[12] - a.[12] AS [Difference Week 12], p.[12] / a.[12] AS [Percent Week 12],
p.[13] AS [Prediction Week 13], a.[13] AS [Actuals Week 13], p.[13] - a.[13] AS [Difference Week 13], p.[13] / a.[13] AS [Percent Week 13]
FROM
predictions p
LEFT JOIN actuals a
ON p.prediction_date_enddate = a.prediction_date_enddate
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 21, 2007 at 1:14 pm
Jason,
Way more than I expected to get out of the posting. Thank you very much. Now what I'm looking at is the actuals. The way the table was designed was it just repeats the actual for all 13 weeks of the projection. I just moved the actuals to their own table because I think that may be easier. With the test data, what the actuals should actuall show would be as an example for the 4/28/07 ending period would be Week 1 - 193,113, Week 2 - 346,434 (Which is the 193,113 + 153,320). Given the test data I have here that is as far as you can go. Hopefully this is enough to explain.
I'm working on something to take care of this (I'm thinking using DATEADD with weeks or something) but just like with the predictions I feel like I'm going down the wrong path. Any suggestions?
November 21, 2007 at 1:18 pm
ehlinger,
Sorry but I'm going to be off of here until next Monday. It's a holiday weekend here in the US so I'm "checking out" for a few days of NO WORK, NO SQL, NO ANYTHING but relaxation. If you still need help next week, prepare some sample data and examples of what the result should look like and repost.
J
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 21, 2007 at 3:31 pm
I'll be gone for the holiday as well. Get some much needed relaxation and just be a kid with my kids.
Here is a new actuals table with 14 weeks of actuals.
CREATE TABLE #tmpactuals
(caid int IDENTITY(1,1) NOT NULL,
prediction_date_startdate datetime NULL,
prediction_date_enddate datetime NULL,
actuals money NULL
) ON [PRIMARY]
INSERT into #tmpactuals VALUES('4/22/2007 0:00', '4/28/2007 0:00', 193113)
INSERT into #tmpactuals VALUES('4/29/2007 0:00', '5/5/2007 0:00', 153320)
INSERT into #tmpactuals VALUES('5/6/2007 0:00', '5/12/2007 0:00', 122271)
INSERT into #tmpactuals VALUES('5/13/2007 0:00', '5/19/2007 0:00', 80846)
INSERT into #tmpactuals VALUES('5/20/2007 0:00', '5/26/2007 0:00', 110603)
INSERT into #tmpactuals VALUES('5/27/2007 0:00', '6/2/2007 0:00', 89748)
INSERT into #tmpactuals VALUES('6/3/2007 0:00', '6/9/2007 0:00', 116675)
INSERT into #tmpactuals VALUES('6/10/2007 0:00', '6/16/2007 0:00', 140567)
INSERT into #tmpactuals VALUES('6/17/2007 0:00', '6/23/2007 0:00', 555827)
INSERT into #tmpactuals VALUES('6/24/2007 0:00', '6/30/2007 0:00', 119377)
INSERT into #tmpactuals VALUES('7/1/2007 0:00', '7/7/2007 0:00', 73529)
INSERT into #tmpactuals VALUES('7/8/2007 0:00', '7/14/2007 0:00', 101982)
INSERT into #tmpactuals VALUES('7/15/2007 0:00', '7/21/2007 0:00', 129099)
INSERT into #tmpactuals VALUES('7/22/2007 0:00', '7/28/2007 0:00', 178279)
The results should look something like this: (Well not exactly like this. I transposed the info for readability on the post. Obviously it would be the columns with 2 rows of data based on the test data)
[font="COURIER NEW"]Columns: Row 1, Row 2
prediction_date_enddate 4/28/2007 0:00, 5/5/2007 0:00
Prediction Week 1: 74140, 123535
Actuals Week 1: 193113, 153320
Difference Week 1: 118973, 29785
Percent Week 1: 260%, 124%
Prediction Week 2: 237762, 245906
Actuals Week 2: 346433, 275591
Difference Week 2: 108671, 29685
Percent Week 2: 146%, 112%
Prediction Week 3: 413159, 517435
Actuals Week 3: 468704, 356437
Difference Week 3: 55545, -160998
Percent Week 3: 113%, 69%
Prediction Week 4: 691786, 738029
Actuals Week 4: 549550, 467040
Difference Week 4: -142236, -270989
Percent Week 4: 79%, 63%
Prediction Week 5: 911249, 846405
Actuals Week 5: 660153, 556788
Difference Week 5: -251096, -289617
Percent Week 5: 72%, 66%
Prediction Week 6: 989056, 939253
Actuals Week 6: 749901, 673463
Difference Week 6: -239155, -265790
Percent Week 6: 76%, 72%
Prediction Week 7: 1089510, 1056769
Actuals Week 7: 866576, 814030
Difference Week 7: -222934, -242739
Percent Week 7: 80%, 77%
Prediction Week 8: 1191440, 1151155
Actuals Week 8: 1007143, 1369857
Difference Week 8: -184297, 218702
Percent Week 8: 85%, 119%
Prediction Week 9: 1292520, 1383868
Actuals Week 9: 1562970, 1489234
Difference Week 9: 270450, 105366
Percent Week 9: 121%, 108%
Prediction Week 10: 1534959, 1505965
Actuals Week 10: 1682347, 1562763
Difference Week 10: 147388, 56798
Percent Week 10: 110%, 104%
Prediction Week 11: 1677370, 1683000
Actuals Week 11: 1755876, 1664745
Difference Week 11: 78506, -18255
Percent Week 11: 105%, 99%
Prediction Week 12: 1872707, 1895996
Actuals Week 12: 1857858, 1793844
Difference Week 12: -14849, -102152
Percent Week 12: 99%, 95%
Prediction Week 13: 2299290, 2108978
Actuals Week 13: 1986957, 1972123
Difference Week 13: -312333, -136855
Percent Week 13: 86%, 94% [/font]
November 29, 2007 at 12:31 am
Realized I had more in the results than what you could get with the sample data. The results should be:
Columns: Row 1, Row 2
prediction_date_enddate 4/28/2007 0:00, 5/5/2007 0:00
Actuals Week 1: 193113, 153320
Actuals Week 2: 346433, 275591
Actuals Week 3: 468704, 356437
Actuals Week 4: 549550, 467040
Actuals Week 5: 660153, 556788
Actuals Week 6: 749901, 673463
Actuals Week 7: 866576, 814030
Actuals Week 8: 1007143, 1369857
Actuals Week 9: 1562970, 1489234
Actuals Week 10: 1682347, 1562763
Actuals Week 11: 1755876, 1664745
Actuals Week 12: 1857858, 1793844
Actuals Week 13: 1986957, 1972123
That may clear up some confussion. Any suggestions would be great. Thanks.
December 3, 2007 at 3:45 pm
In case anyone is ever trying to do something similar, here is what I came up with for the actuals I was after.
SELECT cr.prediciton_date_startdate,
cr.prediction_date_enddate,
cr.actuals AS [1],
b2.[2],
b3.[3],
b4.[4],
b5.[5],
b6.[6],
b7.[7],
b8.[8],
b9.[9],
b10.[10],
b11.[11],
b12.[12],
b13.[13]
FROM #tmpactuals cr
CROSS APPLY(
SELECT [2] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 1, cr.prediction_date_enddate) AND
actuals <> (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 1, cr.prediction_date_enddate))
) b2
CROSS APPLY(
SELECT [3] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 2, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 1, cr.prediction_date_enddate))
) b3
CROSS APPLY(
SELECT [4] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 3, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 2, cr.prediction_date_enddate))
) b4
CROSS APPLY(
SELECT [5] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 4, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 3, cr.prediction_date_enddate))
) b5
CROSS APPLY(
SELECT [6] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 5, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 4, cr.prediction_date_enddate))
) b6
CROSS APPLY(
SELECT [7] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 6, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 5, cr.prediction_date_enddate))
) b7
CROSS APPLY(
SELECT [8] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 7, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 6, cr.prediction_date_enddate))
) b8
CROSS APPLY(
SELECT [9] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 8, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 7, cr.prediction_date_enddate))
) b9
CROSS APPLY(
SELECT [10] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 9, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 8, cr.prediction_date_enddate))
) b10
CROSS APPLY(
SELECT [11] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 10, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 9, cr.prediction_date_enddate))
) b11
CROSS APPLY(
SELECT [12] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 11, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 10, cr.prediction_date_enddate))
) b12
CROSS APPLY(
SELECT [13] = SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 12, cr.prediction_date_enddate) AND
(SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate (SELECT SUM(actuals)
FROM #tmpactuals cr2
WHERE cr2.prediction_date_enddate >= cr.prediction_date_enddate AND
cr2.prediction_date_enddate <= DATEADD(wk, 11, cr.prediction_date_enddate))
) b13
December 3, 2007 at 3:53 pm
Can you post what some of the output looked like? Thanks.
--Jeff Moden
December 3, 2007 at 10:39 pm
I would like to but I can't seem to get the formatting correct to make it look good and be readable. If you run this to create a temp table and then populate it you can run the solution against it and see th results for the actuals (which was the last part of this post series). If anyone runs this and has a way to post the results that are readable and in a good format I'd appreciate to know how so I could use it in future posts.
CREATE TABLE #tmpactuals
(caid int IDENTITY(1,1) NOT NULL,
prediction_date_startdate datetime NULL,
prediction_date_enddate datetime NULL,
actuals money NULL
) ON [PRIMARY]
INSERT into #tmpactuals VALUES('4/22/2007 0:00', '4/28/2007 0:00', 193113)
INSERT into #tmpactuals VALUES('4/29/2007 0:00', '5/5/2007 0:00', 153320)
INSERT into #tmpactuals VALUES('5/6/2007 0:00', '5/12/2007 0:00', 122271)
INSERT into #tmpactuals VALUES('5/13/2007 0:00', '5/19/2007 0:00', 80846)
INSERT into #tmpactuals VALUES('5/20/2007 0:00', '5/26/2007 0:00', 110603)
INSERT into #tmpactuals VALUES('5/27/2007 0:00', '6/2/2007 0:00', 89748)
INSERT into #tmpactuals VALUES('6/3/2007 0:00', '6/9/2007 0:00', 116675)
INSERT into #tmpactuals VALUES('6/10/2007 0:00', '6/16/2007 0:00', 140567)
INSERT into #tmpactuals VALUES('6/17/2007 0:00', '6/23/2007 0:00', 555827)
INSERT into #tmpactuals VALUES('6/24/2007 0:00', '6/30/2007 0:00', 119377)
INSERT into #tmpactuals VALUES('7/1/2007 0:00', '7/7/2007 0:00', 73529)
INSERT into #tmpactuals VALUES('7/8/2007 0:00', '7/14/2007 0:00', 101982)
INSERT into #tmpactuals VALUES('7/15/2007 0:00', '7/21/2007 0:00', 129099)
INSERT into #tmpactuals VALUES('7/22/2007 0:00', '7/28/2007 0:00', 178279)
December 4, 2007 at 6:17 am
you can run the solution against it and see th results
Heh... no... I can't... don't have 2k5...
From what I understand, Cross Apply has some performance limitations... I was looking to make a nice fast 2k solution without it... I've seen your inputs and 1 picture is worth a thosand words... wanted to see the output.
If you setup to put the output in the text window and set the text windows up for "tab alignment" and paste it into a code window (click on IFCode when editing your message, you'll find it), I can do the rest.
--Jeff Moden
December 4, 2007 at 8:21 am
Jeff,
Here you go! Thanks for the tip. I've been struggling with how to get the results in here in a way that is representative of how they look. I also took this and eventually combined it with what the help from Jason and am able to get all the items I need. Specifically to the actuals though, this is what it looks like. Obviously the headers are moved over above their corresponding columns.
prediciton_date_startdate prediction_date_enddate 1 2 3 4 5 6 7 8 9 10 11 12 13
2007-04-22 00:00:00.000 2007-04-28 00:00:00.000 193113.0000 346433.0000 468704.0000 549550.0000 660153.0000 749901.0000 866576.0000 1007143.0000 1562970.0000 1682347.0000 1755876.0000 1857858.0000 1986957.0000
2007-04-29 00:00:00.000 2007-05-05 00:00:00.000 153320.0000 275591.0000 356437.0000 467040.0000 556788.0000 673463.0000 814030.0000 1369857.0000 1489234.0000 1562763.0000 1664745.0000 1793844.0000 1972123.0000
2007-05-06 00:00:00.000 2007-05-12 00:00:00.000 122271.0000 203117.0000 313720.0000 403468.0000 520143.0000 660710.0000 1216537.0000 1335914.0000 1409443.0000 1511425.0000 1640524.0000 1818803.0000
2007-05-13 00:00:00.000 2007-05-19 00:00:00.000 80846.0000 191449.0000 281197.0000 397872.0000 538439.0000 1094266.0000 1213643.0000 1287172.0000 1389154.0000 1518253.0000 1696532.0000
2007-05-20 00:00:00.000 2007-05-26 00:00:00.000 110603.0000 200351.0000 317026.0000 457593.0000 1013420.0000 1132797.0000 1206326.0000 1308308.0000 1437407.0000 1615686.0000
2007-05-27 00:00:00.000 2007-06-02 00:00:00.000 89748.0000 206423.0000 346990.0000 902817.0000 1022194.0000 1095723.0000 1197705.0000 1326804.0000 1505083.0000
2007-06-03 00:00:00.000 2007-06-09 00:00:00.000 116675.0000 257242.0000 813069.0000 932446.0000 1005975.0000 1107957.0000 1237056.0000 1415335.0000
2007-06-10 00:00:00.000 2007-06-16 00:00:00.000 140567.0000 696394.0000 815771.0000 889300.0000 991282.0000 1120381.0000 1298660.0000
2007-06-17 00:00:00.000 2007-06-23 00:00:00.000 555827.0000 675204.0000 748733.0000 850715.0000 979814.0000 1158093.0000
2007-06-24 00:00:00.000 2007-06-30 00:00:00.000 119377.0000 192906.0000 294888.0000 423987.0000 602266.0000
2007-07-01 00:00:00.000 2007-07-07 00:00:00.000 73529.0000 175511.0000 304610.0000 482889.0000
2007-07-08 00:00:00.000 2007-07-14 00:00:00.000 101982.0000 231081.0000 409360.0000
2007-07-15 00:00:00.000 2007-07-21 00:00:00.000 129099.0000 307378.0000
2007-07-22 00:00:00.000 2007-07-28 00:00:00.000 178279.0000
December 4, 2007 at 8:42 am
Now this doesn't get you all of the way there - but it seems a lot simpler to create the running sums FIRST,
then worry about getting the laid out the way you wish.
This is spin on what you've already posted, which creates the running prediction totals and running actuals in the #tmpreceipts table,
which would then be use to do your pivot operation. This should scale quite a bit better than the continuous CROSS APPLY statements.
Take a look...
drop table #tmpreciepts
drop table #tmpactuals
CREATE TABLE #tmpreciepts (
cfrid int IDENTITY(1,1) NOT NULL,
prediction_date_startdate datetime NULL,
prediction_date_enddate datetime NULL,
prediction_week_startdate datetime NULL,
prediction_week_enddate datetime NULL,
prediction_week_number int NULL,
prediction_value money NULL,
actuals money NULL,
running_prediction money null, --added these two to store the temp results
running_actual money null default 0
) ON [PRIMARY]
CREATE TABLE #tmpactuals
(
caid int IDENTITY(1,1) NOT NULL,
prediction_date_startdate datetime NULL,
prediction_date_enddate datetime NULL,
actuals money null
) ON [PRIMARY]
go
--make some test data
INSERT into #tmpreciepts (prediction_date_startdate ,
prediction_date_enddate ,
prediction_week_startdate ,
prediction_week_enddate ,
prediction_week_number ,
prediction_value ,
actuals,
running_prediction)
select '4/22/2007 0:00', '4/28/2007 0:00', '4/22/2007 0:00', '4/28/2007 0:00', 1, 74140, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '4/29/2007 0:00', '5/5/2007 0:00', 2, 163622, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '5/6/2007 0:00', '5/12/2007 0:00', 3, 175397, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '5/13/2007 0:00', '5/19/2007 0:00', 4, 278627, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '5/20/2007 0:00', '5/26/2007 0:00', 5, 219463, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '5/27/2007 0:00', '6/2/2007 0:00', 6, 77807, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '6/3/2007 0:00', '6/9/2007 0:00', 7, 100454, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '6/10/2007 0:00', '6/16/2007 0:00', 8, 101930, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '6/17/2007 0:00', '6/23/2007 0:00', 9, 101080, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '6/24/2007 0:00', '6/30/2007 0:00', 10, 242439, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '7/1/2007 0:00', '7/7/2007 0:00', 11, 142411, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '7/8/2007 0:00', '7/14/2007 0:00', 12, 195337, 193113,0 UNION ALL
select '4/22/2007 0:00', '4/28/2007 0:00', '7/15/2007 0:00', '7/21/2007 0:00', 13, 426583, 193113,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '4/29/2007 0:00', '5/5/2007 0:00', 1, 123535, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '5/6/2007 0:00', '5/12/2007 0:00', 2, 122371, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '5/13/2007 0:00', '5/19/2007 0:00', 3, 271529, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '5/20/2007 0:00', '5/26/2007 0:00', 4, 220594, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '5/27/2007 0:00', '6/2/2007 0:00', 5, 108376, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '6/3/2007 0:00', '6/9/2007 0:00', 6, 92848, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '6/10/2007 0:00', '6/16/2007 0:00', 7, 117516, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '6/17/2007 0:00', '6/23/2007 0:00', 8, 94386, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '6/24/2007 0:00', '6/30/2007 0:00', 9, 232713, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '7/1/2007 0:00', '7/7/2007 0:00', 10, 122097, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '7/8/2007 0:00', '7/14/2007 0:00', 11, 177035, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '7/15/2007 0:00', '7/21/2007 0:00', 12, 212996, 153320,0 UNION ALL
select '4/29/2007 0:00', '5/5/2007 0:00', '7/22/2007 0:00', '7/28/2007 0:00', 13, 212982, 153320,0
INSERT into #tmpactuals VALUES('4/22/2007 0:00', '4/28/2007 0:00', 193113)
INSERT into #tmpactuals VALUES('4/29/2007 0:00', '5/5/2007 0:00', 153320)
INSERT into #tmpactuals VALUES('5/6/2007 0:00', '5/12/2007 0:00', 122271)
INSERT into #tmpactuals VALUES('5/13/2007 0:00', '5/19/2007 0:00', 80846)
INSERT into #tmpactuals VALUES('5/20/2007 0:00', '5/26/2007 0:00', 110603)
INSERT into #tmpactuals VALUES('5/27/2007 0:00', '6/2/2007 0:00', 89748)
INSERT into #tmpactuals VALUES('6/3/2007 0:00', '6/9/2007 0:00', 116675)
INSERT into #tmpactuals VALUES('6/10/2007 0:00', '6/16/2007 0:00', 140567)
INSERT into #tmpactuals VALUES('6/17/2007 0:00', '6/23/2007 0:00', 555827)
INSERT into #tmpactuals VALUES('6/24/2007 0:00', '6/30/2007 0:00', 119377)
INSERT into #tmpactuals VALUES('7/1/2007 0:00', '7/7/2007 0:00', 73529)
INSERT into #tmpactuals VALUES('7/8/2007 0:00', '7/14/2007 0:00', 101982)
INSERT into #tmpactuals VALUES('7/15/2007 0:00', '7/21/2007 0:00', 129099)
INSERT into #tmpactuals VALUES('7/22/2007 0:00', '7/28/2007 0:00', 178279)
go
--create the index required for the running sum operation to work
create index ix_tmprcpt on #tmpreciepts(prediction_date_enddate,prediction_week_enddate) include (prediction_value)
--make an index to make the join run better
create index ix_actual on #tmpactuals(prediction_date_enddate) include (actuals)
go
--variables needed for the running sum
declare @runningPredict money --place holder to pass the running from record to record
declare @runningActual money
declare @dummy1 money --the dummies force SQL2005 to make this "running"
declare @dummy2 money
declare @prevPredWk datetime -- the ID tracker to know when to reset
--set up the starting values
select @runningPredict=0,
@runningactual=0,
@dummy1=0,
@dummy2=0,
@prevPredWk=0
--build the running totals
update #tmpreciepts
set @runningPredict=running_prediction=case when @prevPredWk=#tmpreciepts.prediction_date_enddate
then @runningPredict else 0 end +prediction_value,
@dummy1=@runningPredict,
@runningActual=running_actual=case when @prevPredWk=#tmpreciepts.prediction_date_enddate
then @runningActual else 0 end +#tmpactuals.actuals,
@dummy1=@runningPredict,
@prevPredWk=#tmpreciepts.prediction_date_enddate
from
#tmpreciepts with (Index(ix_tmprcpt),tablock)
left outer join #tmpactuals on #tmpreciepts.prediction_week_enddate=#tmpactuals.prediction_date_enddate
--show what you end up with
select * from #tmpreciepts
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 4, 2007 at 8:51 am
once you do that, an "old-style" pivot on a SINGLE table should do the trick:
select
prediction_date_startdate,
prediction_date_enddate,
sum(case when prediction_week_number=1 then running_prediction else 0 end) as Predicted_1,
sum(case when prediction_week_number=1 then running_actual else 0 end) as Actual_1,
sum(case when prediction_week_number=2 then running_prediction else 0 end) as Predicted_2,
sum(case when prediction_week_number=2 then running_actual else 0 end) as Actual_2,
sum(case when prediction_week_number=3 then running_prediction else 0 end) as Predicted_3,
sum(case when prediction_week_number=3 then running_actual else 0 end) as Actual_3,
sum(case when prediction_week_number=4 then running_prediction else 0 end) as Predicted_4,
sum(case when prediction_week_number=4 then running_actual else 0 end) as Actual_4,
sum(case when prediction_week_number=5 then running_prediction else 0 end) as Predicted_5,
sum(case when prediction_week_number=5 then running_actual else 0 end) as Actual_5
--etc....
from #tmpreciepts
group by
prediction_date_startdate,
prediction_date_enddate
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 14, 2007 at 10:24 am
Thanks Matt! This works great. On the live data I'm finding both methods are actually producing roughly the same speed results, which I found interesting. I am guessing as data accumulates in the table the method you laid out will end up being more efficient.
A couple of questions for you. What are the "Dummy" variables doing? I couldn't tell by looking at it and took them out and everything appears to run fine without them.
The other question I had was in the FROM clause. Can you explain to me what the Index hint with tablock is doing and I guess just what that is doing? I've never really used hints before and just want to understand better what is going on with it. Thanks for your help.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply