Home Forums SQL Server 2005 T-SQL (SS2K5) How to count active jobs at the end of each month across a period of time RE: How to count active jobs at the end of each month across a period of time

  • I think I may have found a solution. It's all in the joins. I created a recursive CTE to generate a framework of end-of-month dates covering the period of time required for the report. I then linked the job data back to the list of dates like this:

    SELECT CTEdates.EndofMonth, COUNT(Job)

    FROM CTEDates

    LEFT OUTER JOIN #Files ON #Files.Received <= CTEDates.EndofMonth

    AND ( #Files.Finished IS NULL OR #Files.Finished > CTEDates.EndofMonth )

    GROUP BY CTEdates.EndofMonth

    Does that make sense or is there a more efficient way of writing the query?