help - sql statement - 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 2000 Forums

Transact-SQL (2000)

help - sql statement

Author

Topic

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-06-20 : 06:18:38

Hi,
I have the following table.

absences
---------
Employee Ref
absence date

I wish to try and do the following -
if the total absence per employee is more than 4 (ie 4 days absence) then set a column named LTS to the number 1, else set the column NONLTS to 1.

ie.
REF,ABSENCE DATE,LTS,NONLTS
---------------------------
EMP245, 31/1/2011, 0,1
EMP245, 1/2/2011, 0, 1
EMP245, 2/2/2011, 0,1
EMP245, 3/2/2011, 0,1
EMP246, 31/1/2011,1,0
EMP246, 31/1/2011, 1,0
EMP246, 1/2/2011, 1, 0
EMP246, 2/2/2011, 1,0
EMP246, 3/2/2011, 1,0

note, I'm not updating the absence table, I just need a query to show the results above

thanks
Craig.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-20 : 06:53:23

EMP246 shouldn't have set LTS to 1 because 31/1/2011 is a duplicate value.
edit:
Also you have not stated if you want to evaluate the data of the whole table or for a specific date range.

No, you're never too old to Yak'n'Roll if you're too young to die.

cjonline
Yak Posting Veteran

55 Posts

Posted - 2011-06-20 : 07:22:11

sorry, that was a typo .. I want to evaluate the whole table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-20 : 07:57:56

select
a.REF,
a.Absence_Date,
case when dt.cnt_absence > 4 then 1 else 0 end as LTS,
case when dt.cnt_absence <= 4 then 1 else 0 end as NONLTS
from absence a
join (select REF,count(*) as cnt_absence from absence group by REF)dt
on dt.REF = a.REF

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources