CODEWITHSUNDEEP
sql-servert-sql
user1501171
user1501171

Reputation: 230

Update CTE to insert create new record chaining datetimes

I have the following CTE:

with cte as 
(
    select 
        fromtime,
        (case 
            when datediff(day, fromtime, totime) = 0
               then totime
            else dateadd(day, 1, convert(date, fromtime))
         end) as dayendtime,
        totime
    from 
        t
    union all
    select 
        dayendtime as fromtime,
        (case 
            when datediff(day, dayendtime, totime) = 0
               then totime
               else dateadd(day, 1, dayendtime)
         end) as dayendtime,
        totime
    from  
        cte
    where 
        dayendtime < totime
)

Which outputs data like this:

enter image description here

And works great, but now I want to chain create a new row chaining the DayEndTime to the FromTime of the next record if the DayEndTime is midnight, for example:

enter image description here

Can anyone give me guidance on how this is possible? Thanks!

Upvotes: 0

Views: 74

Answers (1)

PeterHe
PeterHe

Reputation: 2766

with cte 
as (
      select fromtime,
             (case when datediff(day, fromtime, totime) = 0
                   then totime
                   else dateadd(day, 1, convert(date, fromtime))
              end) as dayendtime,
             totime
      from t
      union all
      select dayendtime as fromtime,
             (case when datediff(day, dayendtime, totime) = 0
                   then totime
                   else dateadd(day, 1, dayendtime)
              end) as dayendtime,
             totime
       from cte
       where dayendtime < totime
),
cte_new 
as (
  SELECT dayendtime AS fromtime,totime AS dayendtime,totime 
  FROM cte
  WHERE dayendtime=cast(dayendtime AS date)
)
SELECT *
FROM cte
UNION ALL
SELECT *
FROM cte_new
ORDER BY fromtime;

Upvotes: 1

Related Questions