SQL Admit and Discharge Events

chintan patel 26 Reputation points
2021-03-18T19:15:07.62+00:00

When customer is only on Discharge, Discharge-Returning or Expired then I want to break it down. If he/she is on the Therapy or hospital or any other info. I don't consider it as the discharge events.

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');

I want output as below.

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

sql

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');




  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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. Ronen Ariely 15,096 Reputation points
    2021-03-19T00:18:33.423+00:00

    Good day,

    Please check if this solve your needs

    If it does, then we can discuss better solution for better performance but first let's confirm that I understood what you asked for. I "broke" the solution to multiple CTE to make it simpler to understand :-) 

    With MyCTE as(  
	select Location, Customer, Date, recordtype,   
		--MyGroupCounting = CASE WHEN recordtype = 'Admit' THEN 1 ELSE 0 END,  
		--CASE WHEN recordtype in ('Expired', 'Discharge-Returning', 'Discharge') THEN 1 ELSE 0 END,  
		MyGroup = SUM(  
			CASE WHEN recordtype in ('Expired', 'Discharge-Returning', 'Discharge')  
			THEN 1 ELSE 0 END  
		) OVER (ORDER BY Location, Customer, Date DESC)  
	from homecare  
	--order by Location, Customer, Date  
),  
MyCTE02 as (  
	select Location, Customer, Date, recordtype, MyGroup,   
		RN_Out = ROW_NUMBER() OVER(PARTITION BY Location, Customer,MyGroup ORDER BY Date DESC),  
		RN_In = ROW_NUMBER() OVER(PARTITION BY Location, Customer,MyGroup ORDER BY Date)   
	FROM MyCTE  
),  
CTE_In as (  
	SELECT Location, Customer, Date, recordtype, MyGroup, RN_Out, RN_In  
	FROM MyCTE02  
	WHERE RN_In = 1  
),  
CTE_Out as (  
	SELECT Location, Customer, Date, recordtype, MyGroup, RN_Out, RN_In  
	FROM MyCTE02  
	WHERE RN_Out = 1 and NOT RN_In = 1  
)  
SELECT   
	I.Location, I.Customer,   
	AdmitDate = I.Date, AdmitStatus = I.recordtype,  
	DischargeDate = ISNULL(O.Date,'9999-12-31'), DischargeStatus = ISNULL(O.recordtype,'Still in the location')  
FROM CTE_In I  
LEFT JOIN CTE_Out O ON I.Location = O.Location and I.Customer = O.Customer and I.MyGroup = O.MyGroup  
GO

    79431-image.png

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. chintan patel 26 Reputation points
    2021-03-19T01:50:14.9+00:00

    This is 100% working. I had similar question on the Stakeoverflow but users had answered the question but It was not working.
    I tested the query and It is 100% working.