T-SQL help
-
Help needed on the T-SQL query.....
Here is the sample data....
BEGIN
IF OBJECT_ID('Tempdb..#tTest') IS NOT NULL
DROP TABLE #tTest
CREATE TABLE #tTest(Name varchar(5) null, PDate datetime null, Value float null)
INSERT INTO #tTest(Name, PDate, Value) VALUES ('A','2011-12-18 00:00:00.000', 0)
,('A','2011-12-17 00:00:00.000',1)
,('A','2011-12-16 00:00:00.000',2)
,('A','2011-12-15 00:00:00.000',3)
,('A','2011-12-14 00:00:00.000',4)
,('A','2011-12-13 00:00:00.000',5)
,('A','2011-12-12 00:00:00.000',6)
,('A','2011-12-11 00:00:00.000',7)
,('A','2011-12-10 00:00:00.000',0)
,('A','2011-12-09 00:00:00.000',1)
,('A','2011-12-08 00:00:00.000',2)
,('A','2011-12-07 00:00:00.000',3)
,('A','2011-12-06 00:00:00.000',4)
,('A','2011-12-05 00:00:00.000',5)
,('A','2011-12-04 00:00:00.000',6)
,('A','2011-12-03 00:00:00.000',7)
,('B','2011-12-18 00:00:00.000',0)
,('B','2011-12-17 00:00:00.000',1)
,('B','2011-12-16 00:00:00.000',2)
,('B','2011-12-15 00:00:00.000',3)
,('B','2011-12-14 00:00:00.000',4)
,('B','2011-12-13 00:00:00.000',5)
,('B','2011-12-12 00:00:00.000',6)
,('B','2011-12-11 00:00:00.000',7)
,('B','2011-12-10 00:00:00.000',0)
,('B','2011-12-09 00:00:00.000',1)
,('B','2011-12-08 00:00:00.000',2)
,('B','2011-12-07 00:00:00.000',3)
,('B','2011-12-06 00:00:00.000',4)
,('B','2011-12-05 00:00:00.000',5)
,('B','2011-12-04 00:00:00.000',6)
,('B','2011-12-03 00:00:00.000',7)
SELECT Name,PDate,Value,Value1='' FROM #tTest
END
I need Value1 out of this.
So for Name='A' & PDate=12/18/2011 Value1 is SUM(Value) of previous 7 days i:e from 12/17 to 12/11
Name='A' & PDate=12/17/2011 Value1 is SUM(Value) of previous 7 days i:e from 12/16 to 12/10
Name='A' & PDate=12/16/2011 Value1 is SUM(Value) of previous 7 days i:e from 12/15 to 12/09
Name='A' & PDate=12/15/2011 Value1 is SUM(Value) of previous 7 days i:e from 12/14 to 12/08
.
.
Name='A' & PDate=12/08/2011 Value1 is SUM(Value) of previous 7 days i:e from 12/07 to 12/01(here only 5 days exists as no data so 12/03)
.
.
and for Name='B' & PDate=12/18/2011 Value1 is SUM(Value) of previous 7 days i:e from 12/17 to 12/11
Name='B' & PDate=12/17/2011 Value1 is SUM(Value) of previous 7 days i:e from 12/16 to 12/10
Name='B' & PDate=12/16/2011 Value1 is SUM(Value) of previous 7 days i:e from 12/15 to 12/09
Name='B' & PDate=12/15/2011 Value1 is SUM(Value) of previous 7 days i:e from 12/14 to 12/08
.
.
Name='B' & PDate=12/08/2011 Value1 is SUM(Value) of previous 7 days i:e from 12/07 to 12/01(here only 5 days exists as no data so 12/03)
.
.
Final Result Set should look like this:
Name PDateValue Value1
A12/18/2011028
A12/17/2011127
A12/16/2011226
A12/15/2011325
A12/14/2011424
A12/13/2011523
A12/12/2011622
A12/11/2011721
A12/10/2011028
A12/9/2011127
A12/8/2011225
A12/7/2011322
A12/6/2011418
A12/5/2011513
A12/4/201167
A12/3/201170
B12/18/2011028
B12/17/2011127
B12/16/2011226
B12/15/2011325
B12/14/2011424
B12/13/2011523
B12/12/2011622
B12/11/2011721
B12/10/2011028
B12/9/2011127
B12/8/2011225
B12/7/2011322
B12/6/2011418
B12/5/2011513
B12/4/201167
B12/3/201170
Thanks in advance!!
-
SELECT t1.Name, t1.PDate, t1.Value, COALESCE(SUM(t2.Value),0) AS Value1
FROM #tTest t1
LEFT OUTER JOIN #tTest t2 ON t2.Name=t1.Name
AND t2.PDate < t1.PDate
AND t2.PDate >= t1.PDate-7
GROUP BY t1.Name, t1.PDate, t1.Value
ORDER BY t1.Name, t1.PDate DESC
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank You! This helps....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply