Split records in half hours - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Split records in half hours

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-17 : 10:42:42

Hello all,
in my Select statement I have some records that have (with other fields)
StartDate and EndDate fields like these:

StartDate EndDate
2013-09-10 03:30:00.000 2013-09-10 03:44:59.000
2013-09-10 03:00:00.000 2013-09-10 03:29:59.000
2013-10-15 22:00:00.000 2013-10-15 22:59:59.000

Now I have to split these record in half hour blocks.

For example the first record will remain the same, because it has only
one quarter:

2013-09-10 03:30:00.000 2013-09-10 03:44:59.000

The second will be splittend in:

2013-09-10 03:00:00.000 2013-09-10 03:15:00.000
2013-09-10 03:15:00.000 2013-09-10 03:29:59.000

The third record will be splittend in:

2013-10-15 22:00:00.000 2013-10-15 22:15:00.000
2013-10-15 22:15:00.000 2013-10-15 22:30:00.000
2013-10-15 22:30:00.000 2013-10-15 22:45:00.000
2013-10-15 22:45:00.000 2013-10-15 22:59:59.000

And so on.
The other fields not change.

How can I realize this?

Thanks in advance.

Luis

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 11:36:16

Did you mean half hour interval or 15 minute interval?

IN any case, you need a numbers table. If you don't have one, construct one like this:

CREATE TABLE #N(n INT);
;WITH N(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 250)
INSERT INTO #N SELECT n FROM N OPTION (MAXRECURSION 0);

Then you can use the numbers table like in the example below. Not tested for edge cases, so may need minor tweaks

CREATE TABLE #tmp (StartDate DATETIME, endDate DATETIME);
INSERT INTO #tmp VALUES
('2013-09-10 03:30:00.000', '2013-09-10 03:44:59.000'),
('2013-09-10 03:00:00.000', '2013-09-10 03:29:59.000'),
('2013-10-15 22:00:00.000', '2013-10-15 22:59:59.000')



SELECT t.*,n.*,
	CASE WHEN DATEADD(mi,(DATEDIFF(mi,0,StartDate)/15+(n-1))*15,0) < StartDate THEN StartDate
	ELSE DATEADD(mi,(DATEDIFF(mi,0,StartDate)/15+(n-1))*15,0) END IntervalStart,
	CASE WHEN DATEADD(mi,(DATEDIFF(mi,0,StartDate)/15+n)*15,0) > EndDate  THEN EndDate
	ELSE DATEADD(mi,(DATEDIFF(mi,0,StartDate)/15+n)*15,0) END IntervalEnd
FROM
	#tmp t
	CROSS JOIN #N n 
WHERE  
		DATEADD(mi,(DATEDIFF(mi,0,StartDate)/15+(n-1))*15,0) <= t.EndDate
		
DROP TABLE #tmp

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-17 : 17:46:03

Here's my version. I use a CTE rather a physical numbers table, use CROSS APPLY to avoid repeating some calcs/computations, and I standardized the IntervalEnd times to be one second less than the start of the next range so that a single time value cannot fall into two different ranges.


;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
)
SELECT 
    CASE WHEN t.StartDate > DATEADD(MINUTE, (StartDate_Interval# + tally.tally) * 15, 0) THEN t.StartDate
         ELSE DATEADD(MINUTE, (StartDate_Interval# + tally.tally) * 15, 0) END AS IntervalStart,
    CASE WHEN t.EndDate < DATEADD(SECOND, -1, DATEADD(MINUTE, (StartDate_Interval# + tally.tally + 1) * 15, 0)) THEN t.EndDate
         ELSE DATEADD(SECOND, -1, DATEADD(MINUTE, (StartDate_Interval# + tally.tally + 1) * 15, 0)) END AS IntervalEnd
FROM #tmp t
CROSS APPLY (
    SELECT DATEDIFF(MINUTE, 0, t.StartDate) / 15 AS StartDate_Interval#, DATEDIFF(MINUTE, 0, t.EndDate) / 15 AS EndDate_Interval#
) AS ca1
INNER JOIN cteTally tally ON
    tally.tally <= EndDate_Interval# - StartDate_Interval#
ORDER BY
    1, 2

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-18 : 03:18:47

Yes, I mean blocks fo 15min, sorry James.
I'll try these solutions. Thank you all for now.

Luis

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-10-20 : 05:19:38

Just a little adding.
Is it possible to apply this splitting operation (in 15min blocks)
based on one field value?
In this case I should perform this splitting only if field:

IsSplittable = 1

while the other records will remain non splitted.

Luis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources