Help me with SQL - 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

General SQL Server Forums

New to SQL Server Programming

Help me with SQL

Author

Topic

Rupas2000
Starting Member

1 Post

Posted - 2014-10-18 : 11:29:37

I have three tables - TableA, TableB & TableC. TableC is the merger of TableA & TableB.

TableA
SID Fname   Lname   Job               Grade EndDate
101 Rosy       Maria     Doctor           A3      06/10/2009
102 Shane     Watson Manager       A1      21/02/2010
103 George    Michael Lawyer          A5      11/04/2015
104 Stella       Kim       Teacher         A2     30/09/2007
105 Amanda   Lee       Accountant   A4     15/08/2012

TableB
SID Dept      Grade EndDate
102 Finance A4      19/05/2012
103 Legal     A1       28/02/2008

TableC
SID Fname    Lname    Job             Dept       Grade EndDate
101 Rosy       Maria      Doctor        NULL      A3       06/10/2009
102 Shane     Watson   Manager    Finance A4       19/05/2012
103 George    Michael Lawyer       Legal      A5       11/04/2015
104 Stella       Kim        Teacher     NULL      A2        30/09/2007
105 Amanda   Lee       Accountant NULL      A4      15/08/2012

Rules: 1. TableA.SID = TableB.SID
2. TableC.EndDate = Earliest EndDate date from joined records from TableA & TableB
3. TableC.Grade = Grade from the Earliest EndDate date from joined records from TableA & TableB

I have written sql many ways on TableA & TableB to get the result of TableC but couldn't get the correct result. So can you please help me with sql joining table A and table B using the key SID using the above rules.

Thanks

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-18 : 15:39:45

Try this:

select a.SID
      ,a.Fname
      ,a.Lname
      ,a.Job
      ,b.Dept
      ,case
          when a.EndDate<b.EndDate or b.EndDate is null then a.Grade
          else b.Grade
       end as Grade
      ,case
          when a.EndDate<b.EndDate or b.EndDate is null then a.EndDate
          else b.EndDate
       end as EndDate
  from TableA as a
       left outer join TableB as b
                    on b.SID=a.SID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources