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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
232 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

3873 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
Aged Yak Warrior

USA
550 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
232 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
232 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  
 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.06 seconds. Powered By: Snitz Forums 2000