Joining Tables - 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

SQL Server Development (2000)

Joining Tables

Author

Topic

anishap
Yak Posting Veteran

61 Posts

Posted - 2005-12-19 : 16:36:18

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.

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-19 : 18:14:23

Without knowing the table relationships, my guess is the FULL OUTER JOIN should be INNER JOIN?

Then there's the whole multiple rows in REQEXAM thing. Hard to display that in one row, but...


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
       inner 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' )
                  and re.FLDDATEDUE < GETDATE() -- Why join if it's not overdue?
       where emp.FLDstatus = 'A'
       and emp.FLDCOMP = 'UWMC'

       order by dep.flddescr,emp.fldlname.

anishap
Yak Posting Veteran

61 Posts

Posted - 2005-12-19 : 18:30:51

I have 4 tables employee, dept,comp and required exam. I need to pull a report for all employees in all department in a particular company('UWMC') and Status ='A'. The criteria is like this: required exam table fldphyical column 'PPD' , 'TBSS' , '109' and also have to check flddatedue.
The output column should be like this:
Name,ID,dept,PPDDUE,TBSSDUE,CXRDUE,Result.Result is based on flddatedue, if the flddatedue(for PPD or TBSS orCXR) is <= todays date then the employee is non-compliant and put the due date on the (PPD,TBSS,CXR column)otherwise the employee is complaint.

Employee Table:
REC_NUM long(8) Primary Key
ID char(25) ID
LNAME char(40) Last Name
FNAME char(40) First Name
MI char(20) Middle Initial
LASTFMI char(40) Computed Last, First MI.
COMP char(12) References COMP.CODE Company code
DEPT char(12) References DEPT.CODE Department code.

Company Table
REC_NUM long(8) Primary Key
CODE char(12)
DESCR char(40)

Department Table
REC_NUM long(8) Primary Key
CODE char(12)
DESCR char(40)

Required Exam Table
RREC_NUM long(8) Primary Key Record Number
EMPLOYEE long(8) References EMPLOYEE.REC_NUM Employee
PHYSICAL char(6) References PHYSICAL.CODE Exam Type
EXPOSURE char(6) References EXPOSURE.CODE Exposure
DATEDUE date Date Due
DATELAST date Date Last Exam

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-19 : 18:31:51

Another method would dedicate a unique result column for each of the 3 possible overdue results if you want a single row per employee... actually looks the same, only different...


select [Name]   = emp.FLDLNAME + ' ' + emp.FLDFNAME, 
       ID       = emp.FLDID, 
       dept     = dep.FLDDESCR,
       PPD      = PPDx.FLDDATELAST ,
       TBSS     = TBSSx.FLDDATELAST ,
       PPDDUE   = PPDx.FLDDATEDUE ,
       TBSSDUE  = TBSSx.FLDDATEDUE ,
       ChestDUE = CDUEx.FLDDATEDUE ,

       Result = CASE WHEN PPDX.FLDDATELAST IS NULL AND TBSSx.FLDDATEDUE IS NULL
                     AND CDUEx.FLDDATEDUE IS NULL
                     THEN 'compliant'
                     ELSE 'noncompliant'
                END


       from Employee emp
       inner join Dept dep 
                  on dep.FLDCODE = emp.FLDDEPT
       left outer join REQEXAM PPDx
                  on  PPDx.FLDEMPLOYEE = emp.FLDREC_NUM
                  and PPDx.FLDPHYSICAL = '110' -- I guess 110 is PPD ?
                  and PPDx.FLDDATEDUE < GETDATE()
       left outer join REQEXAM TBSSx
                  on  TBSSx.FLDEMPLOYEE = emp.FLDREC_NUM
                  and TBSSx.FLDPHYSICAL = 'TBSS' -- Safe bet TBSS is TBSS
                  and TBSSx.FLDDATEDUE < GETDATE()
       left outer join REQEXAM CDUEx
                  on  CDUEx.FLDEMPLOYEE = emp.FLDREC_NUM
                  and CDUEx.FLDPHYSICAL = '109' -- Must be ChestDue?
                  and CDUEx.FLDDATEDUE < GETDATE()

       where emp.FLDstatus = 'A'
       and emp.FLDCOMP = 'UWMC'

       order by dep.flddescr,emp.fldlname.

anishap
Yak Posting Veteran

61 Posts

Posted - 2005-12-20 : 11:26:54

Thanks SamC it seems to be working. I have modified the query...full outer join for dept because I need all employees even without dept.

select [Name] = emp.FLDLNAME + ' ' + emp.FLDFNAME,
ID = emp.FLDID,
dept = dep.FLDDESCR,
PPD = PPDs.FLDDATELAST ,
TBSS = TBSSs.FLDDATELAST ,
PPDDUE = PPDx.FLDDATEDUE ,
TBSSDUE = TBSSx.FLDDATEDUE ,
ChestDUE = CDUEx.FLDDATEDUE ,

Result = CASE WHEN PPDx.FLDDATEDUE IS NULL AND TBSSx.FLDDATEDUE IS NULL
AND CDUEx.FLDDATEDUE IS NULL
THEN 'compliant'
ELSE 'noncompliant'
END

from Employee emp
full outer join Dept dep
on dep.FLDCODE = emp.FLDDEPT
left outer join REQEXAM PPDs
on PPDs.FLDEMPLOYEE = emp.FLDREC_NUM
and PPDs.FLDPHYSICAL = '110' -- I guess 110 is PPD ?

left outer join REQEXAM TBSSs
on TBSSs.FLDEMPLOYEE = emp.FLDREC_NUM
and TBSSs.FLDPHYSICAL = 'TBSS' -- Safe bet TBSS is TBSS

left outer join REQEXAM PPDx
on PPDx.FLDEMPLOYEE = emp.FLDREC_NUM
and PPDx.FLDPHYSICAL = '110' -- I guess 110 is PPD ?
and PPDx.FLDDATEDUE < GETDATE()
left outer join REQEXAM TBSSx
on TBSSx.FLDEMPLOYEE = emp.FLDREC_NUM
and TBSSx.FLDPHYSICAL = 'TBSS' -- Safe bet TBSS is TBSS
and TBSSx.FLDDATEDUE < GETDATE()
left outer join REQEXAM CDUEx
on CDUEx.FLDEMPLOYEE = emp.FLDREC_NUM
and CDUEx.FLDPHYSICAL = '109' -- Must be ChestDue?
and CDUEx.FLDDATEDUE < GETDATE()

where emp.FLDstatus = 'A'
and emp.FLDCOMP = 'UWMC'

order by dep.flddescr,emp.fldlname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources