Roland
Roland

Reputation: 27

How to subtract an offset to dates generated through a recursive CTE?

The query below uses a recursive CTE to generate days if there is no data for that specific day. I want to group the daily downtime total starting at 7:15 the previous day until 7:15 the next day and do it over a month. This query works fine but I need to subtract DATEADD(minute, -(7 * 60 + 15) from each day.

WITH dates as (
      SELECT CONVERT(date, 'Anydate') as dte
      UNION ALL
      SELECT DATEADD(day, 1, dte)
      FROM dates
      WHERE dte < 'Anydate + 1 month later'
     )
SELECT CONVERT(datetime,d.dte), ISNULL(SUM(long_stop_minutes), 0) AS downtime
FROM dates d LEFT JOIN
     long_stops_table b
     ON CAST(t_stamp as DATE) = d.dte AND Type = 'downtime'
GROUP BY CONVERT(datetime, d.dte)
ORDER BY CONVERT(datetime, d.dte) ASC;

Upvotes: 1

Views: 193

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Just subtract the appropriate time units. Here is one way:

SELECT d.dte, 
       COALESCE(SUM(lst.long_stop_minutes), 0) AS downtime
FROM dates d LEFT JOIN
     long_stops_table lst
     ON CONVERT(date, DATEADD(minute, -(7 * 60 + 15), lst.t_stamp) = d.dte AND
        lst.Type = 'downtime'
GROUP BY d.dte
ORDER BY d.dte ASC;

I see no reason to convert dates.dte to a datetime, so I just removed the conversion.

Upvotes: 1

Related Questions