calculation with maximum date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

calculation with maximum date

Author

Topic

iand109
Starting Member

14 Posts

Posted - 2009-11-12 : 09:34:26

Hi,
I have a View on my SQL 2005 DB. I want to create a column that calculates something and returns a value, but has a maximum value of 4.
The calculation is this: Calculate how long a member of staff has been workiing for the company (based on start date), and then for each year they've worked, add a point to their record, up to a maximum of 4 points.
I can do the first part (i.e. how long they've been working for the company), and could add a point for each year, but don't know how to specify a a maximum of 4.
The SQL I have to calculate years served is this:

(SELECT SUM(DATEDIFF(yy, EmpStart, (CASE WHEN DATEDIFF(day, getDate(), EmpEnd) > 0 THEN getDate() WHEN EmpEnd IS NULL
THEN getDate() ELSE EmpEnd END))) AS Expr1
FROM dbo.Employment AS Employment_3
WHERE (dbo.STAFF.AppNo = AppNo)
GROUP BY AppNo) AS YearsServed

I'm guessing it's using CASE, WHEN & THEN but am not sure how to express it.
Please can anyone help?
thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-11-13 : 15:59:20

case WHEN YourValue <= 4 then YourValue else 4 end

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

iand109
Starting Member

14 Posts

Posted - 2009-11-16 : 04:37:29

Thanks, but where in the statement would I put that in? I've tried putting it in various ways but not sure how to specify exactly what value is <= 4. Wherever I try to put it in comes up with a syntax error near the word 'case'.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-16 : 05:26:34

Must be like this:

(SELECT 
 CASE
  WHEN
   SUM(
       DATEDIFF(yy, EmpStart, 
                (CASE 
                  WHEN DATEDIFF(day, getDate(), EmpEnd) > 0 THEN getDate() 
                  WHEN EmpEnd IS NULL THEN getDate() 
                  ELSE EmpEnd 
                 END)
      ) <= 4
  THEN
   SUM(
       DATEDIFF(yy, EmpStart, 
                (CASE 
                  WHEN DATEDIFF(day, getDate(), EmpEnd) > 0 THEN getDate() 
                  WHEN EmpEnd IS NULL THEN getDate() 
                  ELSE EmpEnd 
                 END)
      )
  ELSE 4
 END
) AS Expr1