时间戳

首页 > 解决方案 > 如何获取带有特定事件的 SQL AdmitDate 和 DischargeDate？

sql - 如何获取带有特定事件的 SQL AdmitDate 和 DischargeDate？

问题描述

嗨，我有一个家庭护理系统表，我有一系列客户的条目日志。我想得到一个特定的输出。我想按记录类型“过期”、“放电”和“放电-返回”来分解它。如果客户有承认和退货事件，但在承认和退货事件之间没有该客户的过期、出院或出院退货事件，那么我想要该客户的最低承认日期。如果该客户有录取日期或返回日期并且没有出院事件，那么我想添加 12/31/9999 表明他仍在该位置。请在下面查看我想要的输出数据。我添加了 4 个客户数据。

这是我的代码。

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

输出：-

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

我的新数据集如下。现在，我已将所有 Return 转换为“Admit”，将 Discharge、Discharge-Returning 和 Expired 事件转换为“Discharge”事件。我的新数据集如下。这将解决我的整个数据集。有人可以解决查询吗？

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

解决方案

我不确定“return”作为记录类型/AdmitStatus 的行为。对于客户 76567，它用于表示一种入院形式，而对于 45454，它用于表示从治疗中返回。因此，低于“治疗”表示客户离开，退货相当于退货。如果 Return 确实表现为重新接纳或只是评论，请告诉我，我将更新/删除我的答案。

WITH Homecare AS (
    Select 
        RN = row_number() over (partition by Customer order by date asc),
        Location, 
        Customer, 
        Date as AdmitDate, 
        recordtype as AdmitStatus 
    from [dbo].[homecare] 
    WHERE recordtype in ('Admit','Return','Expired','Therapy','Discharge','Discharge-Returning')
    --AND Customer in ( Select Customer, recordtype from dbo.Homecare group by Customer, recordtype ))
),
HomecareEx as (
select h.*, 
    lead(h.AdmitDate,1,'12/31/9999') over (partition by h.customer order by h.AdmitDate) as DischargeDate, 
    lead(h.AdmitStatus,1,'Still in Location') over (partition by h.customer order by h.AdmitDate) as DischargeStatus
from Homecare h --Where h.AdmitStatus in ('Admit','Return')
) --select * from HomecareEx
select Location, 
    Customer, 
    AdmitDate, 
    AdmitStatus, 
    DischargeDate,
    DischargeStatus
from HomecareEx 
WHERE Coalesce(DischargeStatus,'Still in Location') not in ('Admit','Return') 
AND AdmitStatus in ('Admit','Return');

代码返回这个，不是你想要的，但它确实取决于“返回”的行为。

地点 顾客 录取日期 承认状态 出院日期 放电状态
101 34567 2019-03-27 承认 9999-12-31 还在位置
100 45454 2019-03-20 承认 2019-03-22 治疗
100 45454 2019-03-29 返回 2019-03-30 治疗
100 45454 2019-04-01 返回 2019-04-05 已到期
100 56787 2019-04-05 承认 2019-04-09 已到期
102 76567 2019-03-30 承认 2019-04-01 释放
102 76567 2019-05-02 承认 2019-05-12 放电-返回
102 76567 2019-05-17 返回 2019-05-30 放电-返回
102 76567 2019-06-08 返回 9999-12-31 还在位置

推荐阅读