troubleshooting Question

Selecting two counts from the same table with different where clauses

Avatar of havesum2
havesum2Flag for United States of America asked on
Microsoft SQL ServerWindows Server 2003Microsoft Server OS
9 Comments1 Solution345 ViewsLast Modified:
I'm trying to run a query that will return the number of employees that clocked in on a certain day grouped by department and position.  I have no problem getting this with one count but now I need to get the count from the date's previous year and compare them in one table.  

Here is my query:

USE TimeClockPlus 

DECLARE @Date as smalldatetime
SET @Date = '1/28/2012'


SELECT DISTINCT
	Business,
	MajDept,
	MinDept,
	[Position],
	count(DISTINCT CurrEmpHours.EmployeeID) as [Headcount 2012],
	count(DISTINCT PrevEmpHours.EmployeeID) as [Headcount 2011]
FROM
	EmployeeHours CurrEmpHours
		LEFT OUTER JOIN
			HR.dbo.JobCodes on
				CurrEmpHours.JobCode = JobCodes.JobCode 
					LEFT OUTER JOIN EmployeeHours PrevEmpHours ON CurrEmpHours.JobCode = PrevEmpHours.JobCode 
WHERE
	(
	CurrEmpHours.TimeIn < DATEADD(day, 1,@Date)
	) AND (
	CurrEmpHours.TimeIn >= @Date
	) AND (
	SUBSTRING( CAST( CAST( CurrEmpHours.JobCode AS int) AS char(6)), 5, 2) NOT IN (96, 97, 98, 99)
	)
	AND 
	(
	PrevEmpHours.TimeIn < DATEADD(week, -52, DATEADD(day, 1,@Date))
	) AND (
	PrevEmpHours.TimeIn >= dateadd(week, -52, @Date)
	) AND (
	SUBSTRING( CAST( CAST( PrevEmpHours.JobCode AS int) AS char(6)), 5, 2) NOT IN (96, 97, 98, 99)
	)
group by
	Business, MajDept, MinDept, [Position]
order by
	Business, MajDept, MinDept, [Position]


Results:
Business   MajDept          MinDept         Position               Headcount 2012 Headcount 2011
---------- ---------------- --------------- ---------------------- -------------- --------------
Gaming     General Office   Audit           Auditor                1              1
Gaming     General Office   Audit           Compliance Director    1              1
Gaming     Marketing        Admin           Clerk                  1              1
Gaming     Marketing        Winners Circle  Clerk                  8              10

Desired Results:
Business   MajDept          MinDept         Position               Headcount 2012 Headcount 2011
---------- ---------------- --------------- ---------------------- -------------- --------------
Gaming     General Office   Audit           Auditor                1              1
Gaming     General Office   Audit           Compliance Director    1              1
Gaming     Marketing        Admin           Clerk                  1              1
Gaming     Marketing        Admin           Promotions Specialist  1              NULL
Gaming     Marketing        Admin           Manager                NULL           1
Gaming     Marketing        Winners Circle  Clerk                  8              10

When I run this I get only the positions that had people that clocked in for both the current year and the previous year.  For example, Promotions Specialist had someone clock in on 1/28/2012 but not on 1/29/2011.  Because of my join I would think both counts would show up but with a null value for 2011.  The counts with any expected null values don't show up.  I would think a left join would produce the correct results but it doesn't.  I have tried different joins but none seem to show the results I need.  Is the problem even my join?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros