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 DischargeStatus 100 45454 3/20/2019 Admit 4/5/2019 Expired 101 34567 3/27/2019 Admit 12/31/9999 Still in the location 100 56787 4/5/2019 Admit 4/9/2019 Expired 102 76567 3/30/2019 Admit 4/1/2019 Discharge 102 76567 5/2/2019 Admit 5/12/2019 Discharge-Returning 102 76567 5/17/2019 Return 5/30/2019 Discharge-Returning 102 76567 6/8/2019 Return 12/31/9999 Still in the location 100 43408 8/10/2010 Admit 11/3/2010 Discharge-Returning 100 43408 12/01/2010 Return 12/18/2010 Discharge 100 45090 09/01/2012 Admit 09/17/2012 Discharge
My 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 DischargeStatus 100 42000 3/20/2019 Admit 3/22/2019 Discharge 101 42000 3/22/2019 Admit 4/5/2019 Discharge 100 42000 4/9/2019 Admit 12/31/9999 Still in the location 100 43000 3/19/2019 Admit 5/25/2019 Discharge 102 44000 5/20/2019 Admit 12/31/9999 Still in the location
Advertisement
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_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);
Here is a db<>fiddle.