SQLTeam.com | Weblogs | Forums

SQL Sum duration by hour and day

sql2005

#1

Hi there,
I have this dataset:

CREATE TABLE #yourtable
    ([Description] varchar(3), [Start Date] datetime, [End Date] datetime)
;
    
INSERT INTO #yourtable
    ([Description], [Start Date], [End Date])
VALUES
    ('ABC', '2015-08-17 10:30:30', '2015-08-17 13:00:30'),
    ('ABC', '2015-08-18 11:00:00', '2015-08-18 11:30:00'),
    ('DEF', '2015-08-17 08:25:00', '2015-08-17 10:30:00')
;

I need an ouput like this, what should I do ?
db

Any help would be appreciated.
Thanks


#2

please explain why 2015-08-17 starts with 10 and not 5 or9 or 7


#3

because the event start at 10:30:30 and ends in 13:00:30


#4

Looks a bit messy, but I belive it'll work for your situation:

with cte_tally
  as (select row_number() over(order by (select null))-1 as n
        from             (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_tally1(n) /* 12 hours */
/*****
 * uncomment one line at the time, if above hour range is too small
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_tally2(n) /* 144 hours = 6 days */
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_tally3(n) /* 1728 hours = 72 days ~= 2½ months */
 *
 *****/
     )
select a.[description]
      ,cast(dateadd(hour,b.n,a.[start date]) as date) as [date]
      ,datepart(hour,dateadd(hour,b.n,a.[start date])) as hour
      ,datediff(second
               ,case
                   when b.n=0
                   then a.[start date]
                   else dateadd(hour,datediff(hour,0,a.[start date])+b.n,0)
                end
               ,case
                   when dateadd(hour,b.n+1,a.[start date])>=a.[end date]
                   then a.[end date]
                   else dateadd(hour,datediff(hour,0,a.[start date])+b.n+1,0)
                end
               )
       as [duration(secs)]
      ,datediff(second
               ,case
                   when b.n=0
                   then a.[start date]
                   else dateadd(hour,datediff(hour,0,a.[start date])+b.n,0)
                end
               ,case
                   when dateadd(hour,b.n+1,a.[start date])>=a.[end date]
                   then a.[end date]
                   else dateadd(hour,datediff(hour,0,a.[start date])+b.n+1,0)
                end
               )
      /60.0
       as [duration(min)]
  from #yourtable as a
       inner join cte_tally as b
               on b.n<=datediff(hour,a.[start date],a.[end date])
 order by a.[description]
         ,a.[start date]
         ,b.n
;

#5
INSERT INTO #yourtable VALUES
    ('TTT', '2015-08-17 07:11:00', '2015-08-20 23:44:00') /*confirm cross-days handled*/

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
    CROSS JOIN cteTally10 c3
)
SELECT 
    yt.Description,
    CAST(Start_Hour AS date) AS Date,
    DATEPART(HOUR, Start_Hour) AS Hour,
    [DurationSecs],
    CAST([DurationSecs] / 60.0 AS decimal(6, 2)) AS [Duration(Min)]
FROM #yourtable yt
INNER JOIN cteTally1000 t ON t.number BETWEEN 0 AND DATEDIFF(HOUR, [Start Date], [End Date])
CROSS APPLY (
    SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, [Start Date]) + t.number, 0) AS Start_Hour
) AS alias1
CROSS APPLY (
    SELECT
    DATEDIFF(SECOND, CASE WHEN t.number = 0 THEN [Start Date] ELSE Start_Hour END,
        CASE WHEN [End Date] >= DATEADD(HOUR, 1, Start_Hour) 
             THEN DATEADD(HOUR, 1, Start_Hour)
             ELSE [End Date] END
        ) AS [DurationSecs]
) AS alias2
ORDER BY Description, Date, Hour

#6

another

CREATE TABLE #yourtable
    ([Description] varchar(3), [Start Date] datetime, [End Date] datetime)
;
    
INSERT INTO #yourtable
    ([Description], [Start Date], [End Date])
VALUES
    ('ABC', '2015-08-17 10:30:30', '2015-08-17 13:00:30'),
    ('ABC', '2015-08-18 11:00:00', '2015-08-18 11:30:00'),
    ('DEF', '2015-08-17 08:25:00', '2015-08-17 10:30:00')

;with 
cte_tally
as
(
select * from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))  a(x)
)

,cte_tally_2
as
(
select ROW_NUMBER() over(order by (select null)) id
from cte_tally a
,cte_tally b
,cte_tally c
,cte_tally d
)
,cte 
as
(
select * 
,dateadd(hour,  DATEDIFF(HOUR, 0, a.[Start Date]) + b.x - 1 , 0 ) [newStrDt]
,dateadd(hour,  DATEDIFF(HOUR, 0, a.[Start Date]) + b.x , 0 ) [newEndDt]

from #yourtable a 
outer apply
(
select top ( DATEDIFF(HOUR,a.[start Date], a.[End Date] ) + 1)  b.id  x
from cte_tally_2 b
order by b.id 
) b

)

select a.Description 
,convert(varchar(10), a.[Start Date], 103) [Date]
,DATEPART(hour, a.newStrDt) [Hour]
,DATEDIFF(second, (case when a.[Start Date] > a.newStrDt then a.[Start Date] else a.newStrDt end), 
 (case when a.[End Date] < a.newEndDt then a.[End Date] else a.newEndDt end)) [Duration (Secs)]

,cast(DATEDIFF(second, (case when a.[Start Date] > a.newStrDt then a.[Start Date] else a.newStrDt end), 
 (case when a.[End Date] < a.newEndDt then a.[End Date] else a.newEndDt end)) / 60.0 as decimal(6,2)) [Duration (Min)]


from cte  a

drop table #yourtable

#7

hi

trying something different

recursive cte

drop create data
USE tempdb 

go 

DROP TABLE data 

go 

CREATE TABLE data 
  ( 
     [description] VARCHAR(3), 
     [startdate]  DATETIME, 
     [enddate]    DATETIME 
  ); 

INSERT INTO data 
            ( 
             [description], 
             [startdate], 
             [enddate]) 
VALUES      ('ABC', 
             '2015-08-17 10:30:30', 
             '2015-08-17 13:00:30'), 
            ('ABC', 
             '2015-08-18 11:00:00', 
             '2015-08-18 11:30:00'), 
            ('DEF', 
             '2015-08-17 08:25:00', 
             '2015-08-17 10:30:00'); 
             
select * from data 
go
SQL recursive cte
;WITH rec__cte 
     AS (SELECT TOP 1 rn =1, 
                      N = startdate, 
                      Dateadd(hour, CASE WHEN Datepart(minute, startdate) < 30 
                                    THEN 1 
                                    ELSE 0 END + 
                                    Datepart( hour, 
                                    startdate), Cast( 
                      CONVERT(VARCHAR(10), startdate, 
                      112 
                      ) 
                      AS DATETIME) 
                      ) AS ok, 
                      enddate 
         FROM   data 
         UNION ALL 
         SELECT rn + 1, 
                ok, 
                Dateadd(hour, 1, ok), 
                enddate 
         FROM   rec__cte 
         WHERE  enddate >= Dateadd(hour, 1, ok)) 
SELECT * 
FROM   rec__cte a 

go
Result


#8

Recursion is much slower than a tally table. It's also a more complex technique. My suggestion is to avoid recursion unless it's required.


#9

Thanks Scott..will keep it in mind
:slightly_smiling_face:
:slightly_smiling_face:


#10

As you suggested Scott .. thanks

I have tried with tally table ..

Its not the complete RESULT
but my proof of concept

drop create data
USE tempdb 

go 

DROP TABLE data 

go 

CREATE TABLE data 
  ( 
     [description] VARCHAR(3), 
     [startdate]  DATETIME, 
     [enddate]    DATETIME 
  ); 

INSERT INTO data 
            ( 
             [description], 
             [startdate], 
             [enddate]) 
VALUES      ('ABC', 
             '2015-08-17 10:30:30', 
             '2015-08-17 13:00:30'), 
            ('ABC', 
             '2015-08-18 11:00:00', 
             '2015-08-18 11:30:00'), 
            ('DEF', 
             '2015-08-17 08:25:00', 
             '2015-08-17 10:30:00'); 
             
select * from data 
go
SQL with tally CTE
;WITH tallycte 
     AS (SELECT N=0 
         UNION ALL 
         SELECT n + 1 
         FROM   tallycte 
         WHERE  n + 1 < 100), 
     cte 
     AS (SELECT * 
         FROM   data) 
SELECT a.startdate, 
       Dateadd(hour, n, a.startdate), 
       a.enddate, 
       n 
FROM   cte a 
       JOIN tallycte b 
         ON Dateadd(hour, n, a.startdate) <= a.enddate 
ORDER  BY a.startdate, 
          Dateadd(hour, n, a.startdate), 
          a.enddate 

go
Result


#11

hi Scott

One doubt here

I was trying to see how you were doing the solution
to learn how others do
so that i can pick up other's skills

I notice the following
when i use recursive CTE as tally table
and when i use your tally table
the results differ very much

Could not understand

if you could explain
in one word or two
thanks
:slight_smile:
:slight_smile:

Your SQL
;WITH ctetally10 
     AS (SELECT * 
         FROM   (VALUES(0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0)) AS numbers(number)), 
     ctetally1000 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) - 1 AS number 
         FROM   ctetally10 c1 
                CROSS JOIN ctetally10 c2 
                CROSS JOIN ctetally10 c3) 
SELECT yt.description, 
       Cast(start_hour AS DATE)                     AS Date, 
       Datepart(hour, start_hour)                   AS Hour, 
       [durationsecs], 
       Cast([durationsecs] / 60.0 AS DECIMAL(6, 2)) AS [Duration(Min)] 
FROM   yourtable yt 
       INNER JOIN ctetally1000 t 
               ON t.number BETWEEN 0 AND Datediff(hour, [start date], [end date] 
                                         ) 
       CROSS apply (SELECT Dateadd(hour, Datediff(hour, 0, [start date]) + 
                                         t.number, 0) 
                           AS 
       Start_Hour 
       ) AS alias1 
       CROSS apply (SELECT Datediff(second, CASE 
                                              WHEN t.number = 0 THEN 
                                              [start date] 
                                              ELSE start_hour 
                                            END, CASE 
                                                   WHEN 
                           [end date] >= Dateadd(hour, 1, 
                                         start_hour) 
                                                 THEN 
                                                   Dateadd(hour, 1, start_hour) 
                                                   ELSE [end date] 
                                                 END) AS [DurationSecs]) AS 
                   alias2 
ORDER  BY description, 
          date, 
          hour
Your Results

My Recursive CTE tally table SQL
;WITH recursivetallycte 
     AS (SELECT N=1 
         UNION ALL 
         SELECT n + 1 
         FROM   recursivetallycte 
         WHERE  n + 1 <= 1000) 
SELECT yt.description, 
       Cast(start_hour AS DATE)                     AS Date, 
       Datepart(hour, start_hour)                   AS Hour, 
       [durationsecs], 
       Cast([durationsecs] / 60.0 AS DECIMAL(6, 2)) AS [Duration(Min)] 
FROM   yourtable yt 
       INNER JOIN recursivetallycte t 
               ON t.n BETWEEN 0 AND Datediff(hour, [start date], [end date]) 
       CROSS apply (SELECT Dateadd(hour, Datediff(hour, 0, [start date]) + t.n, 
                           0) AS 
                           Start_Hour) 
                              AS 
       alias1 
       CROSS apply (SELECT Datediff(second, CASE 
                                              WHEN t.n = 0 THEN [start date] 
                                              ELSE start_hour 
                                            END, CASE 
                                                   WHEN 
                           [end date] >= Dateadd(hour, 1, 
                                         start_hour) 
                                                 THEN 
                                                   Dateadd(hour, 1, start_hour) 
                                                   ELSE [end date] 
                                                 END) AS [DurationSecs]) AS 
                   alias2 
ORDER  BY description, 
          date, 
          hour 
OPTION (maxrecursion 1000)
My Results

`