BobRosas
asked on
Filtering on a column name created with CASE statement
I am using a case statement. I assign the values to column defined in the case statement. I want to filter on the values in that column. I cannot seem to get the correct syntax to do this with the WHERE statement. Or maybe I am just way off on how I approaching this problem.
-- Find Employees who's PTO max accural needs to adjusted.
DECLARE @MAX_PTO nvarchar (5)
SET @MAX_PTO = 'False'
SELECT
AccrualRules.RuleId,
Description,
EmployeeAccrualRules.EmployeeId,
FirstName,
LastName,
AccrualCapHours,
CONVERT(varchar, DateHire, 101) AS 'Hire Date',
CONVERT(varchar, GetDate(), 101) As 'Current Date',
DATEDIFF(m,DateHire, GetDate()) AS 'Number of Months',
Class,
Suspend,
DateLeft,
'Descrepancy' =
CASE
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 10 AND 12 AND AccrualCapHours < 120 AND AccrualRules.RuleId = 1 THEN 0 -- Hourly & Admin
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 13 AND 48 AND AccrualCapHours < 176 AND AccrualRules.RuleId = 2 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 49 AND 108 AND AccrualCapHours < 216 AND AccrualRules.RuleId = 3 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 7 AND 12 AND AccrualCapHours < 136 AND AccrualRules.RuleId = 5 THEN 0 -- Management
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 13 AND 48 AND AccrualCapHours < 192 AND AccrualRules.RuleId = 6 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 49 AND 108 AND AccrualCapHours < 224 AND AccrualRules.RuleId = 7 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 109 AND 168 AND AccrualCapHours < 248 AND AccrualRules.RuleId = 8 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 7 AND 12 AND AccrualCapHours < 136 AND AccrualRules.RuleId = 10 THEN 0 -- Executive
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 13 AND 48 AND AccrualCapHours < 192 AND AccrualRules.RuleId = 11 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 49 AND 84 AND AccrualCapHours < 224 AND AccrualRules.RuleId = 12 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 85 AND 120 AND AccrualCapHours < 248 AND AccrualRules.RuleId = 13 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 121 AND 156 AND AccrualCapHours < 256 AND AccrualRules.RuleId = 14 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 127 AND 192 AND AccrualCapHours < 264 AND AccrualRules.RuleId = 15 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 10 AND 12 AND AccrualCapHours < 120 AND AccrualRules.RuleId = 17 THEN 0 -- Hourly & Admin (Non Clocking)
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 13 AND 48 AND AccrualCapHours < 176 AND AccrualRules.RuleId = 18 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 49 AND 108 AND AccrualCapHours < 216 AND AccrualRules.RuleId = 19 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 7 AND 12 AND AccrualCapHours < 136 AND AccrualRules.RuleId = 21 THEN 0 -- Management (Non Clocking)
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 13 AND 48 AND AccrualCapHours < 192 AND AccrualRules.RuleId = 22 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 49 AND 108 AND AccrualCapHours < 224 AND AccrualRules.RuleId = 23 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 109 AND 168 AND AccrualCapHours < 248 AND AccrualRules.RuleId = 24 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 7 AND 12 AND AccrualCapHours < 136 AND AccrualRules.RuleId = 26 THEN 0 -- Executive (Non Clocking)
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 13 AND 48 AND AccrualCapHours < 192 AND AccrualRules.RuleId = 27 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 49 AND 84 AND AccrualCapHours < 224 AND AccrualRules.RuleId = 28 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 85 AND 120 AND AccrualCapHours < 248 AND AccrualRules.RuleId = 29 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 121 AND 156 AND AccrualCapHours < 256 AND AccrualRules.RuleId = 30 THEN 0
WHEN DATEDIFF(m,DateHire, GetDate())BETWEEN 127 AND 192 AND AccrualCapHours < 264 AND AccrualRules.RuleId = 31 THEN 0
END
FROM dbo.AccrualRules
INNER JOIN dbo.EmployeeAccrualRules ON dbo.AccrualRules.RuleId = dbo.EmployeeAccrualRules.RuleId
INNER JOIN dbo.EmployeeList ON dbo.EmployeeAccrualRules.EmployeeId = dbo.EmployeeList.EmployeeId
INNER JOIN dbo.EmployeeJobCodes ON dbo.EmployeeList.EmployeeId = dbo.EmployeeJobCodes.EmployeeId
--WHERE EmployeeJobCodes.JobCode = 5 AND Suspend = 0 AND DateLeft IS NULL AND(Class = 1 OR Class = 2 OR Class = 6)
WHERE [Descrepancy] = 0
ORDER BY [Descrepancy] ASC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I used your first statement as a "virtual table" so to speak. So, that query runs and returns results that are used by the outer query. In the outer query, since the value from the case statement has already been returned, I can set criteria on it and filter it as needed. I could have done it in the original SELECT statement, but I would have had to recreate the entire case statement again in the where clause to do so...so this is more eloquent..and should be faster too.
ASKER
Your awesome!!!!! Thank you very much. I have been frustrated by this for days!!
Thanks!!
Thanks!!
You're very welcome.
ASKER
I thought I already awarded points. Thanks again.
ASKER