Help on SQL Expression

pts.
Tags:
Development
SQL
SQL Server
Hi, I'm new to the SQL environment and writing expressions and need some assistance or recommendations. Any help or assistance would be greatly appreciated! We have SQL 2000 HRIS system. We are implementing employees PTO plans. Rules: Hired in the month of 7,8 or 9, PTO earned is 120 hours Hired in the month of 10, 11 or 12, PTO earned is 90 hours Hired in the month of 1,2 or 3, PTO earned is 60 hours Hired in the month of 4 or 5, PTO earned is 16 hours Hired in the month of 6, PTO earned is 0 hours If date of seniority is greater or equal to 1 year or less than 5 years earned is 120 If date of seniority is greater or equal to 10 years eared is 160 The expression compares the employees? month of their date of seniority and the current year to determine this. Question 1: Can I write an expression that would include the months that receive the same earned factor like this? I know the below expression works if I write an IIF statement for each month. IIf ( Month(DateofSeniority) = 07 or 08 or 09 and Year(today())= Year(DateofSeniority), 120,160 Question 2: I?m not sure how to write the expression when the employee has more than 1 year of service or 10 years. I wrote this, will this work? I would like it if I didn?t have to use the hard coded years for the expression. I would rather write the expression to know the current year and add 5 years or 10 years to determine the PTO earned. We currently don?t have a test environment or development tools to test this. I have to write the expression, wait until the next pay run which is semi-annual to see the results. IIF(YEAR( DateOfSeniority )>2002 and YEAR( DateOfSeniority )< =1997,160,200 ) Here is the entire expression: IIf ( Month(DateofSeniority) = 07 or 08 or 09 and Year(today())= Year(DateofSeniority), 120,IIF(Month(DateofSeniority) = 08 and Year(today()) = Year(DateofSeniority), 120, IIF(Month(DateofSeniority) = 09 and Year(today()) = Year(DateofSeniority), 120, IIF(Month(DateofSeniority) = 10 and Year(today()) = Year(DateofSeniority), 60, IIF(Month(DateofSeniority) = 11 and Year(today()) = Year(DateofSeniority), 60, IIF(Month(DateofSeniority) = 12 and Year(today()) = Year(DateofSeniority), 60, IIF(Month(DateofSeniority) = 01 and Year(today()) = Year(DateofSeniority), 40, IIF(Month(DateofSeniority) = 02 and Year(today()) = Year(DateofSeniority), 40, IIF(Month(DateofSeniority) = 03 and Year(today()) = Year(DateofSeniority), 40, IIF(Month(DateofSeniority) = 04 and Year(today()) = Year(DateofSeniority), 0, IIF(Month(DateofSeniority) = 05 and Year(today()) = Year(DateofSeniority), 0, IIF(Month(DateofSeniority) = 06 and Year(today()) = Year(DateofSeniority), 0, IIF(YEAR( DateOfSeniority )>2002 and YEAR( DateOfSeniority )< =1997,160,200 ))) ) ) ))))))))

Answer Wiki

Thanks. We'll let you know when a new response is added.

There are easier ways to do this. The following example uses DB2 date comparisons. SQL Server will likely use something very similar

CASE
WHEN seniority + 10 YEARS <= CURRENT DATE THEN 160
— 5 to 10 years falls through the cracks here!
WHEN seniority + 1 YEAR <= CURRENT DATE THEN 120
WHEN month IN(7,8,9) THEN 120
WHEN month IN(10,11,12) THEN 90
WHEN month IN(1,2,3) THEN 60
WHEN month IN(4,5) THEN 16
ELSE 0
END

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following