How do I retrieve 7 days of data from a given date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How do I retrieve 7 days of data from a given date

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2008-10-06 : 18:38:09

I'm trying to generate a view that shows a 7-day history of scores along with the most recent date that an award was won by that individual. I would like the date to be variable so a view of any 7-day range is possible, though the LastAwardDate column should always show the most recent date an award was won.

What I want it to look like:

ID | 9/1 | 9/2 | 9/3 | 9/4 | 9/5 | 9/6 | 9/7 | LastAwardDate
---+-----+-----+-----+-----+-----+-----+-----+-----------
01 | 141 | 90 | 150 | 110 | 101 | 96 | 114 | 10/03/2008
02 | 135 | 220 | 164 | 175 | 203 | 300 | 147 | 10/02/2008

TABLES:
DAILY
id, int -- User ID
score, int -- Score
date, smalldatetime -- Date of the score
*there is only going to be one score per date

AWARDS
id, int
date, datestamp
comment, nvarchar(255)

DATA:
DAILY:
01, 9/07/2008, 114
02, 9/07/2008, 147
01, 9/06/2008, 96
02, 9/06/2008, 300
01, 9/05/2008, 101
...and so on

AWARDS:
09/05/2008, 01, "Green Ribbon"
10/03/2008, 01, "Blue Ribbon"
08/02/2008, 02, "VIP Ribbon"
10/02/2008, 02, "Red Ribbon"
... and so on

Any help is GREATLY appreciated. I have it making 8 separate SQL calls from within the page itself but I know there must be a more efficient way to do this all with in MS SQL Server 2000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 00:46:14

[code]SELECT ID,
MAX(CASE WHEN Seq=7 THEN score ELSE NULL END) AS 1,
MAX(CASE WHEN Seq=6 THEN score ELSE NULL END) AS 2,
MAX(CASE WHEN Seq=5 THEN score ELSE NULL END) AS 3,
MAX(CASE WHEN Seq=4 THEN score ELSE NULL END) AS 4,
MAX(CASE WHEN Seq=3 THEN score ELSE NULL END) AS 5,
MAX(CASE WHEN Seq=2 THEN score ELSE NULL END) AS 6,
MAX(CASE WHEN Seq=1 THEN score ELSE NULL END) AS 7,
MAX(awarddate) AS LatestAwardDate
FROM
(
SELECT d.id,d.score,d.date as dailydate,t.date as awarddate,t.comment,
COALESCE((SELECT COUNT(*) FROM DAILY WHERE ID=d.ID AND date >d.date),0)+1 AS Seq
FROM DAILY d
LEFT JOIN AWARDS a
ON a.ID=d.ID
)t
GROUP BY ID[/code]

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2008-10-07 : 15:24:09

This gives the following error in Query Analyzer.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '1'.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near 't'.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-07 : 15:46:22

declare @DAILY table(
id int, -- User ID
score int, -- Score
date smalldatetime -- Date of the score
--*there is only going to be one score per date
);

declare @AWARDS table (
id int,
date smalldatetime,
comment nvarchar(255)
);

insert into @DAILY (id, date,score)
select 1, '9/07/2008', 114 union all
select 2, '9/07/2008', 147 union all
select 1, '9/06/2008', 96 union all
select 2, '9/06/2008', 300 union all
select 1, '9/05/2008', 101
;

insert into @AWARDS (DATE,ID,COMMENT)
select '09/05/2008', 1, 'Green Ribbon' union all
select '10/03/2008', 1, 'Blue Ribbon' union all
select '08/02/2008', 2, 'VIP Ribbon' union all
select '10/02/2008', 2, 'Red Ribbon';

SELECT ID,
MAX(CASE WHEN Seq=7 THEN score ELSE NULL END) AS [1],
MAX(CASE WHEN Seq=6 THEN score ELSE NULL END) AS [2],
MAX(CASE WHEN Seq=5 THEN score ELSE NULL END) AS [3],
MAX(CASE WHEN Seq=4 THEN score ELSE NULL END) AS [4],
MAX(CASE WHEN Seq=3 THEN score ELSE NULL END) AS [5],
MAX(CASE WHEN Seq=2 THEN score ELSE NULL END) AS [6],
MAX(CASE WHEN Seq=1 THEN score ELSE NULL END) AS [7],
MAX(awarddate) AS LatestAwardDate
FROM
(
SELECT d.id,d.score,d.date as dailydate,a.date as awarddate,a.comment,
COALESCE((SELECT COUNT(*) FROM @DAILY WHERE ID=d.ID AND date >d.date),0)+1 AS Seq
FROM @DAILY d
LEFT JOIN @AWARDS a
ON a.ID=d.ID
)t
GROUP BY ID

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2008-10-07 : 18:50:31

