Solved

Changing the WHERE clause does not return what I am expecting

Posted on 2009-05-08
2
215 Views
Last Modified: 2012-05-06
With the help of Experts I finally got the following query working where it returned 1 record for each employer with a count of active employees, paidout employees and forfeiture employees.  Data set returned contains 3 rows with 5 columns.  So far so good until I discovered that my query for active employees was incorrect.  In the current query, the WHERE clause is Employees.EmployeeStatus='ACTIVE'.  I need to change the query to be WHERE Employees.PlanStatus<>'PAID OUT' AND Employees.PlanStatus<>'FORFEITURE'.  I thought this would be a simple change but when I made it, the data set returned contains 5 rows with 5 columns.  The one employer has an employee with a PlanStatus = "PAID OUT" and another Employee with a PlanStatus = "FORFEITURE".  As soon as I change the = to <> or add the second condition to the WHERE clause, more than one record is returned for the employer that has employees with different plan status.  Any insight into this problem would be appreciated.


SELECT a.EmployerName, a.EmployerID, b.ActiveEmployees, c.PaidOutEmployees, d.ForfeitureEmployees
FROM ((employers AS a LEFT JOIN (SELECT Count(Employees.EmployeeStatus) AS ActiveEmployees, Employees.EmployerID FROM Employees WHERE Employees.EmployeeStatus='ACTIVE' GROUP BY Employees.EmployerID, Employees.EmployeeStatus)  AS b ON a.EmployerID=b.EmployerID) LEFT JOIN (SELECT Count(Employees.PlanStatus) AS PaidOutEmployees, Employees.EmployerID FROM Employees WHERE Employees.PlanStatus='PAID OUT' GROUP BY Employees.EmployerID, Employees.PlanStatus)  AS c ON b.EmployerID=c.EmployerID) LEFT JOIN (SELECT Count(Employees.PlanStatus) AS ForfeitureEmployees, Employees.EmployerID FROM Employees WHERE Employees.PlanStatus='FORFEITURE' GROUP BY Employees.EmployerID, Employees.PlanStatus)  AS d ON c.EmployerID=d.EmployerID;

Open in new window

0
Comment
Question by:dyarosh
2 Comments
 
LVL 6

Accepted Solution

by:
RPCIT earned 500 total points
ID: 24338089
the below should do what you wanted.. it should always return the number of rows that are in your employers table..

I changed your group by clauses, and changed the joins to all join to a.

Hope this helps
SELECT a.EmployerName, a.EmployerID, b.ActiveEmployees, c.PaidOutEmployees, d.ForfeitureEmployees
FROM ((employers AS a 
LEFT JOIN (	SELECT Count(Employees.PlanStatus) AS ActiveEmployees, Employees.EmployerID 
			FROM Employees 
			WHERE Employees.PlanStatus <> 'PAID OUT' AND Employees.PlanStatus <> 'FORFEITURE' 
			GROUP BY Employees.EmployerID)  AS b 
					ON a.EmployerID=b.EmployerID) 
LEFT JOIN (	SELECT Count(Employees.PlanStatus) AS PaidOutEmployees, Employees.EmployerID 
			FROM Employees 
			WHERE Employees.PlanStatus='PAID OUT' 
			GROUP BY Employees.EmployerID)  AS c 
					ON a.EmployerID=c.EmployerID) 
LEFT JOIN (	SELECT Count(Employees.PlanStatus) AS ForfeitureEmployees, Employees.EmployerID 
			FROM Employees 
			WHERE Employees.PlanStatus='FORFEITURE'
			GROUP BY Employees.EmployerID)  AS d 
					ON a.EmployerID=d.EmployerID;

Open in new window

0
 
LVL 16

Expert Comment

by:ToddBeaulieu
ID: 24338121
Personally, I find this type of left outer join nesting confusing and hard to maintain. Try figuring this out a month from now when you've moved onto other things.

Of course, when writing Sql, there are many factors to consider, especially the amount of data and frequency fo the query. In other words, you should always evaluate (and re-evaluate as data and load increases) approaches to make sure they're acceptably efficient.

I'd simplify the query using subqueries, which makes it super easy to tweak them individually without harming your eyeballs.
SELECT  c.EmployerName,
        c.EmployerID, 
        ActiveEmployees = (SELECT count(*) FROM Employees WHERE EmployerID = c.EmployerID and PlanStatus != 'FORFEITURE' and PlanStatus != 'PAID OUT'),
        PaidOutEmployees = (SELECT count(*) FROM Employees WHERE EmployerID = c.EmployerID and PlanStatus == 'PAID OUT'),
        ForfeitureEmployees = (SELECT count(*) FROM Employees WHERE EmployerID = c.EmployerID and PlanStatus == 'FORFEITURE')
FROM    employers c

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dcount using a date in a table compared to today's date 3 31
sql server concatenate fields 10 36
WD MybookLive Data disappear 6 38
SQL Server 2012 r2 - Varible Table 3 27
This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Each year, investment in cloud platforms grows more than 20% (https://www.immun.io/hubfs/Immunio_2016/Content/Marketing/Cloud-Security-Report-2016.pdf?submissionGuid=a8d80a00-6fee-4b85-81db-a4e28f681762) as an increasing number of companies begin to…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question