Solved

Filtering on a column name created with CASE statement

Posted on 2008-06-23
6
331 Views
Last Modified: 2010-04-21
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

Open in new window

0
Comment
Question by:BobRosas
  • 3
  • 3
6 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21851522
You just have to wrap another SELECT around it:

SELECT * FROM
(
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
) a

WHERE [Descrepancy] = 0
ORDER BY [Descrepancy] ASC
0
 

Author Comment

by:BobRosas
ID: 21858298
THANK YOU!!!  That worked perfectly - just so I understand - why did I need the extra SELECT statement?

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21858318
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:BobRosas
ID: 21858359
Your awesome!!!!!  Thank you very much.  I have been frustrated by this for days!!  
Thanks!!
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21858370
You're very welcome.
0
 

Author Closing Comment

by:BobRosas
ID: 31470004
I thought I already awarded points.  Thanks again.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now