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
Change is inevitable... Change for the better is not.
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
Change is inevitable... Change for the better is not.
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_startdateprediction_date_enddate12345678910111213
2007-04-22 00:00:00.0002007-04-28 00:00:00.000193113.0000346433.0000468704.0000549550.0000660153.0000749901.0000866576.00001007143.00001562970.00001682347.00001755876.00001857858.00001986957.0000
2007-04-29 00:00:00.0002007-05-05 00:00:00.000153320.0000275591.0000356437.0000467040.0000556788.0000673463.0000814030.00001369857.00001489234.00001562763.00001664745.00001793844.00001972123.0000
2007-05-06 00:00:00.0002007-05-12 00:00:00.000122271.0000203117.0000313720.0000403468.0000520143.0000660710.00001216537.00001335914.00001409443.00001511425.00001640524.00001818803.0000
2007-05-13 00:00:00.0002007-05-19 00:00:00.00080846.0000191449.0000281197.0000397872.0000538439.00001094266.00001213643.00001287172.00001389154.00001518253.00001696532.0000
2007-05-20 00:00:00.0002007-05-26 00:00:00.000110603.0000200351.0000317026.0000457593.00001013420.00001132797.00001206326.00001308308.00001437407.00001615686.0000
2007-05-27 00:00:00.0002007-06-02 00:00:00.00089748.0000206423.0000346990.0000902817.00001022194.00001095723.00001197705.00001326804.00001505083.0000
2007-06-03 00:00:00.0002007-06-09 00:00:00.000116675.0000257242.0000813069.0000932446.00001005975.00001107957.00001237056.00001415335.0000
2007-06-10 00:00:00.0002007-06-16 00:00:00.000140567.0000696394.0000815771.0000889300.0000991282.00001120381.00001298660.0000
2007-06-17 00:00:00.0002007-06-23 00:00:00.000555827.0000675204.0000748733.0000850715.0000979814.00001158093.0000
2007-06-24 00:00:00.0002007-06-30 00:00:00.000119377.0000192906.0000294888.0000423987.0000602266.0000
2007-07-01 00:00:00.0002007-07-07 00:00:00.00073529.0000175511.0000304610.0000482889.0000
2007-07-08 00:00:00.0002007-07-14 00:00:00.000101982.0000231081.0000409360.0000
2007-07-15 00:00:00.0002007-07-21 00:00:00.000129099.0000307378.0000
2007-07-22 00:00:00.0002007-07-28 00:00:00.000178279.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=casewhen @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