get a week range to each individual week

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

SQL Server Development (2000)

get a week range to each individual week

Author

Topic

petebob796
Starting Member

35 Posts

Posted - 2007-08-07 : 10:46:29

I currently have two tables:
registers
register_id start_week end_week
001 2 5
002 1 2

marked
register_id week
001 2

This means register 001 has been marked for week 2 (weeks are based on academic year). I would like to return a report showing weeks that havent been marked so 001 week 3,4 and 5 haven't been marked. I thought the easiest way would be to turn the registers table range into each individual week like the table below.

register_id week
001 2
001 3
001 4
001 5
002 1
002 2

Then find the ones which don't exist in marked however I don't know how to create this table. Any help or suggestions of other ways to do this appreciated.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-07 : 12:04:29

You need to use a number/tally table. (Plenty of examples on this site)
eg

CREATE TABLE dbo.Numbers
(
    Number smallint NOT NULL PRIMARY KEY CLUSTERED
)
DECLARE @i int
SET @i = 1
BEGIN TRAN
WHILE (@i <= 8000)
BEGIN
    INSERT INTO dbo.Numbers SELECT @i
    SET @i = @i + 1
END
COMMIT

Once you have a numbers table you can do something like the following:

SELECT R.register_id, N.Number AS WeekNotMarked
FROM registers R
    JOIN dbo.Numbers N
        ON N.Number BETWEEN R.start_week AND R.end_week
    LEFT JOIN marked M
        ON R.register_id = M.register_id
            AND N.Number = M.[week]
WHERE M.register_id IS NULL

SELECT R.register_id, N.Number AS WeekNotMarked
FROM registers R
    JOIN dbo.Numbers N
        ON N.Number BETWEEN R.start_week AND R.end_week
WHERE NOT EXISTS (
        SELECT *
        FROM marked M
        WHERE M.register_id = RM.register_id
            AND M.[week] = N.Number
    )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources