While Loop - Date and Hour Table

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

General SQL Server Forums

New to SQL Server Programming

While Loop - Date and Hour Table

Author

Topic

Philip.Fernandez
Starting Member

4 Posts

Posted - 2010-01-20 : 13:16:03

Hello. This is my first post here, and I am relatively new to T-SQL, so please go easy on me. This isn't a homework question. This is something I'm trying to do to help me at work.

I'd like to create a temporary table with a date and hour column, and fill the dates and hours using some kind of a WHILE loop. For example:


CREATE TABLE #request
 (
  date DATETIME NOT NULL,
  hour INT NOT NULL
 )

I then want to create a WHILE loop to INSERT into the dates and hour columns 24 hours per day for a specific date range. For example, if I set the first day in the WHILE loop to 1/1/2005 and the end date in the WHILE loop to 1/31/2006, I'd like to see...


   date    hour
---------- ----
  1/1/2005    1
  1/1/2005    2
  1/1/2005    3
...
  1/1/2005   24
...
 1/31/2006   24

Can anyone please provide me with suggestions on how to go about doing this? Thank you in advance.

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-20 : 14:03:39

See the below script

CREATE TABLE #request
(
date DATETIME NOT NULL,
hour INT NOT NULL
)

declare @start_dt datetime
declare @end_dt datetime
declare @i int

set @start_dt = '12/31/2009'
set @end_dt = '01/05/2010'

set @i=0
while (@start_dt <= @end_dt)
begin
WHILE (@i<25)
BEGIN
insert #request
select @start_dt,@i
set @i = @i +1
END
set @start_dt = @start_dt + 1
END

-Shan

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-01-20 : 14:23:45

You can even do it without a while loop

Jim

select dateadd(hour,a.number,dateadd(day,spt.number,'01/01/2005'))
from
master..spt_values spt

CROSS APPLY

(select spt.number
from
master..spt_values spt
where type = 'p' and spt.number < 24
) a
where type = 'p'
and spt.number < 396
order by 1

Everyday I learn something that somebody else already knew

Philip.Fernandez
Starting Member

4 Posts

Posted - 2010-01-20 : 14:24:33

Jim:

Thanks for your suggestion. I'm not familiar with the CROSS APPLY functions and their syntax, so it'll take some time for me to dissect how that's working.

Shan:

I tried your script with the modification of setting @i to 1 instead of 0. However, I noticed that this only updates the table for one day's worth of hours. It's as though the start date is not incrementing, even with the "set @start_dt = @start_dt + 1" function. Do you know what might be causing the loop to not continue to the next day?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-01-20 : 14:37:01

CROSS APPLY (as well as OUTER APPLY) are new in SQL Serve 2005.
Jim

select dateadd(day,spt.number,'01/01/2005'),a.number
from
master..spt_values spt

CROSS JOIN

(select spt.number
from
master..spt_values spt
where type = 'p' and spt.number between 1 and 24
) a
where type = 'p'
and spt.number < 396
order by 1

Everyday I learn something that somebody else already knew

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-20 : 15:06:46

This will work..

CREATE TABLE #request
(
date DATETIME NOT NULL,
hour INT NOT NULL
)

declare @start_dt datetime
declare @end_dt datetime
declare @i int

declare @t int

set @start_dt = '12/31/2009'
set @end_dt = '01/05/2010'
--set @t =

set @i=0
while (DATEDIFF(dd, @start_dt, @end_dt) <> 0)

begin
set @i = 0
WHILE (@i<24)
BEGIN
set @i = @i +1

insert #request
select @start_dt,@i

END

set @start_dt = DATEADD("day", 1,@start_dt)
END

select * from #request

-Shan

Philip.Fernandez
Starting Member

4 Posts

Posted - 2010-01-20 : 15:20:36

Shan:

I think I've found the problem and corrected it. I simply didn't reset @i at any point, so it hit 24 and and stopped after the first day. Thank you for your second script though. I will try it and see if it is a better solution than your first script with my minor adjustments.

Thanks again for your help. It's very much appreciated.

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-20 : 15:35:26

You are welcome

-Shan

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-20 : 19:18:41

If you need a loop or a cursor....you haven't found the "answer"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources