如何使用SQL在同一列中查找日期之间的差异?
我试图解决以下挑战:如何使用SQL在同一列中查找日期之间的差异?
1)如果患者在48小时内访问ER,我想标记为1
。 2)如果同一患者在48小时后再次访问ER,我想标记为2
。
3)每个后续访问必须标记为3
,4
,5
等48小时后。
这里是我的表是什么样子:
PATIENT_ID ADMIT_DATE LOCATION
---------- ---------- --------
33 1/10/2014 ER
33 1/11/2014 ER
33 1/15/2014 ER
33 1/17/2014 ER
45 2/20/2014 OBS
45 2/21/2014 OBS
45 2/25/2014 OBS
45 2/30/2014 OBS
45 2/32/2014 OBS
这里是理想的结果应该是什么样子:
PATIENT_ID ADMIT_DATE LOCATION FLAG
---------- ---------- -------- ----
33 1/10/2014 ER 1
33 1/15/2014 ER 2
33 1/17/2014 ER 3
45 2/20/2014 OBS 1
45 2/25/2014 OBS 2
45 2/30/2014 OBS 3
45 2/32/2014 OBS 4
我已经开始这样的事情,但是无法完成它:
SELECT PATIENT_ID, ADMIT_DATE, LOCATION,
CASE WHEN MIN(ADMIT_DATE)-MAX(ADMIT_DATE)<48 THEN 1 ELSE 0 AS FLAG
FROM MYTABLE
GROUP BY PATIENT_ID, ADMIT_DATE, LOCATION
有人可以帮忙吗?
你可以做到这一点很容易使用LAG,DATEDIFF和ROWNUMBER功能。 LAG
函数可帮助您获得以前的ADMIT_DATE
值。然后,您可以使用DATEDIFF
函数计算以小时计的差异。最后,使用ROWNUMBER
你可以简单地排列你的结果。
这个充满工作示例:
SET NOCOUNT ON
GO
DECLARE @DataSource TABLE
(
[ATIENT_ID] TINYINT
,[ADMIT_DATE] DATE
,[LOCATION] VARCHAR(3)
)
INSERT INTO @DataSource ([ATIENT_ID], [ADMIT_DATE], [LOCATION])
VALUES (33, '1-10-2014', 'ER')
,(33, '1-11-2014', 'ER')
,(33, '1-15-2014', 'ER')
,(33, '1-17-2014', 'ER')
,(45, '2-15-2014', 'OBS')
,(45, '2-16-2014', 'OBS')
,(45, '2-20-2014', 'OBS')
,(45, '2-25-2014', 'OBS')
,(45, '2-27-2014', 'OBS')
;WITH DataSource ([ATIENT_ID], [ADMIT_DATE], [LOCATION], [DIFF_IN_HOURS]) AS
(
SELECT [ATIENT_ID]
,[ADMIT_DATE]
,[LOCATION]
,DATEDIFF(
HOUR
,LAG([ADMIT_DATE], 1, NULL) OVER (PARTITION BY [ATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
,[ADMIT_DATE]
)
FROM @DataSource
)
SELECT [ATIENT_ID]
,[ADMIT_DATE]
,[LOCATION]
,ROW_NUMBER() OVER (PARTITION BY [ATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
FROM DataSource
WHERE [DIFF_IN_HOURS] >= 48
OR [DIFF_IN_HOURS] IS NULL -- these are first records
SET NOCOUNT OFF
GO
注意,我有固定的样本数据,因为它是错误的。
这是一个没有LAG
功能的替代解决方案:
;WITH TempDataSource ([ATIENT_ID], [ADMIT_DATE], [LOCATION], [Rank]) AS
(
SELECT [ATIENT_ID]
,[ADMIT_DATE]
,[LOCATION]
,ROW_NUMBER() OVER (PARTITION BY [ATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
FROM @DataSource
),
DataSource ([ATIENT_ID], [ADMIT_DATE], [LOCATION], [DIFF_IN_HOURS]) AS
(
SELECT DS1.[ATIENT_ID]
,DS1.[ADMIT_DATE]
,DS1.[LOCATION]
,DATEDIFF(HOUR, DS2.[ADMIT_DATE], DS1.[ADMIT_DATE])
FROM TempDataSource DS1
LEFT JOIN TempDataSource DS2
ON DS1.[Rank] - 1 = DS2.[Rank]
AND DS1.[ATIENT_ID] = DS2.[ATIENT_ID]
AND DS1.[LOCATION] = DS2.[LOCATION]
)
SELECT [ATIENT_ID]
,[ADMIT_DATE]
,[LOCATION]
,ROW_NUMBER() OVER (PARTITION BY [ATIENT_ID], [LOCATION] ORDER BY [ADMIT_DATE] ASC)
FROM DataSource
WHERE [DIFF_IN_HOURS] >= 48
OR [DIFF_IN_HOURS] IS NULL -- these are first records
感谢您的详细解决方案,但我有一个问题。 LAG函数只适用于SQL Server 2012,但我正在使用SQL Server 2008.是否有另一个功能或不同的解决方案,我可以使用?谢谢 – moe 2014-11-02 15:05:40
是的,它仅适用于“SQL Server 2012”和“SQL Server 2014”。 workarround很简单 - 使用'OVER(PARTITION BY [ATIENT_ID],[LOCATION] ORDER BY [ADMIT_DATE] ASC)''使用'ROW_NUMBER()'函数创建一个临时的排名结果集,然后使用'SELECT'和' SELF JOIN'来获取之前的记录。如果有什么不清楚,请告诉我。 – gotqn 2014-11-02 16:18:26
@moe检查编辑。 – gotqn 2014-11-02 16:26:04
你可以使用DATEDIFF()在SQL服务器提供类似
SELECT DATEDIFF(hour,startDate,endDate) AS 'Duration'
SELECT Patient_id,Admit_date, Location,
CASE WHEN DATEDIFF (HH , min(admit_date) , max(admit_date)) < 48 THEN count(flag)+1 ELSE 0 End As Flag
FROM tbl_Patient
GROUP BY PATIENT_ID, ADMIT_DATE, LOCATION
是什么 “但无法完成它” 是什么意思? – 2014-11-02 04:46:25
没有2014年2月31日和32日:-) – gotqn 2014-11-02 09:24:59