Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

generating or populating time dimensions Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2011 1:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 2:30 AM
Points: 19, Visits: 43
hi,can any one help me in populating time dimensions in sql from 2000 to 2030 for every 15 min.now i need to create time dimensions from 2000 jan 01 to 2030 dec 31 by taking 15 min interval,for every day 96 rows will come.it has to done through ssis(sql server integration services)but not through stored procedures........plz help me if any one know
Post #1046318
Posted Wednesday, January 12, 2011 2:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 2:28 AM
Points: 357, Visits: 310
Here you go...

Not the most effecient solution, but it will do


set dateformat ymd
declare @start datetime
declare @increment float
declare @end datetime
set @start=(select cast('2000/01/01' as datetime))
set @end =(select cast('2031/01/01' as datetime))


if exists(select * from sysobjects where name ='Time')
drop table [Time]

Create table [Time]([Date] datetime not null)

while @start <= @end
begin
insert into [Time]
select @start
set @start=@start +(cast(1 as float)/24/4)
end


select * from [time]

Post #1046326
Posted Wednesday, January 12, 2011 3:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 2:30 AM
Points: 19, Visits: 43
thank you very much..........
still i need to refine like this........here we r printing year,mth,day in a single column..can u tell how to put that yr,mth,day in a seperate columns..
can u explain..........@start +(cast(1 as float)/24/4........i did not understand 1 as float
Post #1046351
Posted Wednesday, January 12, 2011 3:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 2:28 AM
Points: 357, Visits: 310

thank you very much..........
still i need to refine like this........here we r printing year,mth,day in a single column..can u tell how to put that yr,mth,day in a seperate columns..
can u explain..........@start +(cast(1 as float)/24/4........i did not understand 1 as float


Pleasure,

There are sql functions which can strip date parts from a date. For your first question,
Select Year([date]),month([Date]), day([Date]) 
from [Time]

Have a look at BOL for dateparts

secound question,
By adding a value 1 to a date you get the next day value. So in theory 1=1 day. Your reqiurement needed to be incremented by 15min.
So 1/12, will give us an hour value further /4 will give us quarter of an hour.

the cast ( as float) forces SQL not to look at it as an integer, because we are adding decimals.

hope this makes sense.

cheers,
Shanu
Post #1046354
Posted Wednesday, January 12, 2011 10:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 2:30 AM
Points: 19, Visits: 43
thank you .......
can we do the same thing through ssis in sql server..........
if possible plz tell me..........
waiting for ur reply
Post #1046998
Posted Wednesday, January 12, 2011 10:43 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 2:28 AM
Points: 357, Visits: 310
Yes you can,

But give it a shot and post for any assistance reqiured.

Post #1047008
Posted Thursday, January 13, 2011 3:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 2:30 AM
Points: 19, Visits: 43
can u tell me in detail .......how to do that one in ssis......
Post #1047108
Posted Thursday, January 13, 2011 3:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:40 AM
Points: 13,723, Visits: 10,678
gupta.sreekanth (1/13/2011)
can u tell me in detail .......how to do that one in ssis......


Sorry if this sounds harsh, but shouldn't you at least try it yourself?
All the people on this forum are unpaid volunteers. You cannot expect them to write free code for you all the time. We are all glad to help in the case you are stuck, but you have to do at least some effort yourself.
If you don't want to, hire a consultant to do it for you.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1047112
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse