Menu
I'm working on sql server 2008 r2. I'm trying to split a row by 24 hours period ranges between FromDate, Todate. For example, if a time row has given as below, (Range between FromDate, Todate is 4 days, so I want 4 rows)
ID FromDate Todate
---- ------------------------ -------------------------
1 2014-04-01 08:00:00.000 2014-04-04 12:00:00.000
The result I want to see like this:
ID FromDate Todate DateDiff(HH)
---- ------------------------ -----------------------------------
1 2014-04-01 08:00:00.000 2014-04-01 23:59:59.000 15
1 2014-04-02 00:00:00.000 2014-04-02 23:59:59.000 23
1 2014-04-03 00:00:00.000 2014-04-03 23:59:59.000 23
1 2014-04-04 00:00:00.000 2014-04-04 12:00:00.000 12
570
Remaining Minutes = (TotalSeconds % 3600) / 60 Remaining Seconds = (TotalSeconds % 60) (The % is the modulo operator in T-SQL, which returns the remainder when dividing two integers.) Thus, we can write our SQL like this to return 3 integer columns (Hours, Minutes, Seconds) for each event: Now our results like a lot nicer!
According to above output,there are shown only two hour's slot because only these hour's have a data. sql-serversql-server-2008sql-server-2008-r2
So, we can call DateDiff (second, Date1,Date2) and easily get the total number of seconds between two dates, which we can use to get the duration of each event in seconds: Now, technically, we have returned the durations -- but it may not be very useful in that format.
Based on this answer here, create a table with a row for each hour slot then outer join it to your results table, which will always give you a time row regardless of rows existing in your table
Try this query:
WITH TAB1 (ID,FROMDATE,TODATE1,TODATE) AS
(SELECT ID,
FROMDATE,
DATEADD(SECOND, 24*60*60 - 1, CAST(CAST(FROMDATE AS DATE) AS DATETIME)) TODATE1,
TODATE
FROM TABLE1
UNION ALL
SELECT
ID,
DATEADD(HOUR, 24, CAST(CAST(TODATE1 AS DATE) AS DATETIME)) FROMDATE,
DATEADD(SECOND, 2*24*60*60-1, CAST(CAST(TODATE1 AS DATE) AS DATETIME)) TODATE1,
TODATE
FROM TAB1 WHERE CAST(TODATE1 AS DATE) < CAST(TODATE AS DATE)
),
TAB2 AS
(SELECT ID,FROMDATE,
CASE WHEN TODATE1 > TODATE THEN TODATE ELSE TODATE1 END AS TODATE
FROM TAB1)
SELECT TAB2.*,
DATEPART(hh, TODATE) - DATEPART(hh, FROMDATE) [DateDiff(HH)] FROM TAB2;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
