join with date ranges? 1

[cristianivanoff]

Hello all!
I have a problem joining two tables with date ranges. Maybe anybody here can give a suggestion how to solve this problem.
I have two tables,Tab1 and Tab2, and I would like to join them and get the resulting table Result.

Any suggestion is appreciated!

Br
cristian

Tab1:
SubsID,ObjectID,StatusID,BValidFrom,BValidTo
220,9898,1,20080801,20080819
220,9898,2,20080820,20080902

Tab2:
SubsID,KamID,CValidFrom,CValidTo
220,1020,20080701,20080823
220,1030,20080824,20081001

Result:
SubsID,ObjectID,KamID,StatusID,ValidFrom,ValidTo
220,9898,1020,1,20080801,20080819
220,9898,1020,2,20080820,20080823
220,9898,1030,2,20080824,20080902

 

[r937]

SELECT Tab1.SubsID
     , Tab1.ObjectID
     , Tab2.KamID
     , Tab1.StatusID
     , CASE WHEN Tab1.BValidFrom > Tab2.CValidFrom
            THEN Tab1.BValidFrom
            ELSE Tab2.CValidFrom END AS ValidFrom
     , CASE WHEN Tab1.BValidTo < Tab2.CValidTo
            THEN Tab1.BValidTo
            ELSE Tab2.CValidTo END AS ValidTo
  FROM Tab1
INNER
  JOIN Tab2
    ON Tab2.SubsID = Tab1.SubsID
   AND Tab2.CValidTo >= Tab1.BValidFrom  
   AND Tab2.CValidFrom <= Tab1.BValidTo

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[cristianivanoff]

GREAT! You are my hero!!!!

Br
Cristian