asked on

Time slote creation using TSQL

Hi,

I've a requirment to populate the data comming from main transaction system into different half an hour timeslot interval in Fact table.

What I need the data to be loaded as follows:

Transaction data:

Time measure
11.45 am 10
12.00 am 40
12.15 am 46
12.30 am 43
12.45 am 65
01.00 am 33
---
---
-- so on

how do I need to store in the fact table is:

Time Slote Measure
12am 50 ( add 11.45pm and 12am)
12.30am 89 ( add 12.15am and 12.30am)
1.00am 98 ( add 12.45am and 1.00am)

Please note: if the date is Eg: 2013-07-15: 11.45AM the data should be add into
next day which is 2013-07-16:12.00AM

Can anybody hel this urgently, if you need any information please let me know

PortletPaul

15 or 30 minutes?

well for one thing you might clarify the timeslots
you state "half an hour timeslot interval"
but display 15 minute intervals

it might also help to use 24 hour notation
you state that 2013-07-15: 23.45 the data should be add into next day
which is 2013-07-16 00:00
but again this is a 15 minute timeslot

and to be doubly sure, you really want data from a prior day added to the next?
(that would be unusual)

Fact table standards

it's pretty normal for "fact tables" to be based on existing site standards.
Does this fact table exist? or do you have to build it?
If you have to build it, are there other similar fact tables onsite?

ValentinoV

In addition to Paul: are you using SSIS or purely T-SQL to populate the fact table?

sachitjain

See if following example helps

set nocount on
declare @ts datetime, @measure int

declare @sourcetable table (ts datetime, measure int)
insert into @sourcetable values ('2013-07-15 11:45:00.000', 10)
insert into @sourcetable values ('2013-07-15 12:00:00.000', 40)
insert into @sourcetable values ('2013-07-15 12:15:00.000', 46)
insert into @sourcetable values ('2013-07-15 12:30:00.000', 43)
insert into @sourcetable values ('2013-07-15 12:45:00.000', 65)
insert into @sourcetable values ('2013-07-15 13:00:00.000', 33)
insert into @sourcetable values ('2013-07-15 23:45:00.000', 11)
insert into @sourcetable values ('2013-07-16 00:00:00.000', 22)

declare @targettable table ([timestamp] datetime, measure int)

DECLARE Facts CURSOR FAST_FORWARD FOR
SELECT ts, measure FROM @sourcetable order by ts
OPEN Facts
FETCH NEXT FROM Facts INTO @ts, @measure

WHILE (@@FETCH_STATUS = 0)
BEGIN
      declare @minute int, @targetdate datetime
      set @minute = DATEPART(MI, @ts)

      if @minute = 15 or @minute = 45
            set @targetdate = DATEADD(mi, 15, @ts)
      else
            set @targetdate = @ts

      MERGE @targettable AS stm
      USING (SELECT @targetdate timestamp, @measure measure) AS sd
      ON stm.[timestamp] = sd.[timestamp]
      WHEN MATCHED THEN UPDATE SET stm.measure = stm.measure + sd.measure
      WHEN NOT MATCHED THEN
      INSERT([timestamp],measure)
      VALUES(@targetdate, @measure);

      FETCH NEXT FROM Facts INTO @ts, @measure
END
CLOSE Facts
DEALLOCATE Facts

select * from @targettable

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

keplan

ASKER