SQL - 如何在 TSQL 中查询重新录取? [英] SQL - How do I query for re-admissions in TSQL?

问题描述

我想弄清楚如何在 Server 2008r2 上查询重新入驻.这是访问表的基本结构.还有其他领域，但我认为没有一个领域会有所帮助.一个问题是，其中一些可能是转移而不是排放，我无法轻松推断出这一点，但现在可以忽略该问题.我尝试了这一点，但我想我对 SQL 的理解需要更多的工作.我试图在网上找到任何我能找到的信息，但没有一个查询能让我得出有用的结论，或者我只是不明白.任何建议将不胜感激.

I'm trying to figure out how to query for readmissions on Server 2008r2. Here is the basic structure of the visit table. There are other fields but none that I thought would be helpful. One issue is that some of these may be transfers instead of discharges which I have no easy way to deduce but that issue can be ignored for now. I tried my hand at this but I guess my understanding of SQL needs more work. I tried to find any info I could online but none of the queries lead me to a useful conclusion or I just didn't understand. Any suggestions would be appreciated.

再入院是指患者在上次出院后 30 天内返回.

Readmission is if a patient returns within 30 days of previous discharge.

+---------+--------+-----------------+-----------------+
| VisitID |  UID   |     AdmitDT     |   DischargeDT   |
+---------+--------+-----------------+-----------------+
|      12 | 2      | 6/17/2013 6:51  | 6/17/2013 6:51  |
|      16 | 3      | 6/19/2013 4:48  | 6/21/2013 13:35 |
|      18 | 3      | 6/11/2013 12:08 | 6/11/2013 12:08 |
|      21 | 3      | 6/12/2013 14:40 | 6/12/2013 14:40 |
|      22 | 3      | 6/13/2013 10:00 | 6/14/2013 12:00 |
|      25 | 2      | 6/11/2013 16:13 | 6/11/2013 16:13 |
|      30 | 1      | 6/20/2013 8:35  | 6/20/2013 8:35  |
|      31 | 7      | 6/13/2013 6:12  | 6/13/2013 6:12  |
|      34 | 3      | 6/12/2013 8:40  | NULL            |
|      35 | 1      | 6/12/2013 8:52  | NULL            |
|      38 | 2      | 6/12/2013 10:10 | 6/12/2013 10:10 |
+---------+--------+-----------------+-----------------+

尝试代码:

SELECT N2.*
FROM visitTable AS N1
INNER JOIN
visitTable AS N2 ON N1.UID = N2.UID
WHERE N1.EncounterID <> N2.EncounterID AND ( N2.AdmitDT BETWEEN N1.DischargeDT and DATEADD(DD,30, N1.DischargeDT))

推荐答案

开始:

sqlfiddle

新小提琴

它按照admitDT的顺序获取每个UID的每次访问，然后将每次访问与该结果中的下一次访问配对.如果当前的录取日期介于最后出院日期和之后的 30 天之间，请选择它.虽然有一些奇怪的地方 - UID 1 显示已于 2012 年 6 月 12 日被录取但从未出院，但随后于 2013 年 6 月 20 日再次被录取并在同一天出院.

It gets each visit for each UID in order of admitDT, then pairs each visit with the next visit in that result. If the current admit date is between the last discharge date and 30 days from then, select it. There are some weird points though - UID 1 is shown to have been admitted on 6/12/2012 and never discharged, but then admitted again on 6/20/2013 and discharged the same day.

稍微重组以减少连接数

WITH cte AS (
  SELECT visitid,uid,dischargedt,admitdt,
    row_number()over(partition BY uid ORDER BY admitdt) AS r
  FROM t
  )
SELECT
c1.visitid AS v1, c2.visitid AS v2,
c1.uid,
c1.dischargedt as [Discharged from first visit],
c2.admitdt as [Admitted to next visit]
FROM cte c1
INNER JOIN cte c2 ON c1.uid=c2.uid
WHERE c1.visitid<>c2.visitid
AND c1.r+1=c2.r
AND c2.admitdt BETWEEN c1.dischargedt AND dateadd(d,30,c1.dischargedt )
ORDER BY c1.uid

结果:

| V1 | V2 | UID | DISCHARGED FROM FIRST VISIT |      ADMITTED TO NEXT VISIT |
|----|----|-----|-----------------------------|-----------------------------|
| 25 | 38 |   2 | June, 11 2013 16:13:00+0000 | June, 12 2013 10:10:00+0000 |
| 38 | 12 |   2 | June, 12 2013 10:10:00+0000 | June, 17 2013 06:51:00+0000 |
| 18 | 34 |   3 | June, 11 2013 12:08:00+0000 | June, 12 2013 08:40:00+0000 |
| 21 | 22 |   3 | June, 12 2013 14:40:00+0000 | June, 13 2013 10:00:00+0000 |
| 22 | 16 |   3 | June, 14 2013 12:00:00+0000 | June, 19 2013 04:48:00+0000 |

这篇关于SQL - 如何在 TSQL 中查询重新录取?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！