CODEWITHSUNDEEP
sqlsql-server
lnjblue
lnjblue

Reputation: 150

How to do a complex where clause involving dates

My table looks like this:

Customer     Enrolled
Emp1         01/01/2014
Emp1         02/01/2014
Emp1         03/01/2014
Emp1         04/01/2014
Emp1         05/01/2014
.
.
.
Emp1         12/01/2014
Emp2         12/01/2015

I have to identify if an employee was enrolled for an entire year- i.e. has all rows in column 1/1/2014 through 12/01/2014.

I've thought about using a case statement but I'm unsure of what the best approach would be.

This is my current query:

select distinct EMPLOYEE from TABLE
WHERE ENROLLED = '2014-01-01' AND ENROLLED = '2014-02-01' AND ENROLLED = '2014-03-01' 
AND ENROLLED = '2014-04-01' AND ENROLLED = '2014-05-01' AND ENROLLED = '2014-06-01' 
AND ENROLLED = '2014-07-01' AND ENROLLED = '2014-08-01' AND ENROLLED = '2014-09-01' 
AND ENROLLED = '2014-10-01' AND ENROLLED = '2014-11-01' AND ENROLLED = '2014-12-01'

Maybe I'm just looking at it wrong or over complicating it.

Upvotes: 0

Views: 37

Answers (2)

Stephan Lechner
Stephan Lechner

Reputation: 35154

You can do this as a general query that gives you the result for each such customer and each such year:

select customer, year(enrolled)
from table
group by customer, year(enrolled)
having count(distinct(enrolled))=12

If you are interested only in a particular year or in a particular customer, you can add a where-condition like where year(enrolled)=1024 and customer='emp1'

Upvotes: 4

Xavier J
Xavier J

Reputation: 4728

Try this - this will count how many months.

select count(Distinct(Enrolled)) from [TABLE] where Enrolled between '1-1-2014' and '12-1-2014' and Customer='Emp1'

Upvotes: 2

Related Questions