Problem with COUNT..please help...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with COUNT..please help...

Author

Topic

nebula81
Starting Member

4 Posts

Posted - 2013-04-08 : 05:01:33

Hi,

I still new with SQL server 2005. i have problem to create view.
I have 2 tables. Table 1 consist of Name, StartDate & EndDate. Table 2 is just a list of date (where i want to calculate how may records falls in these date)

For Example
Table 1:
Name StartDate EndDate
Sarah 1/1/2013 1/2/2013
Mike 1/2/2013 1/4/2013
John 1/1/2013 1/2/2013
Jill 1/2/2013 1/4/2013

Table 2:
1/1/2013
1/2/2013
1/3/2013
1/4/2013

The result should be like this:
Date NoOfRecord
1/1/2013 2
1/2/2013 4
1/3/2013 2
1/4/2013 2

I don't know how to write SQL to come out with this result.

Please help

Thank you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-08 : 05:31:44

SELECT c2.Dates, COUNT(*) cnt
FROM Table1 c1
JOIN Table2 c2 ON c2.Dates BETWEEN c1.StartDate AND c1.EndDate
GROUP BY c2.Dates