I need to easily identify rows where the start date overlaps an end date for a member. I don't want to delete or suppress them from my final report, I need to make a smaller sub report so I can look into the reasons why the dates overlap.
The way the output looks:
MemberName Field Member ID (Sub ID) Field Member Date of Birth Field Case# Admit Date Discharge Date
BANKS, C 12345 8/8/2012 7124437908 8/9/2012 8/22/2012
BANKS, C 12345 8/8/2012 7124455224 8/17/2012 8/22/2012
BAR, L 67891 6/27/1952 7124310690 6/5/2012 7/1/2012
BAR, L 67891 6/27/1952 7124383381 7/11/2012 8/5/2012
(I don't know how you get posts to line up all purdy - this was my best attempt!)
I would like a query that identifies and extracts the rows for BANKS,C only as the Admit Date for 7124455224 falls between the Admit Date and Discharge Date for 7124437908. (note that the Discharge Dates may not always be the same!)
from myTable as x
where exists (select 1 from myTable as z
where z.Member_id = x.Member_id and
z.case_number <> x.case_number and
z.admit_date <= x.discharge_date and
z.discharge_date >= x.admit_date
Note: this works if the Admit_Date is always <= discharge_date.