[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Statement to count entries for each day of the month

Posted on 2008-06-19
9
Medium Priority
?
473 Views
Last Modified: 2010-04-21
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.
0
Comment
Question by:lyptus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21824075
declare @StartDate datetime, @Begin datetime, @End datetime
set @startdate = getdate()  --date doesn't matter here.

set @Begin = cast(cast(month(getdate()) as varchar(2)) + '/1/' + cast(year(getdate()) as varchar(4)) as datetime)
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

0
 

Author Comment

by:lyptus
ID: 21825093
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.
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)

Open in new window

0
 

Author Comment

by:lyptus
ID: 21825164
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.
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)

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 60

Expert Comment

by:chapmandew
ID: 21825193
Not sure what you mean...it should give you the count.  Am I missing something?
0
 

Author Comment

by:lyptus
ID: 21825423
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              
0
 
LVL 7

Accepted Solution

by:
Cedric_D earned 2000 total points
ID: 21825489
-- input table:
declare @t table (id int, StartDate datetime, EndDate datetime)
insert into @t (id, StartDate, EndDate) values (1, '05/03/2008', '05/12/2008')
insert into @t (id, StartDate, EndDate) values (2, '05/10/2008' , '05/20/2008')
insert into @t (id, StartDate, EndDate) values (4, '05/19/2008',       Null  )

--

declare @t2 table (day int, count int)

declare @d int, @StartDate datetime, @EndDate datetime
select @d = 1, @StartDate = '05/01/2008', @EndDate = '06/01/2008'

while @StartDate < @endDate
begin
      insert into @t2 (day, count)
      select datepart(day, @StartDate), count(*)
      from @t
      where (StartDate is null or StartDate <= @StartDate)
            and (EndDate is null or EndDate >= @StartDate)
      
      set @StartDate = dateadd(day, 1, @StartDate)
end

select * from @t2


0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21825490
Ahhh...thats a different monster entirely....I can't really see a set-based solution for this.  Some looping is going to be required.
0
 
LVL 7

Expert Comment

by:Cedric_D
ID: 21825535
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.
0
 

Author Closing Comment

by:lyptus
ID: 31468877
Thanks for the post. That worked great!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question