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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-12-20 : 08:18:30
|
| Anish writes "Can some one help me with this MS SQL query. The purpose of the query is to find out which employee is compliant or non-compliant. For example if an employee is due for PPD or TBSS or Chest and due date is <= GETDATE()then the employee is non-compliant otherwise compliant.**************************************select [Name] = emp.FLDLNAME + ' ' + emp.FLDFNAME,ID = emp.FLDID,dept = dep.FLDDESCR,PPD = case when FLDPHYSICAL ='110' THEN re.FLDDATELAST else null end,TBSS = case when FLDPHYSICAL = 'TBSS' THEN re.FLDDATELAST else null end,PPDDUE = case when FLDPHYSICAL = '110' THEN case when re.FLDDATEDUE <= GETDATE() then re.FLDDATEDUE else null end else null end,TBSSDUE = case when FLDPHYSICAL = 'TBSS' THEN case when re.FLDDATEDUE <= GETDATE() then re.FLDDATEDUE else null end else null end,ChestDUE = case when FLDPHYSICAL = '109' THEN case when re.FLDDATEDUE <= GETDATE() then re.FLDDATEDUE else null end else null end,Result = case when re.FLDDATEDUE <= GETDATE() then 'noncompliant' else 'compliant' endfrom Employee empfull outer join Dept dep on dep.FLDCODE = emp.FLDDEPTleft outer join REQEXAM re on re.FLDEMPLOYEE = emp.FLDREC_NUMand re.FLDPHYSICAL IN ( '110', 'TBSS', '109' )where emp.FLDstatus = 'A'and emp.FLDCOMP = 'UWMC'order by dep.flddescr,emp.fldlname.***************************************Its working but if an employee has PPD due or TBSS due or Chest due its showing in different rows. I want it in one single row." |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-12-20 : 08:32:34
|
| Cross post http://sqlteam.com/Forums/topic.asp?TOPIC_ID=59350 |
 |
|
|
|
|
|
Joining Tables