Temp Calendar Table - 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)

Temp Calendar Table

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-06 : 08:25:34

I am Working on a complex Scheduling project and need a Temp Calender Table That can be created fast over and over again.

Does anyone have a way to populate a temp table with dates between date(a) and date(b) without useing a cursor?

Jim
Users <> Logic

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-06 : 08:42:34

How about this.

I am just using 100 days but you can obviously change this if you like.
In my example Branch is a table that I know will always have many rows.

--***********************
DECLARE @StartDate DATETIME

SET @StartDate = GETDATE()

SELECT top 100 IDENTITY(INT, 1, 1) as DayNo, @StartDate as MyDate
INTO #Cal
FROM Branch

UPDATE #Cal
SET MyDate = DATEADD(dd, DayNo, MyDate)

select * from #Cal

--****************************

What do you think?

Duane.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-06 : 08:54:47

Interesting approach, if I can use a fixed date window this will defiantly work.

Thanks Duane.

Anyone else have a different approach?

Jim
Users <> Logic

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-06 : 08:58:52

Same approach - just delete the unwanted fields:
How about this one Jim?

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = GETDATE()
SET @EndDate = '2004-05-31'

SELECT top 100 IDENTITY(INT, 1, 1) as DayNo, @StartDate as MyDate
INTO #Cal
FROM Branch

UPDATE #Cal
SET MyDate = DATEADD(dd, DayNo, MyDate)

DELETE FROM #Cal WHERE MyDate > @EndDate

select * from #Cal

Duane.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-06 : 09:02:25

Yep that will work.
Should have thought of that myself.
Not enough coffie Yet Only 8:00 am here.

Thanks Duane.

Jim
Users <> Logic

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-06 : 09:23:13

If you dont want the delete at the end you could also use dynamic sql:

set @top = datediff(dd, @Startdate, @Enddate)

exec('SELECT top ' + STR(@top) + ' IDENTITY(INT, 1, 1) ................ '

But that is probably too much effort for what it is worth.

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources