Argthtjtr

I have two columns: Start_Date and End_Date.

The task is to return a time to end of next day (using T-SQL, SSMS 14.0).

For example:

Start_Date = '2018-11-21 23:40:00'          End_Date = '2018-11-23 02:40:00'

The query should return 3 rows:

0 hour 20 min -- time between 2018-11-21 23:40:00 and 2018-11-22 00:00:00

24 hour 0 min -- time between 2018-11-22 00:00:00 and 2018-11-23 00:00:00

2 hour 40 min -- time between 2018-11-23 00:00:00 and 2018-11-23 02:40:00

I suppose it can be implemented using the cursor or recursive CTE.

Yes, CTE can be used with contribution of DATEDIFF and DATEADD functions together as :

CREATE VIEW DAYS AS

WITH CTE AS (

  SELECT 1 as nr

  UNION ALL

  SELECT nr+1

  FROM CTE 

  WHERE nr <= DATEDIFF( day, CONVERT(date, '2018-11-21 23:40:00'),

                             CONVERT(date, '2018-11-23 02:40:00') )

)

SELECT 

   DATEDIFF(

             minute, 

             (case when nr > 1 then 

                   DATEADD ( day , nr - 1 , CONVERT(date, '2018-11-21 23:40:00') )

              else

                   DATEADD ( day , nr - 1 , CONVERT(datetime, '2018-11-21 23:40:00'))

              end),



             (case when nr < 3 then 

                   DATEADD ( day , nr , CONVERT(date, '2018-11-21 23:40:00') )

              else

                   DATEADD ( day , nr - 3, CONVERT(datetime, '2018-11-23 02:40:00'))

              end)

               ) as time_diff                         

  FROM CTE;



SELECT cast(time_diff/60 as varchar) + ' hours '

     + cast(time_diff % 60 as varchar) + ' minutes' as "Time Difference" FROM DAYS;



Time Difference

-------------------

 0 hours 20 minutes

24 hours  0 minutes

 2 hours 40 minutes

dbfiddle demo

You can get your desired results as

WITH Dates AS

(

  SELECT 1 RN,



         @sd StartD,

         CAST(DATEADD(Day, -1, @ed) AS DATE) EndD

  UNION

  SELECT 2,

         CAST(DATEADD(Day, 1, @sd) AS DATE),

         CAST(@ed AS DATE)

  UNION 

  SELECT 3,

         CAST(@ed AS DATE),

         @ed

)

SELECT 

 CAST( DATEDIFF(YEAR, StartD, EndD) AS VARCHAR(10) )+ ' Years, '+

 CAST( DATEDIFF(MONTH, StartD, EndD)%12 AS VARCHAR(10) )+ ' Months, '+

 CAST( (DATEDIFF(DAY, StartD, EndD)%31)%12 

 - CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) )+ ' Days, '+

 CAST( (DATEDIFF(HOUR, StartD, EndD)%60)%24 

 - CASE WHEN RN = 1 THEN RN ELSE 0 END AS VARCHAR(10) ) + ' Hours,'+

 CAST( DATEDIFF(MINUTE, StartD, EndD)%60 AS VARCHAR(10) ) +' Minutes, '+

 CAST( DATEDIFF(SECOND, StartD, EndD)%60 AS VARCHAR(10) ) +' Seconds' AS Results

FROM Dates;

Results:

+----------------------------------------------------------+

|                         Results                          |

+----------------------------------------------------------+

| 0 Years, 0 Months, 0 Days, 0 Hours,20 Minutes, 0 Seconds |

| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds  |

| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |

+----------------------------------------------------------+

Demo

Finally, as a note I think your results should be

+----------------------------------------------------------+

|                         Results                          |

+----------------------------------------------------------+

| 0 Years, 0 Months, 1 Days, 1 Hours,20 Minutes, 0 Seconds |

| 0 Years, 0 Months, 1 Days, 0 Hours,0 Minutes, 0 Seconds  |

| 0 Years, 0 Months, 0 Days, 2 Hours,40 Minutes, 0 Seconds |

+----------------------------------------------------------+

if I'm right, then all what you need to do is removing the two (2) CASE expressions.

I believe you will want the ability to apply this to a variety of task durations. Here I have used a dynamic "tally table" which just a series of numbers from 0 to (in this case) 100, if you need a greater range amend the Tally cte by adding extra cross joins. If you already have a table of numbers, or your own preferred method for this substitute that for mine.

CREATE TABLE mytable(

   ID Integer NOT NULL

  ,Start_Date datetime NOT NULL

  ,End_Date   datetime NOT NULL

);

INSERT INTO mytable(id,Start_Date,End_Date) VALUES (1,'2018-11-21 23:40:00','2018-11-23 02:40:00');

INSERT INTO mytable(id,Start_Date,End_Date) VALUES (2,'2018-11-23 13:40:00','2018-11-23 22:40:00');

INSERT INTO mytable(id,Start_Date,End_Date) VALUES (3,'2018-11-18 23:40:00','2018-11-23 02:40:00');

3 test cases, as given, + a very short duration + a longer duration:

;WITH

Digits 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

          )

, Tally AS (

          SELECT  [ones].digit

                + [tens].digit * 10

                -- + [hundreds].digit * 100

                AS number

          FROM Digits [ones]

          CROSS JOIN Digits [tens]

          -- CROSS JOIN Digits [hundreds]

          )

select 

     ID

    , cast(ca2.minutes/60 as varchar) + ' hours '

      + cast(ca2.minutes % 60 as varchar) + ' minutes' as Duration

    , format(on_date,'yyyy-MM-dd') on_date

    , portion

    , format(start_date,'yyyy-MM-dd hh:mm') start_date

    , format(end_date,'yyyy-MM-dd hh:mm') end_date

from mytable t

inner join Tally on Tally.number <= datediff(dd,t.Start_Date,t.End_Date)

cross apply (

    select dateadd(dd,tally.number,dateadd(dd,datediff(dd,0,t.start_date),0))  on_date

    ) ca

cross apply (

    select

      case

           when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then datediff(minute,t.start_date,t.end_date)

           when cast(t.end_date as date) = ca.on_date then datediff(minute,ca.on_date,t.end_date)

           when ca.on_date between t.start_date and t.end_date then 24*60

           when ca.on_date < t.start_date then datediff(minute,t.start_date,dateadd(dd,1,ca.on_date))

      end minutes

    , case

           when t.start_date >= ca.on_date and t.end_date <= dateadd(dd,1,ca.on_date) then 'is on'

           when cast(t.end_date as date) = ca.on_date then 'end'

           when ca.on_date between t.start_date and t.end_date then 'span' 

           when ca.on_date < t.start_date then 'start'

      end portion

    ) ca2

I have used 2 apply operators, the first turns a number from the Tally into a date, and this is then re-used by the alias on_date in the second apply where we can calculate the duration. Note the second case expression portion is not essential, it's just there to help document the logic.

Result:

       ID        Duration         on_date     portion      start_date          end_date      

 ---- ---- -------------------- ------------ --------- ------------------ ------------------ 

   1    1   0 hours 20 minutes   2018-11-21   start     2018-11-21 11:40   2018-11-23 02:40  

   2    1   24 hours 0 minutes   2018-11-22   span      2018-11-21 11:40   2018-11-23 02:40  

   3    1   2 hours 40 minutes   2018-11-23   end       2018-11-21 11:40   2018-11-23 02:40  

   4    2   9 hours 0 minutes    2018-11-23   is on     2018-11-23 01:40   2018-11-23 10:40  

   5    3   0 hours 20 minutes   2018-11-18   start     2018-11-18 11:40   2018-11-23 02:40  

   6    3   24 hours 0 minutes   2018-11-19   span      2018-11-18 11:40   2018-11-23 02:40  

   7    3   24 hours 0 minutes   2018-11-20   span      2018-11-18 11:40   2018-11-23 02:40  

   8    3   24 hours 0 minutes   2018-11-21   span      2018-11-18 11:40   2018-11-23 02:40  

   9    3   24 hours 0 minutes   2018-11-22   span      2018-11-18 11:40   2018-11-23 02:40  

  10    3   2 hours 40 minutes   2018-11-23   end       2018-11-18 11:40   2018-11-23 02:40  

also see: https://rextester.com/VPTI85082