TSQL to prepare 15 Min Chunk Data

Question

I am having a warehouse Fact Table containing raw data (Even Duplicate Data) received from vendor through a data feed. I need to prepare the chunk of 15 Min Interval data. How can I best SQL Server query to do this. E.g.Sample Data

ID key Date                              Value
1  1    2013-10-08 00:00:00.000       10 
2  1    2013-10-08 00:23:00.000       15 
3  1    2013-10-08 01:00:00.000       20    
4  1    2013-10-08 01:15:00.000       25 
5  1    2013-10-08 01:30:00.000       30 
6  1    2013-10-08 01:35:00.000       30 
7  1    2013-10-08 01:50:00.000       30 
8  1    2013-10-08 01:55:00.000       30

Kindly post your expected output. – Felix Pamittan Mar 05 '15 at 07:51 — Felix Pamittan, Mar 05 '15 at 07:51
what time dimension tables do you have? won't they be used? – Paul Maxwell Mar 05 '15 at 08:30 — Paul Maxwell, Mar 05 '15 at 08:30

score 0 · Answer 1 · answered Mar 05 '15 at 08:19

Batching in fixed batches for each quarter of the hour, by constructing a new column that specifies the start of each batch:

SELECT
    *,
    CASE
        WHEN (DATEPART(minute, [Date]) >= 0 AND DATEPART(minute, [Date]) < 15) THEN DATETIMEFROMPARTS (DATEPART(year, [Date]), DATEPART(month, [Date]), DATEPART(day, [Date]), DATEPART(hour, [Date]), 0, 0, 0)
        WHEN (DATEPART(minute, [Date]) >= 15 AND DATEPART(minute, [Date]) < 30) THEN DATETIMEFROMPARTS (DATEPART(year, [Date]), DATEPART(month, [Date]), DATEPART(day, [Date]), DATEPART(hour, [Date]), 15, 0, 0)
        WHEN (DATEPART(minute, [Date]) >= 30 AND DATEPART(minute, [Date]) < 45) THEN DATETIMEFROMPARTS (DATEPART(year, [Date]), DATEPART(month, [Date]), DATEPART(day, [Date]), DATEPART(hour, [Date]), 30, 0, 0)
        ELSE DATETIMEFROMPARTS (DATEPART(year, [Date]), DATEPART(month, [Date]), DATEPART(day, [Date]), DATEPART(hour, [Date]), 45, 0, 0)
    END AS BatchStart
FROM
    Fact
ORDER BY
    [Date]

Result for your example:

ID  key  Date                     Value  BatchStart
1   1    2013-10-08 00:00:00.000  10     2013-10-08 00:00:00.000
2   1    2013-10-08 00:23:00.000  15     2013-10-08 00:15:00.000
3   1    2013-10-08 01:00:00.000  20     2013-10-08 01:00:00.000
4   1    2013-10-08 01:15:00.000  25     2013-10-08 01:15:00.000
5   1    2013-10-08 01:30:00.000  30     2013-10-08 01:30:00.000
6   1    2013-10-08 01:35:00.000  30     2013-10-08 01:30:00.000
7   1    2013-10-08 01:50:00.000  30     2013-10-08 01:45:00.000
8   1    2013-10-08 01:55:00.000  30     2013-10-08 01:45:00.000

score 0 · Answer 2 · answered Mar 05 '15 at 08:34

0

This will round down to nearest 15 minutes

SELECT dateadd(minute, datediff(minute, 0, Date)/15*15, 0) 
FROM yourtable

answered Mar 05 '15 at 08:34

t-clausen.dk

43,517
12
59
92

TSQL to prepare 15 Min Chunk Data

2 Answers2