Need SQL Help

UDA
Enthusiast

Need SQL Help

Hi Can any one suggest sql/database procedure to implement the logic below.

Two records have overlapping time periods if one of the following conditions is true:

Logically Incorrect: A record whose end date is less than start date.
Logically correct: A record whose end date is greater than start date.

a.Neither record is a Logically Incorrect, and the start date of either record is between the start date and end date of the other record. When this condition is true, keep the earliest start date and the latest end date.
b.Both records being compared are Logically Incorrect, and the start dates are equal. When this condition is true, keep the record with the maximum end date. If multiple records still exist, keep either record, as the dates are the same.
c.Only one record is a Logically Incorrect, and the start date of the Logically Incorrect record is between the start and end dates of the Logically Correct record. When this condition is true, keep the Logically Correct record and drop the Logically Incorrect record.
2 REPLIES
Enthusiast

Re: Need SQL Help

You could try using the RANK function along with a Qualify. It would look something like this:

Select RANK(End_Date ASC) as RANKED, Start_Date
From Table
Group by Start_Date
Qualify RANKED < 2;

Be sure to do an ascending Rank; it will give the earliest End_Date for each Start_Date a ranking of 1. The qualify will then limit the results by only bringing back all rows with a rank of 1;

Enthusiast

Re: Need SQL Help

In all cases, you want the earliest start date and latest end date.

Case A
---------
There are 2 possibilities:

S1 ---------------- E1
S2----------------E2

You would want S1 and E2
<----------------------------------->

S1 ---------------- E1
S2------E2
You want S1 and E1
<----------------------------------->

S1 ---------------- E1
S2----------- E2
You want S1 and E1 or E2 (both are the same
<----------------------------------->

Case B
------
Again, 3 options:

E1----------------S1
E2------------S2

You want S1 or S2 and E2
<----------------------------------->

E1------------S1
E2----------------S2

You want S1 or S2 and E1
<----------------------------------->

E1----------------S1
E2----------------S2

You want S1 or S2 and E1 or E2
<----------------------------------->

Case C
------
Again, 3 options:

S1----------------E1
E2-----------S2

You want S1 and E1
<----------------------------------->

S1----------------E1
E2-------S2

You want S1 and E1
<----------------------------------->

S1----------------E1
E2---S2

You want S1 and E1
<----------------------------------->

You can get this with a case statement.
You would need to join the records, then do the following:

select ...
, case when S1 <= S1 then S1 else S2 end as Start_date
, case when E1 >= E2 then E1 else E2 end as End_date
from T1 join T1 on

Once you have the records, you can use them as you please.

James