CODEWITHSUNDEEP
sqlnetezza
user3642531
user3642531

Reputation: 319

Overlapping dates in SQL

I have a table listing all of the times that a patient has received any type of medication that looks something like this:

Pat ID  Type of Admin    Order ID     Start Date          End Date
1       Medication       1            01/01/2016 04:20    01/04/2016 05:30
1       Medication       2            01/03/2016 18:30    01/07/2016 10:15

I would like to know the exact overlapping date ranges. So a resultant table would look like this:

Pat ID      First_Overlap      Last_Overlap
1           01/03/2016 18:30   01/04/2016 05:30

I am using Netezza. Does anyone know how to do this?

Upvotes: 2

Views: 897

Answers (2)

Steve Moore
Steve Moore

Reputation: 168

I am not familiar with Netezza however if I was to do this in MySQL or another SQL variant I would do something like this:

SELECT C.PatID AS PatID, MAX(C.FirstOverlap) AS FirstOverlap, MIN(C.LastOverlap) As LastOverlap
FROM (
  SELECT A.PatID AS PatID, CASE WHEN B.StartDate <= A.EndDate THEN B.StartDate ELSE A.EndDate END AS FirstOverlap,
  CASE WHEN B.EndDate >= A.StartDate THEN B.EndDate ELSE A.StartDate END AS LastOverlap
  FROM careAdmin AS A LEFT JOIN careAdmin AS B ON A.PatID = B.PatID AND A.OrderID = B.OrderID
  WHERE NOT B.EndDate <= A.StartDate OR B.StartDate >= A.EndDate
  ) C
GROUP BY C.PatID

Even though the syntax may differ slightly hopefully the process will help you get to the right answer.

Regards

Upvotes: 1

Niederee
Niederee

Reputation: 4295

Try this:

create temp table overlapTemp (
pat_id Int,
type_of_admin varchar(100),
order_id int,
start_dt timestamp,
end_dt timestamp
)
distribute on random;

insert into overlapTemp values(1,'Medication',1,'01/01/2016 04:20','01/04/2016 05:30');
insert into overlapTemp values(1,'Medication',2,'01/03/2016 18:30','01/07/2016 10:15');



select a.pat_id
,case when (b.start_dt,b.end_dt) overlaps (a.start_dt,a.end_dt) then b.start_dt end as overlap_start
,case when (b.start_dt,b.end_dt) overlaps (a.start_dt,a.end_dt) then a.end_dt end as overlap_end
from overlapTemp a inner join
overlapTemp b
on a.pat_id = b.pat_id
and a.order_id<b.order_id

Upvotes: 1

Related Questions