Case Statement Help

[tkaz]

Using SQL Server 2008

Below is the select statement I have working to determine Years of Service from an Employee table. Now, I've been asked to determine the leave time based on YOS and criteria like 1 - 4 years PTO = 80 hours; 5 - 9 = 120 hours and 10+ = 160 hours. I THINK that the first step is to write a CASE statement within the select to get the PTO for each employee based on their Years of Service, and then I need to include a column for "GRANDFATHERED" employees who already have 200 hours of PTO based on an old program.

I am a mediocre SQL coder...years of experience with Crystal Reports, but not writing queries that will return data I could write formulas for in CR. I would appreicate any help you could give. I will try to offer better examples if needed. Thanks for advance for any help you can give me.


SELECT Employee
,LastName
,FirstName
,MidName
,Race
,Sex
,CONVERT(VARCHAR(10),HireDate ,101) as HireDate
,CONVERT(VARCHAR(10),udRehireDate ,101) as RehireDate
,CONVERT(CHAR(10), ISNULL(udRehireDate,HireDate), 101) AS "Hire or Rehire Date"
,CONVERT(Decimal(5,2),(DATEDIFF(Day,ISNULL(udRehireDate,HireDate),CAST('12/31/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME))/365.25)) as YOS
,HrlyRate
,PREC.Description
from PREH
Inner Join PREC on
PREH.PRCo = PREC.PRCo
and PREH.EarnCode = PREC.EarnCode
where PREH.PRCo = '216'
and PREH.GLCo = '216'
and PREH.JCCo = '216'
and PREH.ActiveYN = 'Y'
order by Employee

 

[gmmastros]

Try this:

; With Data As
(
	SELECT Employee
	,LastName
	,FirstName
	,MidName
	,Race
	,Sex
	,CONVERT(VARCHAR(10),HireDate ,101) as HireDate
	,CONVERT(VARCHAR(10),udRehireDate ,101) as RehireDate 
	,CONVERT(CHAR(10), ISNULL(udRehireDate,HireDate), 101) AS "Hire or Rehire Date"
	,CONVERT(Decimal(5,2),(DATEDIFF(Day,ISNULL(udRehireDate,HireDate),CAST('12/31/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME))/365.25)) as YOS
	,HrlyRate
	,PREC.Description
	from PREH
	Inner Join PREC on 
	PREH.PRCo = PREC.PRCo
	and PREH.EarnCode = PREC.EarnCode
	where PREH.PRCo = '216'
	and PREH.GLCo = '216'
	and PREH.JCCo = '216'
	and PREH.ActiveYN = 'Y'
)
Select	*,
        Case When YOS Between 1 and 4 Then 80
             When YOS Between 5 and 9 then 120
             When YOS >= 10 Then 160
             End As PaidTimeOff
From    Data
order by Employee

Notice that I wrapped your original query in a pair of parenthesis...

; With Data As
(
  -- Original Query Here
)
Select ....

This is called a common table expression. It is technically not necessary for this query, but it certainly makes things a lot easier. Notice that the case statement near the bottom uses the results of the YOS column. Since the YOS column is calculated, you can ONLY use the results of the calculation (the YOS column) in an order by clause. In this case, we need to use it in the select clause, so we cannot reference the YOS column. We could repeat the calculation multiple times in the case statement so that we are not referencing the column name (YOS), but this gets very messy.

By using a common table expression, we are allowed to use the YOS column outside of the common table expression.

I'm afraid I cannot help you the the GRANDFATHERED part because you haven't given enough information.

Last point (and it's a minor one)... When you want to use spaces in column name, you should wrap the column name in square brackets instead of quotes.

Change: AS "Hire or Rehire Date"
To: AS [Hire or Rehire Date]

This is not a big deal, but SQL Server prefers it this way, so it's a good habit to get in to.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tkaz]

I was going about that all the wrong way! I was trying to do the CASE statement in the select. I've got much to learn in order to do this they way they are asking for things now. I probably couldn't even be qualified as a mediocre SQL coder based on this! It did work and I do appreciate it.

The Grandfathered part is going to have to based on a list of employee id's that I provide and populate the PaidTimeOff with 200 hours instead of the max 160 that the new policy provides. Many of the employees here have been here for 20+ years and were under an old policy that stated after 10+ years of service, they would receive 200 hours of vacation. Last year, this policy was changed to make the max number of PTO hours 160. Those who had been here with 10+ years at that time were guaranteed they could still get their 200. Does that make sense?

For example: Employee 1100, Andrews, Abigale has 19.15 YOS at the end of this year. Under the new plan, she would receive 160 hours of PTO but because she was "Grandfathered" in from the old plan, her PTO should be 200 hours.

Thank you so much for your quick and easy to understand reply on the other stuff!

 

[djj55]

What you may want to do is add a case comparing a variable

Case When YOS Between 1 and 4 Then 80
             When YOS Between 5 and 9 then 120
             When YOS > DATEDIFF(GETDATE(), 'startdateofgrandfather') + 10 THEN 200
             When YOS >= 10 Then 160
             End As PaidTimeOff

I hope this is clear. It may not be how to do it, but it is how I would start.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Let me be clear about the case statement. There are a variety of places that you can use a case statement. You see them most often in the select clause, but I've also used them in a WHERE clause, and even in a JOIN clause.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tkaz]

djj55 - Thanks for the tip...I will give that a whirl, although I think it's going to be more complicated after receiving more requirements.

gmmastros - understood. I am going to try to take an on-line class or something to bone up on writing more advanced SQL queries. I'm a bit intimidated, but I also was when I first started working with Crystal and Business Objects years and years ago. I've had a DBA to work with most of my career who would help when I got stuck on something. He's gone with the latest round of cuts and I'm it

 

[gmmastros]

Ah... you're an [google]Accidental DBA[/google]. There are many resources on the internet that can help too.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tkaz]

LOL!! That's a good way to put it! I am very uncomfortable with the new requirements because they want stuff so fast I don't have time to think through it. I appreciate you getting me on the right track with this particular project. I wanted to include the finished code I have for right now. The Grandfathered piece of this is still up in the air because there appears to be NO LOGIC whatsoever in who they have decided to include in this group. I'm waiting for clarification on that from the end user. Thanks again...I'm pretty sure you will hear from me again as the requests pile up on me!

; With Data As
(
SELECT PREH.Employee
,PREH.LastName
,PREH.FirstName
,PREH.PRGroup
,PREH.PRDept
,PREH.EarnCode
,PREC.Description
--,CONVERT(VARCHAR(10),HireDate ,101) as HireDate
--,CONVERT(VARCHAR(10),udRehireDate ,101) as RehireDate
,CONVERT(CHAR(10), ISNULL(udRehireDate,HireDate), 101) AS [Hire or Rehire Date]
,CONVERT(Decimal(5,2),(DATEDIFF(Day,ISNULL(udRehireDate,HireDate),CAST('12/31/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME))/365.25)) as YOS
from PREH
Inner Join PREC on
PREH.PRCo = PREC.PRCo
and PREH.EarnCode = PREC.EarnCode
Inner Join PRGR
on PREH.PRCo = PRGR.PRCo
and PREH.PRGroup = PRGR.PRGroup
where PREH.PRCo = '216'
and PREH.GLCo = '216'
and PREH.JCCo = '216'
and PREH.ActiveYN = 'Y'
)
Select *,
Case When EarnCode in (100, 142) and (YOS Between 1.00 and 4.99) Then 80
When EarnCode in (140,141) and (YOS Between 1.00 and 2.99) Then 80
When EarnCode in (100, 142) and (YOS Between 5.00 and 9.99) Then 120
When EarnCode in (140,141) and (YOS Between 3.00 and 5.99) Then 120
When EarnCode in (100, 142) and (YOS >= 10.00) Then 160
When EarnCode in (140,141) and (YOS Between 6.00 and 9.99) Then 160
When EarnCode in (140,141) and (YOS >=10.00) Then 200
WHEN EarnCode in (100, 142) and [Hire or Rehire Date]>= CAST('01/01/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and [Hire or Rehire Date] <=CAST('03/31/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and YOS < 1.00 then 40
WHEN EarnCode in (100, 142) and [Hire or Rehire Date]>= CAST('04/01/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and [Hire or Rehire Date] <=CAST('06/30/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and YOS < 1.00 then 24
WHEN EarnCode in (100, 142) and [Hire or Rehire Date]>= CAST('07/01/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and [Hire or Rehire Date] <=CAST('09/30/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and YOS < 1.00 then 16
WHEN EarnCode in (140,141) and [Hire or Rehire Date]>= CAST('01/01/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and [Hire or Rehire Date] <=CAST('03/31/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and YOS < 1.00 then 80
WHEN EarnCode in (140,141) and [Hire or Rehire Date]>= CAST('04/01/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and [Hire or Rehire Date] <=CAST('06/30/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and YOS < 1.00 then 56
WHEN EarnCode in (140,141) and [Hire or Rehire Date]>= CAST('07/01/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and [Hire or Rehire Date] <=CAST('09/30/' + CAST(YEAR(GETDATE()) AS CHAR(5)) AS DATETIME) and YOS < 1.00 then 32
End As PaidTimeOff
From Data
order by Employee