SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


generating or populating time dimensions


generating or populating time dimensions

Author
Message
gupta.sreekanth
gupta.sreekanth
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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
shanu.hoosen
shanu.hoosen
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 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]


gupta.sreekanth
gupta.sreekanth
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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
shanu.hoosen
shanu.hoosen
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 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
gupta.sreekanth
gupta.sreekanth
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 43
thank you .......
can we do the same thing through ssis in sql server..........
if possible plz tell me..........
waiting for ur reply
shanu.hoosen
shanu.hoosen
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 310
Yes you can,

But give it a shot and post for any assistance reqiured.
gupta.sreekanth
gupta.sreekanth
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 43
can u tell me in detail .......how to do that one in ssis......
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63408 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search