## How to count active jobs at the end of each month across a period of time

 Author Message AndyAus Forum Newbie Group: General Forum Members Points: 4 Visits: 125 My apologies if this question has been answered elsewhere but I can’t find a solution. I need to count the number of active jobs that exist at the end of each month. A job is active if it has been received but not yet finished. The data looks like this:CREATE TABLE #Files(Job NVARCHAR(10), Received DATETIME, Finished DATETIME)INSERT INTO #Files (JOB, Received, Finished)SELECT 'A1', CAST('20120110' AS DATETIME), CAST('20120506' AS DATETIME) UNION ALLSELECT 'A23', CAST('20120113' AS DATETIME), NULL UNION ALLSELECT 'B4', CAST('20120221' AS DATETIME), CAST('20120224' AS DATETIME) UNION ALLSELECT 'L5', CAST('20120307' AS DATETIME), CAST('20120425' AS DATETIME) UNION ALLSELECT 'D5', CAST('20120316' AS DATETIME), NULL UNION ALLSELECT 'S3', CAST('20120323' AS DATETIME), CAST('20120510' AS DATETIME) UNION ALLSELECT 'D2', CAST('20120502' AS DATETIME), CAST('20120607' AS DATETIME)SELECT * FROM #Files ORDER BY 2, 3DROP TABLE #FilesSo, from the data above, I need to calculate the following:Date Active jobs20120131 220120229 220120331 520120430 420120531 320120630 2A job can be counted multiple times depending on when it was received and finished. Any advice would be appreciated. Thanks. AndyAus Forum Newbie Group: General Forum Members Points: 4 Visits: 125 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 CTEDatesLEFT OUTER JOIN #Files ON #Files.Received <= CTEDates.EndofMonth AND ( #Files.Finished IS NULL OR #Files.Finished > CTEDates.EndofMonth )GROUP BY CTEdates.EndofMonthDoes that make sense or is there a more efficient way of writing the query?