SQL Statement to count entries for each day of the month
Posted on 2008-06-19
I am working on writting a sql statemtent for a sql 2005 report. I need to show the highest number of guests for each day of a given month. The user will select the month. I set this as @StartDate. Behind the scenes I set @NextDate which will be 1 month later. So if a user selects May. I set @StartDate to 5/1/2008 and @NextDate to 6/1/2008 The table I am going against is named Guest. It has 3 columns.
ID StartDate EndDate
1 04/28/2008 05/01/2008
2 05/2/2008 05/15/2008
3 04/25/2008 06/2/2008
4 05/15/2008 Null (Null would mean they are still there)
Based on this data I need a sql statement that will go through and list the day and total number of guests. So if someone selected May.
All the way to Day 31. If they select April the day would only go to 30.
I'm not sure where to even start with this. Thanks for your help.