SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Problem with COUNT..please help...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nebula81
Starting Member

4 Posts

Posted - 04/08/2013 :  05:01:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 04/08/2013 :  05:31:44  Show Profile  Reply with Quote
SELECT c2.Dates, COUNT(*) cnt
FROM Table1 c1
JOIN Table2 c2 ON c2.Dates BETWEEN c1.StartDate AND c1.EndDate
GROUP BY c2.Dates
Go to Top of Page

nebula81
Starting Member

4 Posts

Posted - 04/08/2013 :  05:43:25  Show Profile  Reply with Quote
Hi bandi,

what is c1 and c2?

tq!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 04/08/2013 :  06:10:23  Show Profile  Reply with Quote
quote:
Originally posted by nebula81

Hi bandi,

what is c1 and c2?

tq!


c1 and c2 are alias names for respective tables

--
Chandu
Go to Top of Page

nebula81
Starting Member

4 Posts

Posted - 04/08/2013 :  23:27:57  Show Profile  Reply with Quote
Hi bandi,

it works! thank a lot!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 04/09/2013 :  00:18:41  Show Profile  Reply with Quote
quote:
Originally posted by nebula81

Hi bandi,

it works! thank a lot!





--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000