SQL Loop Count of people in program during specified duration
I'm not sure if there should be a loop for this or what the easiest approach would be.
My data consists of a list of people participating in our program. They have various start and end dates, but the following equation is able to capture the number of people who participated on a specific date:
Is there a way I can loop in different date values to get the number of program participants each day for an entire year?
Multiple years?
One simple way is to use a CTE to generate the dates and then a left join to bring in the data. For instance, the following gets the counts as of the first of the month for this year:
Note that this will work best for a handful of dates. If you want more than 100, you need to add option (maxrecursion 0) to the end of the query.
Also, count(people) is highly suspicious. Perhaps you mean sum(people) or something similar.
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
