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:49 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
Post #1046320
Posted Wednesday, January 12, 2011 2:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
Here is one way of doing it. Maybe there is also a nice way of doing it using recursive CTE
create table TimeTable (dt datetime)

insert into TimeTable (dt) values ('20000101')

declare @Interval int
set @Interval = 15

--In the loop I muliply the value of @interval each time, and then I use it
--to add it to the value of dt column of all records in the table.
while not exists (select * from TimeTable where dt >= '20291231 23:45:00')
begin
insert into TimeTable (dt)
select DATEADD(mi,@Interval,dt)
from TimeTable
where DATEADD(mi,@Interval,dt) < '20300101'

select @Interval = @Interval * 2
end

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1046336
Posted Wednesday, January 12, 2011 2:58 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
repeated thread

http://www.sqlservercentral.com/Forums/Topic1046318-391-1.aspx
Post #1046341
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse