question

Summer55 avatar image
0 Votes"
Summer55 asked ·

SQL Admit and Discharge Events

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








sql-server-transact-sql
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It may be that English is not my native language, or it may be that I don't know the business domain. What is a "discharge event"?

I think it could help if you could explain the business rules in a little more detail and why you want that particular result?

0 Votes 0 ·
cheong00 avatar image cheong00 ErlandSommarskog ·

From the table name "home care" I think it could be the admission control of medical facilities that have wards. And "Discharge" mean the doctor have made decision to let the patient go home, and "Discharge-Returning" mean the patient gone home is returning to the facility.

0 Votes 0 ·
pituach avatar image
1 Vote"
pituach answered ·

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




image.png (39.3 KiB)
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

II have uploaded a new dataset. The above query does not work on new dataset.

0 Votes 0 ·
Summer55 avatar image
0 Votes"
Summer55 answered ·

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.

· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So why don't you mark the query as answer?!?

Why did you mark your own respond as answer?

Please change it and ,ark the answer you got as the answer 😀

0 Votes 0 ·


Hi, So sorry for it. I accidentally did it. I have corrected it. Thanks a lot.

1 Vote 1 ·

No problem :-)

I am glad we could help

0 Votes 0 ·