Hi I have a table for homecare system and I have a series of entry logs for customer. I want to get a specific output. I want to break it down by record type ‘expired’, ‘Discharge’ and ‘Discharge-Returning’. if there is Admit and return events for a customer but there is no expired, discharge or discharge-returning event for that customer in between admit and return events then I want min admit date for that customer. If there is admit date or return date and no discharge event for that customer then I want to add 12/31/9999 showing that he is still in the location. please see how I want an output data below. I have added 4 customer data.
Here is my code.
CREATE TABLE [dbo].[homecare]( [Location] [int] NOT NULL, [Customer] INT NOT NULL, [Date] DATE NOT NULL, [recordtype] Varchar(50) NOT NULL)GO 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');INSERT INTO homecare VALUES (100, 43408, '8/10/2010','Admit');INSERT INTO homecare VALUES (100, 43408, '11/3/2010','Discharge-Returning');INSERT INTO homecare VALUES (100, 43408, '12/01/2010','Return');INSERT INTO homecare VALUES (100, 43408, '12/8/2010','Hospital');INSERT INTO homecare VALUES (100, 43408, '12/12/2010','Return');INSERT INTO homecare VALUES (100, 43408, '12/18/2010','Discharge');INSERT INTO homecare VALUES (100, 45090, '09/01/2010','Admit');INSERT INTO homecare VALUES (100, 45090, '09/03/2012','Therapy');INSERT INTO homecare VALUES (100, 45090, '09/07/2012','Return');INSERT INTO homecare VALUES (100, 45090, '09/10/2012','Hospital');INSERT INTO homecare VALUES (100, 45090, '09/12/2012','Return');INSERT INTO homecare VALUES (100, 45090, '09/17/2012','Discharge');Output:-
Location Customer AdmitDate AdmitStatus DischargeDate DischargeStatus100 45454 3/20/2019 Admit 4/5/2019 Expired101 34567 3/27/2019 Admit 12/31/9999 Still in the location100 56787 4/5/2019 Admit 4/9/2019 Expired102 76567 3/30/2019 Admit 4/1/2019 Discharge102 76567 5/2/2019 Admit 5/12/2019 Discharge-Returning102 76567 5/17/2019 Return 5/30/2019 Discharge-Returning102 76567 6/8/2019 Return 12/31/9999 Still in the location100 43408 8/10/2010 Admit 11/3/2010 Discharge-Returning100 43408 12/01/2010 Return 12/18/2010 Discharge100 45090 09/01/2012 Admit 09/17/2012 DischargeMy New dataset is below. Now, I have converted all Return into “Admit” and Discharge, Discharge-Returning and Expired events into “Discharge” events. My new Dataset is as below. This would solve my entire dataset. Can someone resolve the query?
INSERT INTO homecare VALUES (100, 42000, '3/20/2019','Admit');INSERT INTO homecare VALUES (100, 42000, '3/21/2019','Admit'); INSERT INTO homecare VALUES (100, 42000, '3/22/2019','Discharge');INSERT INTO homecare VALUES (100, 42000, '3/22/2019','Admit');INSERT INTO homecare VALUES (100, 42000, '3/30/2019','Admit');INSERT INTO homecare VALUES (100, 42000, '4/1/2019','Discharge');INSERT INTO homecare VALUES (100, 42000, '4/5/2019','Discharge');INSERT INTO homecare VALUES (101, 42000, '4/9/2019','Admit');INSERT INTO homecare VALUES (100, 43000, '3/19/2019','Admit');INSERT INTO homecare VALUES (100, 43000, '5/21/2019','Admit');INSERT INTO homecare VALUES (100, 43000, '5/25/2019','Discharge');INSERT INTO homecare VALUES (100, 43000, '5/25/2019','Discharge');INSERT INTO homecare VALUES (100, 44000, '5/20/2019','Admit');Desired Output:-
Location Customer AdmitDate AdmitStatus DischargeDate DischargeStatus100 42000 3/20/2019 Admit 3/22/2019 Discharge101 42000 3/22/2019 Admit 4/5/2019 Discharge100 42000 4/9/2019 Admit 12/31/9999 Still in the location100 43000 3/19/2019 Admit 5/25/2019 Discharge102 44000 5/20/2019 Admit 12/31/9999 Still in the locationAdvertisement
Answer
This answers the original version of the question.
This is tricky. The first piece is figuring out where the “stays” start. Obviously “admin” is one, but then you need special logic for the returns. The following query compares the date of the previous “admin” and “exit” status to determine if a “return” is the start of a new row.
After that, it is cumbersome aggregation:
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_datefrom (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 ) hcgroup by grp, location, customerorder by min(date);Here is a db<>fiddle.