March 19, 2015 at 12:22 pm
Good Day,
I am trying to use a date comparison in a statement using the year statement as well. Here is what I have:
Case [LastHireDate]
When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12'
When Month([LastHireDate]) = '1' then '12'
When Month([LastHireDate]) = '2' then '11'
When Month([LastHireDate]) = '3' then '10'
When Month([LastHireDate]) = '4' then '9'
When Month([LastHireDate]) = '5' then '8'
When Month([LastHireDate]) = '6' then '7'
When Month([LastHireDate]) = '7' then '6'
When Month([LastHireDate]) = '8' then '5'
When Month([LastHireDate]) = '9' then '4'
When Month([LastHireDate]) = '10' then '3'
When Month([LastHireDate]) = '11' then '2'
When Month([LastHireDate]) = '12' then '1'
End As LastHireDate,
When I am looking at it [LastHireDate] is showing that red line underneath. The < symbol has a red line and @EndYearlyDate has a red line. I can not seem to get them to clear and am, wondering what I am missing. When I execute the error comes up that it does not like the < sign in there. Any Help is appreciated.
Here is the full piece that the Case resides in:
Insert _Test
SELECT
EmpNo,
PersonIdNo,
REPLACE(PersonTaxIdNo,'-',''),
LastName,
FirstName,
Case [EmploymentStatus]
When 'RFT' then 'Yes'
When 'RPT' then 'Yes'
When 'PD' then 'No'
When 'TEM' then 'No'
End As EmploymentStatus,
BirthDate,
SeniorityDate,
0,
'No',
0,
Case [LastHireDate]
When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12'
When Month([LastHireDate]) = '1' then '12'
When Month([LastHireDate]) = '2' then '11'
When Month([LastHireDate]) = '3' then '10'
When Month([LastHireDate]) = '4' then '9'
When Month([LastHireDate]) = '5' then '8'
When Month([LastHireDate]) = '6' then '7'
When Month([LastHireDate]) = '7' then '6'
When Month([LastHireDate]) = '8' then '5'
When Month([LastHireDate]) = '9' then '4'
When Month([LastHireDate]) = '10' then '3'
When Month([LastHireDate]) = '11' then '2'
When Month([LastHireDate]) = '12' then '1'
End As LastHireDate,
0
FROM EmployeePay_Job_Curr
Where EmploymentStatus Not in ('VOL', 'CON') and
EmployeeStatus Not in ('Not Employee') and
(TerminationDate >= @StartYearlyDate or TerminationDate is Null) and
SeniorityDate <= @PPStart and
EmploymentStatusOrgCode = 'ABC Corp'
Thank you.
March 19, 2015 at 12:53 pm
You can't have CASE [LastHireDate] WHEN boolean_expression THEN X
Only: CASE [LastHireDate] WHEN value_expression THEN x
Change your case to just:
CASE
When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12'
When Month([LastHireDate]) = '1' then '12'
When Month([LastHireDate]) = '2' then '11'
When Month([LastHireDate]) = '3' then '10'
When Month([LastHireDate]) = '4' then '9'
When Month([LastHireDate]) = '5' then '8'
When Month([LastHireDate]) = '6' then '7'
When Month([LastHireDate]) = '7' then '6'
When Month([LastHireDate]) = '8' then '5'
When Month([LastHireDate]) = '9' then '4'
When Month([LastHireDate]) = '10' then '3'
When Month([LastHireDate]) = '11' then '2'
When Month([LastHireDate]) = '12' then '1'
End As LastHireDate,
March 19, 2015 at 12:56 pm
Ok that was way to easy. Thank you for the help. I was missing that. Thank you.
March 19, 2015 at 6:05 pm
May I ask, why all the WHENs?
CASE
When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12'
ELSE 13- Month([LastHireDate])
End As LastHireDate,
Doesn't that get you the same thing?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply