daily to hourly - 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 2005 Forums

Transact-SQL (2005)

daily to hourly

Author

Topic

shaw
Starting Member

15 Posts

Posted - 2007-05-03 : 14:05:06

hi there,

i have a table with 1 column going like this:

7/1/2001
7/2/2001
7/3/2001
7/4/2001
7/5/2001
7/6/2001

i have another table with 1 column going like this:
07/01/2001 01
07/01/2001 02
07/01/2001 03
07/01/2001 04
07/01/2001 05
07/01/2001 06
07/01/2001 07
07/01/2001 08
07/01/2001 09
07/01/2001 10
07/01/2001 11
07/01/2001 12
07/01/2001 13
07/01/2001 14
07/01/2001 15
07/01/2001 16
07/01/2001 17
07/01/2001 18
07/01/2001 19
07/01/2001 20
07/01/2001 21
07/01/2001 22
07/01/2001 23
07/01/2001 24
07/02/2001 01
07/02/2001 02
07/02/2001 03
07/02/2001 04
07/02/2001 05

In the second table, I want to create a second column. Into it, I want to put the 7/1/2001 value for all values that starts with 7/1/2001 in the second table .This is basically putting the daily data for all hours for that day.

You have any idea for the script?

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-03 : 14:09:22

quote:
Originally posted by shaw
.... I want to put the 7/1/2001 value for all values that starts with 7/1/2001 in the second table ...

Can you repharse that please?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

shaw
Starting Member

15 Posts

Posted - 2007-05-03 : 14:18:58

sure,

my main table is hourly.
so it goes as the following (this is one column)
07/01/2001 01
07/01/2001 02
07/01/2001 03
07/01/2001 04
07/01/2001 05

my daily column is like this one:

7/1/2001
7/2/2001
7/3/2001
7/4/2001

Now I want to take the dates in my second table and put them into my hourly (first) table.

For ex:

Take '7/1/2001' in my second table and put it 24 times in my first table as '07/01/2001'. So that at the end, I will have the following:

07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/01/2001
07/02/2001
07/02/2001
07/01/2002
.
.
.
.
24 times for each day.

Is it more clear?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-03 : 14:42:36

Create a table variable with all the 24 rows '01' through '24', then do an insert with a CROSS JOIN.


Declare @hourly table (col1 datetime, col2 varchar(5))
Declare @daily table (col1 datetime)

Declare @hours table (col1 varchar(5))
insert into @hours values ('01')
insert into @hours values ('02')
insert into @hours values ('03')
insert into @hours values ('04')
insert into @hours values ('05')

insert into @daily values ('7/1/2001')
insert into @daily values ('7/2/2001')
insert into @daily values ('7/3/2001')
insert into @daily values ('7/4/2001')

insert into @hourly
select * from @daily
cross join @hours

select * from @hourly order by col1,col2

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

shaw
Starting Member

15 Posts

Posted - 2007-05-03 : 16:54:51

thanks for this Dinakar.
worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources