By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].homecare(
Location [int] NOT NULL,
Customer INT NOT NULL,
Date DATE NOT NULL,
recordtype Varchar(50) NOT NULL
)
INSERT INTO homecare VALUES (100, 45454, '3/20/2019','Admit');
INSERT INTO homecare VALUES (100, 45454, '3/21/2019','InfoUpdate');
INSERT INTO homecare VALUES (100, 45454, '3/22/2019','Therapy');
INSERT INTO homecare VALUES (100, 45454, '3/29/2019','Return');
INSERT INTO homecare VALUES (100, 45454, '3/30/2019','Therapy');
INSERT INTO homecare VALUES (100, 45454, '4/1/2019','Return');
INSERT INTO homecare VALUES (100, 45454, '4/5/2019','Expired');
INSERT INTO homecare VALUES (101, 34567, '3/27/2019','Admit');
INSERT INTO homecare VALUES (100, 56787, '4/5/2019','Admit');
INSERT INTO homecare VALUES (100, 56787, '4/9/2019','Expired');
INSERT INTO homecare VALUES (102, 76567, '3/30/2019','Admit');
INSERT INTO homecare VALUES (102, 76567, '3/31/2019','Infochange');
INSERT INTO homecare VALUES (102, 76567, '4/1/2019','Discharge');
INSERT INTO homecare VALUES (102, 76567, '5/2/2019','Admit');
INSERT INTO homecare VALUES (102, 76567, '5/12/2019','Discharge-Returning');
INSERT INTO homecare VALUES (102, 76567, '5/17/2019','Return');
INSERT INTO homecare VALUES (102, 76567, '5/30/2019','Discharge-Returning');
INSERT INTO homecare VALUES (102, 76567, '6/8/2019','Return');
18 rows affected
with hc as (
select hc.*,
sum(case when recordtype = 'Admit' or
recordtype = 'Return' and prev_admit_end > prev_admit
then 1 else 0
end) over (partition by customer, location order by date) as grp
from (select hc.*,
max(case when recordtype = 'Admit' then date end) over (partition by customer, location order by date) as prev_admit,
max(case when recordtype in ('Discharge', 'Expired', 'Discharge-Returning') then date end) over (partition by customer, location order by date) as prev_admit_end
from homecare hc
) hc
)
select location, customer,
max(case when seqnum_asc = 1 then recordtype end) as admit_status,
min(date) as admit_date,
(case when max(case when seqnum_desc = 1 then recordtype end) in ('Discharge', 'Expired', 'Discharge-Returning')
then max(case when seqnum_desc = 1 then recordtype end)
else 'Still in the location'
end) as discharge_date,
max(case when seqnum_desc = 1 then recordtype end) as discharge_status,
(case when max(case when seqnum_desc = 1 then recordtype end) in ('Discharge', 'Expired', 'Discharge-Returning')
then max(date) else '9999-12-31'
end) as discharge_date
from (select hc.*,
row_number() over (partition by location, customer, grp order by date) as seqnum_asc,
row_number() over (partition by location, customer, grp order by date desc) as seqnum_desc
from hc
) hc
group by grp, location, customer
order by min(date)
| location | customer | admit_status | admit_date | discharge_date | discharge_status | discharge_date |
|---|---|---|---|---|---|---|
| 100 | 45454 | Admit | 2019-03-20 | Expired | Expired | 2019-04-05 |
| 101 | 34567 | Admit | 2019-03-27 | Still in the location | Admit | 9999-12-31 |
| 102 | 76567 | Admit | 2019-03-30 | Discharge | Discharge | 2019-04-01 |
| 100 | 56787 | Admit | 2019-04-05 | Expired | Expired | 2019-04-09 |
| 102 | 76567 | Admit | 2019-05-02 | Discharge-Returning | Discharge-Returning | 2019-05-12 |
| 102 | 76567 | Return | 2019-05-17 | Discharge-Returning | Discharge-Returning | 2019-05-30 |
| 102 | 76567 | Return | 2019-06-08 | Still in the location | Return | 9999-12-31 |
Warning: Null value is eliminated by an aggregate or other SET operation.