Thanks! That works, though I don't believe it's pulling in the data correctly. To be honest, I don't really follow some of the SQL there. I need to research what COALESCE does.

Here's one line of output I recieved:

1024 NULL NULL NULL NULL NULL NULL 69 2008-09-27 17:14:06.667

The score given is from 9/17/2008 - There are scores for the previous days but it's returning nulls. i can clean up the date in the site code. Here's the data that this should be pulling from for the date of 9/17/2008:

TABLE: Daily
1024 | 9/11/2008 | 55
1024 | 9/12/2008 | 65
1024 | 9/13/2008 | 45
1024 | 9/14/2008 | 54
1024 | 9/15/2008 | 37
1024 | 9/16/2008 | 41
1024 | 9/17/2008 | 69

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 01:22:07

with your data too added


declare @DAILY table(
id int, -- User ID
score int, -- Score
date smalldatetime -- Date of the score
--*there is only going to be one score per date
);

declare @AWARDS table (
id int,
date smalldatetime,
comment nvarchar(255)
);

insert into @DAILY (id, date,score)
select 1, '9/07/2008', 114 union all
select 2, '9/07/2008', 147 union all
select 1, '9/06/2008', 96 union all
select 2, '9/06/2008', 300 union all
select 1, '9/05/2008', 101 union all
select 1024 , '9/11/2008',  55 union all
select 1024 , '9/12/2008',  65 union all
select 1024 , '9/13/2008',  45 union all
select 1024 , '9/14/2008',  54 union all
select 1024 , '9/15/2008',  37 union all
select 1024 , '9/16/2008',  41 union all
select 1024 , '9/17/2008',  69 union all
select 2, '9/10/2008', 300 union all
select 1, '9/10/2008', 115 union all
select 2, '9/11/2008', 23 union all
select 2, '9/12/2008', 11 union all
select 2, '9/20/2008', 224 union all
select 1, '10/12/2008', 167 union all
select 1, '10/26/2008', 44 union all
select 1, '11/15/2008', 457 

insert into @AWARDS (DATE,ID,COMMENT)
select '09/05/2008', 1, 'Green Ribbon' union all
select '10/03/2008', 1, 'Blue Ribbon' union all
select '08/02/2008', 2, 'VIP Ribbon' union all
select '10/02/2008', 2, 'Red Ribbon' union all
select '09/20/2008',1024,'Red Ribbon';

SELECT ID,
MAX(CASE WHEN Seq=7 THEN score ELSE NULL END) AS [1],
MAX(CASE WHEN Seq=6 THEN score ELSE NULL END) AS [2],
MAX(CASE WHEN Seq=5 THEN score ELSE NULL END) AS [3],
MAX(CASE WHEN Seq=4 THEN score ELSE NULL END) AS [4],
MAX(CASE WHEN Seq=3 THEN score ELSE NULL END) AS [5],
MAX(CASE WHEN Seq=2 THEN score ELSE NULL END) AS [6],
MAX(CASE WHEN Seq=1 THEN score ELSE NULL END) AS [7],
MAX(awarddate) AS LatestAwardDate
FROM
(
SELECT d.id,d.score,d.date as dailydate,a.date as awarddate,a.comment,
COALESCE((SELECT COUNT(*) FROM @DAILY WHERE ID=d.ID AND date >d.date),0)+1 AS Seq
FROM @DAILY d
LEFT JOIN @AWARDS a
ON a.ID=d.ID
)t
GROUP BY ID


output
-------------------------------------------------------------
ID          1           2           3           4           5           6           7           LatestAwardDate
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------------------
1           101         96          114         115         167         44          457         2008-10-03 00:00:00
2           NULL        300         147         300         23          11          224         2008-10-02 00:00:00
1024        55          65          45          54          37          41          69          2008-09-20 00:00:00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources