lyptus
asked on
SQL Statement to count entries for each day of the month
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.
Day Count
1 2
2 2
....
15 3
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.
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.
Day Count
1 2
2 2
....
15 3
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.
ASKER
Thanks for the post. I modifed the statment to work with my db
There seems to be 3 problems. @StartDate and @StartDateNext are getting their values in the code. I need them to be supplied. It also seems to be counting only the StartDate if it lands on the day. If someones start date is 5/1 and end date is 5/3 they would be included in the count for Day 1, 2, and 3. Right now they are only being included in the count for Day 1. Also the Days are only going from day 1 to 19.
Thanks.
There seems to be 3 problems. @StartDate and @StartDateNext are getting their values in the code. I need them to be supplied. It also seems to be counting only the StartDate if it lands on the day. If someones start date is 5/1 and end date is 5/3 they would be included in the count for Day 1, 2, and 3. Right now they are only being included in the count for Day 1. Also the Days are only going from day 1 to 19.
Thanks.
Declare @Start datetime, @StartDate datetime, @StartDateNext datetime
set @Start = getdate() --date doesn't matter here.
set @StartDate = cast(cast(month(getdate()) as varchar(2)) + '/1/' + cast(year(getdate()) as varchar(4)) as datetime)
set @StartDateNext = dateadd(m, 1, @StartDate)
SELECT DATEPART(d, StartDate) AS Day, COUNT(*) AS Count
FROM dbo.Guests
WHERE (StartDate >= @StartDate) AND (StartDate < @StartDateNext)
GROUP BY DATEPART(d, StartDate)
ASKER
I was able to modify it so I can supply the date and it is showing all the days of the month.
Now I just need the count.
Now I just need the count.
Declare @Start datetime
set @Start = getdate() --date doesn't matter here.
SELECT DATEPART(d, StartDate) AS Day, COUNT(*) AS Count
FROM dbo.BookingNbrs
WHERE (StartDate >= @StartDate) AND (StartDate < @StartDateNext)
GROUP BY DATEPART(d, StartDate)
Not sure what you mean...it should give you the count. Am I missing something?
ASKER
The count is not correct. Plus if no one has a date on the 5/1/2008 for example, it doesnt show day 2.
lets say this is the table
ID StartDate EndDate
1 05/3/2008 05/12/2008
2 05/10/2008 05/20/2008
3 05/19/2008 Null
this is what I am getting now.
Day Count
3 1
10 1
19 1
It is saying only 1 guy has a start day of the 3rd, 1 on the 10th, and 1 on the 19th.
The out put should be
Day Count
1 0 //No Guests
2 0 //No Guests
3 1 //ID 1 arrives
4 1
5 1
6 1
7 1
8 1
9 1 //ID 1 was the only guest the 1st throught the 9th
10 2 //ID 1 is still a guest and ID 2 arrived.
11 2
12 2
13 1 //ID 1 left on the 12th so he won't be included on the 13th
14 1
15 1
16 1
17 1
18 1
19 2 //ID 2 is still a guest and ID 3 arrives.
20 1 //ID 2 left on the 19th so he won't be included on the 20th
21 1
22 1
23 1
24 1
25 1
26 1
27 1
28 1
29 1
30 1 //if the month only has 30 days it would stop here.
31 1
lets say this is the table
ID StartDate EndDate
1 05/3/2008 05/12/2008
2 05/10/2008 05/20/2008
3 05/19/2008 Null
this is what I am getting now.
Day Count
3 1
10 1
19 1
It is saying only 1 guy has a start day of the 3rd, 1 on the 10th, and 1 on the 19th.
The out put should be
Day Count
1 0 //No Guests
2 0 //No Guests
3 1 //ID 1 arrives
4 1
5 1
6 1
7 1
8 1
9 1 //ID 1 was the only guest the 1st throught the 9th
10 2 //ID 1 is still a guest and ID 2 arrived.
11 2
12 2
13 1 //ID 1 left on the 12th so he won't be included on the 13th
14 1
15 1
16 1
17 1
18 1
19 2 //ID 2 is still a guest and ID 3 arrives.
20 1 //ID 2 left on the 19th so he won't be included on the 20th
21 1
22 1
23 1
24 1
25 1
26 1
27 1
28 1
29 1
30 1 //if the month only has 30 days it would stop here.
31 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ahhh...thats a different monster entirely....I can't really see a set-based solution for this. Some looping is going to be required.
This can be written (optimized) to be without cycle, however for that case you need prepared temp table of consequent numbers from 1 to 31, which require cycle though :)
If you wish, I will write this variant.
If you wish, I will write this variant.
ASKER
Thanks for the post. That worked great!
set @startdate = getdate() --date doesn't matter here.
set @Begin = cast(cast(month(getdate())
set @end = dateadd(m, 1, @Begin)
select datepart(d, datefield), count(*)
from yourtable
where datefield >= @Begin And
DateField < @End
group by datepart(d, datefield)
order by 1 desc