Generating a list of dates - 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

SQL Server Development (2000)

Generating a list of dates

Author

Topic

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-10-01 : 19:09:12

Hi,

I know I've seen this here somewhere before, but can't find it.

I want to chart over any given time period the number of active customers we have (grouped by day). In our customers table we have an activateddate and cancelleddate column so for any given day figuring out the number of active customers is pretty easy. The bit I'm struggling is generating a seqeuntial list of days that I can join on, so that I can create a stored proc with a startdate and enddate as arguments. I need have a sequential list of days in case there are days in the customer table where no-one was activated or cancelled so I don't get gaps in my chart.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-01 : 20:11:34

Not sure if there is a set-based method...


SET NOCOUNT ON

CREATE TABLE Table1 
(
SomeColumn DATETIME NOT NULL
)

DECLARE @enddate DATETIME
DECLARE @date DATETIME

SET @date = 'Jan 01 2000'
SET @enddate = 'Dec 31 2010'

WHILE @date <= @enddate
BEGIN
	INSERT INTO Table1 VALUES(@date)

	SET @date = DATEADD(day, 1, @date)
END

SELECT CONVERT(VARCHAR(50), SomeColumn, 101)
FROM Table1

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-01 : 21:12:20

create a tally table, or a table of numbers from 0 to 1000 or so. Store it permanently in your database, it will be useful for situations like this.

Then, you can just say something like:

SELECT DateAdd(dd,Number, @StartDate) as NewDate
FROM Numbers
WHERE Number <= DateDiff(dd,@EndDate, @StartDate)

or something along those lines.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources