December 19, 2005 at 3:09 pm
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' end
from Employee emp
full outer join Dept dep on dep.FLDCODE = emp.FLDDEPT
left outer join REQEXAM re on re.FLDEMPLOYEE = emp.FLDREC_NUM
and 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.
December 20, 2005 at 10:32 am
/*
This may or may not execute without modifications. I did not have any data to test it, but it is an example of one way to solve your problem:
Create a totals query with one record per emp that has the DateDue information for the various Exams to Join to.
For your final view, check the various DateDue values to calculate the output.
Good Luck!
*/
SELECT ID = emp.FLDID,
[Name] = emp.FLDLNAME + ' ' + emp.FLDFNAME,
dept = dep.FLDDESCR,
PPD = case when 110DateDue Is Not Null THEN '110' else null end,
PPDDUE = (case when 110DateDue Is Not Null THEN
(case when re.110DateDue <= GETDATE() then
re.110DateDue
else null
end)
else null
end),
TBSS = case when TBSSDateDue Is Not Null THEN 'TBSS' else null end,
TBSSDUE = (case when TBSSDateDue Is Not Null THEN
(case when re.TBSSDateDue <= GETDATE() then
re.TBSSDateDue
else null
end)
else null
end),
Chest = case when 109DateDue Is Not Null THEN '109' else null end,
ChestDUE = (case when 109DateDue Is Not Null THEN
(case when re.109DateDue <= GETDATE() then
re.109DateDue
else null
end)
else null
end),
--I assume you want emp to be noncompliant if any date is <=GetDate()
Result = (case when 110DateDue<=GETDATE() then 'noncompliant'
else (case when TBSSDateDue<=GETDATE() then 'noncompliant'
else (case when 109DateDue<=GETDATE() then 'noncompliant'
else 'compliant'
end)
end)
end)
FROM Employee emp
FULL OUTER JOIN [Dept] dep
ON dep.FLDCODE = emp.FLDDEPT
LEFT OUTER JOIN --this is a totals query that combines multiple rows of data in REQEXAM to a single row per emp.
( SELECT FLDEMPLOYEE,
Max(Case when FLDPHYSICAL='110' then FLDDATEDUE else Null end) as 110DateDue,
Max(Case when FLDPHYSICAL='TBSS' then FLDDATEDUE else Null end) as TBSSDateDue,
Max(Case when FLDPHYSICAL='109' then FLDDATEDUE else Null end) as 109DateDue,
FROM REQEXAM
WHERE FLDPHYSICAL IN ( '110', 'TBSS', '109' )
GROUP BY FLDEMPLOYEE
) re
ON re.FLDEMPLOYEE = emp.FLDREC_NUM
WHERE emp.FLDstatus = 'A'
AND emp.FLDCOMP = 'UWMC'
ORDER BY dep.flddescr,emp.fldlname.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply