Selecting two counts from the same table with different where clauses
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:
Results:
Desired Results:
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?
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 10Desired 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 10When 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked resulting in a LEFT JOIN that's showing all current year with a zero in the previous year that didn't have an employee clock in but what if I need a FULL OUTER JOIN that produces zeroes for the current year and the previous year?
Why do you think you need a FULL OUTER JOIN. Do you understand what is a FULL OUTER JOIN?
ASKER
I thought I did.
With your change my results come out as:
I need the data to include both:
With your change my results come out as:
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 Winners Circle Clerk 8 10I need the data to include both:
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
I am confused if you do this:
Do you get:
Business MajDept MinDept Position
---------- ---------------- --------------- ----------------------
Gaming General Office Audit Auditor
Gaming General Office Audit Compliance Director
Gaming Marketing Admin Clerk
Gaming Marketing Admin Promotions Specialist
Gaming Marketing Admin Manager
USE TimeClockPlus
DECLARE @Date AS smalldatetime
SET @Date = '20120128'
SELECT Business,
MajDept,
MinDept,
[Position]
FROM EmployeeHours CurrEmpHours
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)
GROUP BY Business,
MajDept,
MinDept,
[Position]
ORDER BY Business,
MajDept,
MinDept,
[Position]Do you get:
Business MajDept MinDept Position
---------- ---------------- --------------- ----------------------
Gaming General Office Audit Auditor
Gaming General Office Audit Compliance Director
Gaming Marketing Admin Clerk
Gaming Marketing Admin Promotions Specialist
Gaming Marketing Admin Manager
ASKER
If I run this:
I get:
SELECT Business,
MajDept,
MinDept,
[Position]
FROM EmployeeHours
inner JOIN HR.dbo.JobCodes on JobCodes.JobCode = EmployeeHours.JobCode
WHERE TimeIn < DATEADD(day, 1, @Date)
AND TimeIn >= @Date
AND SUBSTRING(CAST(CAST(EmployeeHours.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]I get:
Business MajDept MinDept Position
---------- ---------------- --------------- ----------------------
Gaming General Office Audit Auditor
Gaming General Office Audit Compliance Director
Gaming Marketing Admin Clerk
Gaming Marketing Admin Promotions Specialist
Gaming Marketing Admin Manager
Gaming Marketing Winners Circle Clerk
Please add the aliases to all columns, without know the shema it is really confusing to know what columns belong to what table.
Also this is unrelated to your problem but this:
AND SUBSTRING(CAST(CAST(CurrEm
Should be written:
AND SUBSTRING(CAST(CAST(CurrEm
Or simply (if I have the logic right):
AND CAST(CurrEmpHours.JobCode AS int) % 100 NOT IN (96, 97, 98, 99)
Out of curiousity what is the data type for CurrEmpHours.JobCode ?
AND SUBSTRING(CAST(CAST(CurrEm
Should be written:
AND SUBSTRING(CAST(CAST(CurrEm
Or simply (if I have the logic right):
AND CAST(CurrEmpHours.JobCode AS int) % 100 NOT IN (96, 97, 98, 99)
Out of curiousity what is the data type for CurrEmpHours.JobCode ?
And that would be because you are not doing a LEFT JOIN, but instead it is an INNER JOIN. If you need a LEFT JOIN than move the conditions for that table to the JOIN.