SELECT *,
CASE
when PERSONNUM in ('x','y','z')
then 'Home'
end as HomeEmployees
when PERSONNUM in ('a','b','c')
then 'Away'
end as AwayEmployees
FROM dbo.ALLTOTALS
where PERSONNUM in ('a','b','c','x','y','z')
and HomeEmployees is not null
--multiple whens based upon select PERSONNUM fields
Why not modify your WHERE clause from
where HomeEmployees is not null
into
WHERE PERSONNUM IN ('x', 'y', 'z')
so only records with values of x , y, z in column PERSONNUM are only selected.
select 'Home' as HomeEmployees should do. No need for the CASE.You can also project the case into an alias and then filter it with an outer query, like so:
SELECT * -- Or better still, explicitly identify each needed column
FROM
(
SELECT *, -- Or better still, explicitly identify each needed column
CASE
WHEN PERSONNUM in ('x','y','z')
THEN 'Home'
END AS HomeEmployees
FROM AllTotals
) AS FilteredEmployees
WHERE
FilteredEmployees.HomeEmployees IS NOT NULL;
Or as a CTE:
WITH FilteredEmployees AS
(
SELECT *,
CASE
WHEN PERSONNUM in ('x','y','z')
THEN 'Home'
END AS HomeEmployees
FROM AllTotals
)
SELECT *
FROM FilteredEmployees
WHERE FilteredEmployees.HomeEmployees IS NOT NULL;
Logical order of execution(see section "Logical Processing Order of the SELECT statement") for the following SELECT statement:
DECLARE @MyTable TABLE
(
ID INT IDENTITY PRIMARY KEY,
[Date] SMALLDATETIME NOT NULL,
WorkingTime INT NOT NULL,
EmployeeID INT NULL
);
INSERT @MyTable ([Date], WorkingTime, EmployeeID)
SELECT '20130801', 1, 123 UNION ALL
SELECT '20130802', 0, 124 UNION ALL
SELECT '20130803', 0, 125;
SELECT x.ID,
CASE WHEN x.ID % 2 = 1 THEN x.ID END AS EmployeeID
FROM @MyTable x
WHERE EmployeeID IS NOT NULL;
is
As you can see, CASE ... END AS EmployeeID expression is evaluated AFTER WHERE clause. And this means also that EmployeeID IS NOT NULL predicate references x.EmployeeID column and not the EmployeeID alias from SELECT clause.
This is the reason I get the following results:
ID EmployeeID
----------- -----------
1 1
2 NULL
3 3
If you want to filter by EmployeeID alias then you could use one of these solutions:
SELECT x.ID,
CASE WHEN x.ID % 2 = 1 THEN x.ID END AS EmployeeID
FROM @MyTable x
WHERE CASE WHEN x.ID % 2 = 1 THEN x.ID END IS NOT NULL;
SELECT *
FROM
(
SELECT x.ID,
CASE WHEN x.ID % 2 = 1 THEN x.ID END AS EmployeeID
FROM @MyTable x
) src
WHERE src.EmployeeID IS NOT NULL;
Note: It's not a good idea to use an alias with the same name as a column name. This create confusion.
