SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Split records in half hours
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
227 Posts

Posted - 10/17/2013 :  10:42:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/17/2013 :  11:36:16  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
271 Posts

Posted - 10/17/2013 :  17:46:03  Show Profile  Reply with Quote
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
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
227 Posts

Posted - 10/18/2013 :  03:18:47  Show Profile  Reply with Quote
Yes, I mean blocks fo 15min, sorry James.
I'll try these solutions. Thank you all for now.

Luis
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
227 Posts

Posted - 10/20/2013 :  05:19:38  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000