sql - SQL 帮助 - 在当前记录时间戳之前查找顺序
问题描述
如果这个问题令人困惑，请原谅我：我有一个病人级别的数据，其中包含护士和医生创建的记录。
对于每位患者，医生输入每位患者的每日目标值。医生可以多次修改这个目标
在一天的过程中，护士输入一个价值，目标是总价值等于医生设定的目标。
我正在尝试编写一个查询 1. 对于每个患者，返回护士输入的每个值，并查找在护士记录他/她的值之前发出的最新医嘱。
以下是我一直在研究的 SQL
--*********************************************-***************************************************
-- As of 10/11/2018 - Runtime = 1:40 min
DECLARE @StartDate DATETIME, @EndDate DATETIME
-- Change the following 2 variables to select a different date range for patient attribution
SET @StartDate = '2018-03-01 00:00:00.000'
SET @EndDate = '2018-10-31 00:00:00.000'
--*************************************************
Select Distinct
results.LOCATION_ABBR
,results.PROTOCOL_NAME
,results.PROV_NAME
,results.MRN
,results.OSU_CSN
,results.PAT_NAME
,results.AGE_YEARS,
results.gender,
results.Race,
results.HOSP_ADMSN_TIME,
results.HOSP_DISCH_TIME,
results.OrderDate,
results.TF_ORDERED
-- ,results.TF_Datetime
,results.TF_Date
,results.TF_Time
,results.TF_Daily
,SUM(results.TF_Daily) OVER (partition by results.OSU_CSN, results.TF_Date) AS Daily_TF_Total_v2
,results.Total_Required
,results.Daily_TF_Total
-- ,results.First_TF
-- ,results.Last_TF
From (
Select Distinct
OSU_Data.LOCATION_ABBR
,OSU_Data.PROTOCOL_NAME
,OSU_Data.PROV_NAME
,OSU_Data.MRN
,OSU_Data.OSU_CSN
,OSU_Data.PAT_NAME
,OSU_Data.AGE_YEARS,
OSU_Data.gender,
Race.PatRace as Race,
OSU_Data.HOSP_ADMSN_TIME,
OSU_Data.HOSP_DISCH_TIME,
OSU_Data.DATE_USED,
OSU_Data.OrderDate,
OSU_Data.TF_ORDERED
,TF_Daily.TF_Datetime
,TF_Daily.TF_Date
,TF_Daily.TF_Time
,TF_Daily.TF_Daily
,Max(cast(OSU_Data.TF_ORDERED as numeric(9,2))) OVER (partition by OSU_Data.OSU_CSN, OSU_Data.DATE_USED) AS Total_Required
,SUM(TF_Daily.TF_Daily) OVER (partition by OSU_Data.OSU_CSN, TF_Daily.TF_Date) AS Daily_TF_Total
,row_number() over (partition by OSU_Data.OSU_CSN order by TF_Daily.TF_Datetime) First_TF
,row_number() over (partition by OSU_Data.OSU_CSN order by TF_Daily.TF_Datetime desc) Last_TF
-- ,LAG(OSU_Data.OrderDate) OVER (ORDER BY OSU_Data.OSU_CSN,OSU_Data.OrderDate,TF_Datetime) PreviousTFOrderDT
-- ,LEAD(OSU_Data.OrderDate) OVER (ORDER BY OSU_Data.OSU_CSN,OSU_Data.OrderDate,TF_Datetime) NextTFOrderDT
From
(
Select
preOSU_Data.Orderid
,preOSU_Data.PROTOCOL_ID
,preOSU_Data.PROTOCOL_NAME
,preOSU_Data.LOCATION_ABBR
,preOSU_Data.PROV_NAME
,preOSU_Data.PAT_ID
,preOSU_Data.MRN
,preOSU_Data.OSU_CSN
,preOSU_Data.PAT_NAME
,preOSU_Data.AGE_YEARS
,preOSU_Data.gender
,Race.PatRace
,preOSU_Data.OrderDate
-- ,preOSU_Data.TF_GoalHr
,preOSU_Data.HOSP_ADMSN_TIME
,preOSU_Data.HOSP_DISCH_TIME
,preOSU_Data."YEAR USED"
,preOSU_Data."MONTH USED"
,preOSU_Data.DATE_USED
,preOSU_Data."For Sorting"
,preOSU_Data.TF_ORDERED
From
(Select Distinct
ORDERS.ORDER_ID as Orderid
,CL_PRL_SS.PROTOCOL_ID
,CL_PRL_SS.PROTOCOL_NAME
,CLARITY_LOC.LOCATION_ABBR
,CLARITY_SER.PROV_NAME
,PAT_ENC_HSP.PAT_ID
,V_PAT_FACT.PAT_MRN_ID as MRN
,PAT_ENC_HSP.PAT_ENC_CSN_ID as OSU_CSN
,V_PAT_FACT.PAT_NAME
,V_PAT_FACT.AGE_YEARS
,V_PAT_FACT.SEX_NAME as gender
,ORDER_INST as OrderDate
,DATEPART(HOUR, ORDER_INST) as TF_GoalHr
,PAT_ENC_HSP.HOSP_ADMSN_TIME
,PAT_ENC_HSP.HOSP_DISCH_TIME
,YEAR(ORDER_INST) "YEAR USED"
,DATENAME(MONTH, ORDER_INST) "MONTH USED"
,DATEFROMPARTS(YEAR(ORDER_INST),MONTH(ORDER_INST),DAY(ORDER_INST)) DATE_USED
,DATEFROMPARTS(YEAR(ORDER_INST),MONTH(ORDER_INST),1) "For Sorting"
,ORD_SPEC_QUEST.ORD_QUEST_RESP as TF_ORDERED
,Max(ORDER_INST) OVER (partition by PAT_ENC_HSP.PAT_ENC_CSN_ID, DATEFROMPARTS(YEAR(ORDER_INST),MONTH(ORDER_INST),DAY(ORDER_INST))) AS Last_DailyTFGoalTimestamp
From
PAT_ENC_HSP
,ORDERS
,ORDER_PROC
,ORDER_SMARTSET
,CL_PRL_SS
,ZC_PAT_SERVICE
,ORD_SPEC_QUEST
,CL_QQUEST
,V_PAT_FACT
,CLARITY_SER
,CLARITY_ADT
,CLARITY_DEP
,CLARITY_LOC
,ZC_PATIENT_RACE
Where
(PAT_ENC_HSP.PAT_ENC_CSN_ID = ORDER_SMARTSET.PAT_ENC_CSN_ID
AND ORDERS.ORDER_ID = ORDER_SMARTSET.ORDER_ID
AND ORDER_PROC.ORDER_PROC_ID=ORDER_SMARTSET.ORDER_ID
AND ORDER_SMARTSET.SS_PRL_ID=CL_PRL_SS.PROTOCOL_ID
AND ORDERS.ORDER_ID = ORD_SPEC_QUEST.ORDER_ID
AND ORD_SPEC_QUEST.ORD_QUEST_ID = CL_QQUEST.QUEST_ID)
and PAT_ENC_HSP.PAT_ENC_CSN_ID = CLARITY_ADT.PAT_ENC_CSN_ID
and CLARITY_ADT.DEPARTMENT_ID = CLARITY_DEP.DEPARTMENT_ID
and CLARITY_DEP.REV_LOC_ID = CLARITY_LOC.LOC_ID
and PAT_ENC_HSP.PAT_ID = V_PAT_FACT.PAT_ID
and ORDER_PROC.AUTHRZING_PROV_ID = CLARITY_SER.PROV_ID
and PAT_ENC_HSP. HOSP_ADMSN_TIME between @StartDate and @EndDate
AND CL_PRL_SS.PROTOCOL_ID = 799
AND CAST(CL_QQUEST.QUEST_ID as varchar(25)) = '101960'
UNION
Select Distinct
ORDERS.ORDER_ID as Orderid
,CL_PRL_SS.PROTOCOL_ID
,CL_PRL_SS.PROTOCOL_NAME
,CLARITY_LOC.LOCATION_ABBR
,CLARITY_SER.PROV_NAME
,PAT_ENC_HSP.PAT_ID
,V_PAT_FACT.PAT_MRN_ID as MRN
,PAT_ENC_HSP.PAT_ENC_CSN_ID as OSU_CSN
,V_PAT_FACT.PAT_NAME
,V_PAT_FACT.AGE_YEARS
,V_PAT_FACT.SEX_NAME as gender
,ORDER_INST as OrderDate
,DATEPART(HOUR, ORDER_INST) as TF_GoalHr
,PAT_ENC_HSP.HOSP_ADMSN_TIME
,PAT_ENC_HSP.HOSP_DISCH_TIME
,YEAR(ORDER_INST) "YEAR USED"
,DATENAME(MONTH, ORDER_INST) "MONTH USED"
,DATEFROMPARTS(YEAR(ORDER_INST),MONTH(ORDER_INST),DAY(ORDER_INST)) DATE_USED
,DATEFROMPARTS(YEAR(ORDER_INST),MONTH(ORDER_INST),1) "For Sorting"
,ORD_SPEC_QUEST.ORD_QUEST_RESP as TF_ORDERED
,Max(ORDER_INST) OVER (partition by PAT_ENC_HSP.PAT_ENC_CSN_ID, DATEFROMPARTS(YEAR(ORDER_INST),MONTH(ORDER_INST),DAY(ORDER_INST))) AS Last_DailyTFGoalTimestamp
From
PAT_ENC_HSP
,ORDERS
,ORDER_PROC
,ORDER_METRICS
,CL_PRL_SS
,ZC_PAT_SERVICE
,ORD_SPEC_QUEST
,CL_QQUEST
,V_PAT_FACT
,CLARITY_SER
,CLARITY_ADT
,CLARITY_DEP
,CLARITY_LOC
Where
(PAT_ENC_HSP.PAT_ENC_CSN_ID = ORDER_METRICS.PAT_ENC_CSN_ID
and ORDERS.ORDER_ID = ORDER_METRICS.ORDER_ID
AND ORDER_PROC.ORDER_PROC_ID=ORDER_METRICS.ORDER_ID
AND ORDER_METRICS.PRL_ORDERSET_ID=CL_PRL_SS.PROTOCOL_ID
AND ORDERS.ORDER_ID = ORD_SPEC_QUEST.ORDER_ID
AND ORD_SPEC_QUEST.ORD_QUEST_ID = CL_QQUEST.QUEST_ID)
and PAT_ENC_HSP.PAT_ENC_CSN_ID = CLARITY_ADT.PAT_ENC_CSN_ID
and CLARITY_ADT.DEPARTMENT_ID = CLARITY_DEP.DEPARTMENT_ID
and CLARITY_DEP.REV_LOC_ID = CLARITY_LOC.LOC_ID
and PAT_ENC_HSP.PAT_ID = V_PAT_FACT.PAT_ID
and ORDER_PROC.AUTHRZING_PROV_ID = CLARITY_SER.PROV_ID
and PAT_ENC_HSP. HOSP_ADMSN_TIME between @StartDate and @EndDate
AND CL_PRL_SS.PROTOCOL_ID = 799
AND CAST(CL_QQUEST.QUEST_ID as varchar(25)) = '101960'
) as preOSU_Data
--********************************************************
-- PATIENT_RACE has multiple lines for some patients
-- Added the following to return Line 1 from Patient_Race
Inner Join(
SELECT DISTINCT
PATIENT_RACE.PAT_ID,
ZC_PATIENT_RACE.NAME as PatRace
FROM PATIENT_RACE, ZC_PATIENT_RACE
WHERE
PATIENT_RACE.PATIENT_RACE_C = ZC_PATIENT_RACE.PATIENT_RACE_C
and LINE = 1
) as Race on preOSU_Data.PAT_ID = Race.PAT_ID
WHERE preOSU_Data.OrderDate = Last_DailyTFGoalTimestamp
-- ORDER BY
-- preOSU_Data.OSU_CSN
-- ,preOSU_Data.OrderDate
) as OSU_Data
--********************************************************
-- PATIENT_RACE has multiple lines for some patients
-- Added the following to return Line 1 from Patient_Race
Inner Join(
SELECT DISTINCT
PATIENT_RACE.PAT_ID,
ZC_PATIENT_RACE.NAME as PatRace
FROM PATIENT_RACE, ZC_PATIENT_RACE
WHERE
PATIENT_RACE.PATIENT_RACE_C = ZC_PATIENT_RACE.PATIENT_RACE_C
and LINE = 1
) as Race on OSU_Data.PAT_ID = Race.PAT_ID
-- WHERE
-- OSU_Data.OrderDate = OSU_Data.Last_DailyTFGoalTimestamp
--
---------------------------------------------------------
-- Daily TF given
---------------------------------------------------------
Inner join
(
select distinct
PATIENT.PAT_NAME,
PAT_ENC.HOSP_DISCHRG_TIME,
pat_enc.pat_enc_csn_id,
IP_FLWSHT_MEAS.recorded_time as TF_Datetime,
CONVERT (DATE,IP_FLWSHT_MEAS.recorded_time) as TF_Date,
Format(IP_FLWSHT_MEAS.recorded_time, 'h:mm tt') as TF_Time,
DATEPART(HOUR, IP_FLWSHT_MEAS.recorded_time) as TF_Hour,
isnull(cast(IP_FLWSHT_MEAS.MEAS_VALUE as float),0) as TF_Daily
FROM
PAT_ENC
LEFT OUTER JOIN PATIENT ON PAT_ENC.PAT_ID = PATIENT.PAT_ID
LEFT OUTER JOIN PAT_ENC_2 ON PAT_ENC.PAT_ENC_CSN_ID = PAT_ENC_2.PAT_ENC_CSN_ID
LEFT OUTER JOIN ZC_PAT_CLASS ON PAT_ENC_2.ADT_PAT_CLASS_C = ZC_PAT_CLASS.ADT_PAT_CLASS_C
LEFT OUTER JOIN IP_FLWSHT_REC ON PAT_ENC.INPATIENT_DATA_ID = IP_FLWSHT_REC.INPATIENT_DATA_ID
LEFT OUTER JOIN IP_FLWSHT_MEAS ON IP_FLWSHT_REC.FSD_ID = IP_FLWSHT_MEAS.FSD_ID
where
PAT_ENC. HOSP_ADMSN_TIME between @StartDate and @EndDate
and IP_FLWSHT_MEAS.FLO_MEAS_ID = '3043040002'
-- and IP_FLWSHT_MEAS.FLT
Group by
PATIENT.PAT_NAME,
PAT_ENC.HOSP_DISCHRG_TIME,
PAT_ENC_2.ADT_PAT_CLASS_C,
pat_enc.pat_enc_csn_id,
IP_FLWSHT_MEAS.recorded_time,
IP_FLWSHT_MEAS.MEAS_VALUE
) as TF_Daily on OSU_Data.OSU_CSN = TF_Daily.pat_enc_csn_id and TF_Datetime > OSU_Data.OrderDate -- and Osu_Data.TF_GoalHr = TF_Daily.TF_Hour
--**********************************************************
--and TF_Daily.TF_Datetime >=
Group by
OSU_Data.OSU_CSN
,OSU_Data.OrderDate
,OSU_Data.DATE_USED
,TF_Datetime
,OSU_Data.LOCATION_ABBR
,OSU_Data.PROTOCOL_NAME
,OSU_Data.PROV_NAME
,OSU_Data.MRN
,OSU_Data.PAT_NAME
,OSU_Data.AGE_YEARS,
OSU_Data.gender,
Race.PatRace,
-- OSU_Data.Race,
OSU_Data.HOSP_ADMSN_TIME,
OSU_Data.HOSP_DISCH_TIME
,TF_Daily.TF_Date
,TF_Daily.TF_Daily
--,TF_Daily2
,TF_Daily.TF_Time
,TF_Daily.TF_Hour
,OSU_Data.TF_ORDERED
) as results
-- Exclusions and Clean-up
Where
results.Total_Required >= 800
and results.Daily_TF_Total > 0
解决方案
推荐阅读
- django - axios post dosent 将任何数据发送到 django
- java - 在不使用 Fielddata 的情况下对弹性搜索中的文本字段进行排序
- java - 测试失败，“when() 需要一个参数，该参数必须是'模拟上的方法调用'” maven 项目错误（未使用框架）
- ios - Xcode11.3.1：“此时无法安装此应用”
- rust - 具有关联类型的工厂方法
- java - java.lang.Object getTimeout() 的返回类型与 org.gradle.api.internal.AbstractTask 中的 org.gradle.api.provider.Property 不兼容
- c# - 使用从 mysql 到 raport/pdf/word C# 的数据创建表的问题
- excel - 如何删除具有零的特定多个列？
- spring-boot - Spring数据JPA中按时间戳排序
- javascript - 如何从 node.js 连接到 sql DB？