SQL Puzzle - SQL Server Forums

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)

SQL Puzzle

Author

Topic

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2003-06-11 : 15:56:03

For my brain anyway....

I'm looking for a way to select a list of dates based on a range of dates. Given the following table and rows.


CREATE TABLE DateRanges (
	BeginDate smalldatetime,
	EndDate smalldatetime)

INSERT INTO DateRanges VALUES ('12/1/2001', '12/31/2001')
INSERT INTO DateRanges VALUES ('1/1/2002', '1/31/2002')
INSERT INTO DateRanges VALUES ('3/1/2002', '3/31/2002')

.

I'd like to be able to select the following results... Any ideas for a set base solution to this??

12/1/2001
12/2/2001
...
12/30/2001
12/31/2001
1/1/2002
1/2/2002
...
1/30/2002
1/31/2002
3/1/2002
3/2/2002
...
3/30/2002
3/31/2002

Jeff Banschbach, MCDBA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-11 : 16:03:04

Tally table!

a tally table if just a table of numbers, usually from 1-1000.

you create it once so you always have it, not each time you need it.

so, if the table is called "Tally" with a column called "n" from 1 - 1000 :

SELECT DateAdd(d,n-1,begindate) as [Date]
FROM
DateRanges
CROSS JOIN
Tally
WHERE
Tally.n <= DateDiff(d, begindate, enddate)

(note: i may be off by a day or two -- not tested yet)

tally tables are really useful for many purposes ... do a search on these forums for this and other great examples.

- Jeff

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2003-06-11 : 16:07:18

Damn that was fast Mr. Cross Join. Actually seeing your nick name in another post is what led me to the answer. Here's what I ended up using.


CREATE TABLE DateRanges (
	BeginDate smalldatetime,
	EndDate smalldatetime)

INSERT INTO DateRanges VALUES ('12/1/2001', '12/31/2001')
INSERT INTO DateRanges VALUES ('1/1/2002', '1/31/2002')
INSERT INTO DateRanges VALUES ('3/1/2002', '3/31/2002')

CREATE TABLE Days (DayNum int)
INSERT INTO Days VALUES (0)
INSERT INTO Days VALUES (1)
INSERT INTO Days VALUES (2)
INSERT INTO Days VALUES (3)
INSERT INTO Days VALUES (4)
INSERT INTO Days VALUES (5)
INSERT INTO Days VALUES (6)
INSERT INTO Days VALUES (7)
INSERT INTO Days VALUES (8)
INSERT INTO Days VALUES (9)
INSERT INTO Days VALUES (10)
INSERT INTO Days VALUES (11)
INSERT INTO Days VALUES (12)
INSERT INTO Days VALUES (13)
INSERT INTO Days VALUES (14)
INSERT INTO Days VALUES (15)
INSERT INTO Days VALUES (16)
INSERT INTO Days VALUES (17)
INSERT INTO Days VALUES (18)
INSERT INTO Days VALUES (19)
INSERT INTO Days VALUES (20)
INSERT INTO Days VALUES (21)
INSERT INTO Days VALUES (22)
INSERT INTO Days VALUES (23)
INSERT INTO Days VALUES (24)
INSERT INTO Days VALUES (25)
INSERT INTO Days VALUES (26)
INSERT INTO Days VALUES (27)
INSERT INTO Days VALUES (28)
INSERT INTO Days VALUES (29)
INSERT INTO Days VALUES (30)

SELECT	DATEADD(d, D.DayNum, DR.BeginDate)
FROM 	DateRanges DR CROSS JOIN
	Days D
WHERE	DATEADD(d, D.DayNum, DR.BeginDate) BETWEEN DR.BeginDate AND DR.EndDate

.

Thanks!

Jeff Banschbach, MCDBA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-11 : 17:12:24

no problem ...

you should have better performance with:

WHERE D.DayNum < DateDiff(d, BeginDate, EndDate)

because now you can use an index for the Days table (if there is one).

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources