为具有最接近日期和 ID 的数据选择脚本作为仲裁

Question

下面是一个生成示例数据的脚本，我需要从中编写一个选择语句以为每个 CareID 返回一个 PlanID。计划ID 需要具有最接近诊断日期的商定日期或 MDTDate。在倍数的情况下，最高值 PlanID 是仲裁规则。最近的日期是在诊断日期之前还是之后并不重要。谢谢。

create table ClosestDate
(CareID int not null, PlanID int not null, 
PerformanceStatus int not null, DiagnosisDate date null,
AgreedDate date null, MDTDate date null)
insert into ClosestDate values (1,1833,2,'12/07/2013','20/08/2011','20/08/2011')
insert into ClosestDate values (4,65,3,'28/07/2009','27/11/2009','27/11/2009')
insert into ClosestDate values (7,68,0,'19/09/2009','22/09/2009','09/12/2009')
insert into ClosestDate values (8,69,2,'30/10/2009','15/12/2009','15/12/2009')
insert into ClosestDate values (9,64,1,'02/10/2009','18/11/2009','18/11/2009')
insert into ClosestDate values (23,278,1,'21/07/2009','15/09/2009','15/09/2009')
insert into ClosestDate values (63,66,1,'19/07/2009','27/03/2010','27/03/2010')
insert into ClosestDate values (70,40,0,'24/03/2010','23/03/2010','23/03/2010')
insert into ClosestDate values (106,67,1,'27/08/2009','04/04/2010','05/04/2010')
insert into ClosestDate values (109,72,1,'10/12/2009','06/01/2010','06/01/2010')
insert into ClosestDate values (110,74,3,'13/12/2009','15/01/2010','08/01/2010')
insert into ClosestDate values (111,57,1,'01/09/2009','03/11/2009','03/11/2009')
insert into ClosestDate values (149,1640,1,'05/07/2011','05/07/2011','05/07/2011')
insert into ClosestDate values (199,110,1,'12/01/2010','20/02/2010',NULL)
insert into ClosestDate values (205,73,1,'14/12/2009','08/02/2010','08/02/2010')
insert into ClosestDate values (209,109,3,'15/01/2010','09/02/2010',NULL)
insert into ClosestDate values (222,104,4,'26/03/2010','06/04/2010','06/04/2010')
insert into ClosestDate values (222,105,3,'26/03/2010','08/04/2010','08/04/2010')
insert into ClosestDate values (222,106,1,'26/03/2010','09/04/2010','09/04/2010')
insert into ClosestDate values (222,111,3,'26/03/2010','09/04/2010','09/04/2010')
insert into ClosestDate values (228,136,3,'02/04/2010','07/07/2010','07/07/2010')
insert into ClosestDate values (304,4054,2,'11/06/2013','11/06/2013','11/06/2013')
insert into ClosestDate values (304,5151,1,'11/06/2013','17/03/2014','17/03/2014')
insert into ClosestDate values (305,1507,2,'14/10/2010','14/05/2011','14/05/2011')

Answer 1

首先，我必须重新格式化您的数据才能将其转换为date类型。我手动完成，所以这里可能有错误，但查询的逻辑不应该受到影响。重新格式化的代码：

create table #ClosestDate
(CareID int not null, PlanID int not null, 
PerformanceStatus int not null, DiagnosisDate date null,
AgreedDate date null, MDTDate date null)
insert into #ClosestDate values (1,1833,2,  '20130712', '20110820', '20110820')
insert into #ClosestDate values (4,65,3,    '20090728', '20091127', '20091127')
insert into #ClosestDate values (7,68,0,    '20090919', '20090922', '20091209')
insert into #ClosestDate values (8,69,2,    '20091030', '20091215', '20091215')
insert into #ClosestDate values (9,64,1,    '20091002', '20091118', '20091118')
insert into #ClosestDate values (23,278,1,  '20090721', '20090915', '20090915')
insert into #ClosestDate values (63,66,1,   '20090719', '20100327', '20100327')
insert into #ClosestDate values (70,40,0,   '20100324', '20100323', '20100323')
insert into #ClosestDate values (106,67,1,  '20090827', '20100404', '20100405')
insert into #ClosestDate values (109,72,1,  '20091210', '20100106', '20100106')
insert into #ClosestDate values (110,74,3,  '20091213', '20100115', '20100108')
insert into #ClosestDate values (111,57,1,  '20090901', '20091103', '20091103')
insert into #ClosestDate values (149,1640,1,'20110705', '20110705', '20110705')
insert into #ClosestDate values (199,110,1, '20100112', '20100220', NULL)
insert into #ClosestDate values (205,73,1,  '20091214', '20100208', '20100208')
insert into #ClosestDate values (209,109,3, '20100115', '20100209', NULL)
insert into #ClosestDate values (222,104,4, '20100326', '20100406', '20100406')
insert into #ClosestDate values (222,105,3, '20100326', '20100408', '20100408')
insert into #ClosestDate values (222,106,1, '20100326', '20100409', '20100409')
insert into #ClosestDate values (222,111,3, '20100326', '20100409', '20100409')
insert into #ClosestDate values (228,136,3, '20100402', '20100707', '20100707')
insert into #ClosestDate values (304,4054,2,'20130611', '20130611', '20130611')
insert into #ClosestDate values (304,5151,1,'20130611', '20140317', '20140317')
insert into #ClosestDate values (305,1507,2,'20101014', '20110514', '20110514')

这是我提出的解决方案：

; WITH Differences AS (
    -- calculate the differences in days
    SELECT CareID, PlanID, PerformanceStatus, DiagnosisDate, AgreedDate, MDTDate
        , CASE
            -- if any of the dates is null, take the difference to the other one or NULL if both are NULL
            WHEN AgreedDate IS NULL OR MDTDate IS NULL THEN COALESCE(ABS(DATEDIFF(day, DiagnosisDate, AgreedDate)), ABS(DATEDIFF(day, DiagnosisDate, MDTDate)))
            -- take the smaller difference (WHEN AND ELSE cover this logic)
            WHEN ABS(DATEDIFF(day, DiagnosisDate, AgreedDate)) <= ABS(DATEDIFF(day, DiagnosisDate, MDTDate)) THEN ABS(DATEDIFF(day, DiagnosisDate, AgreedDate))
            ELSE ABS(DATEDIFF(day, DiagnosisDate, MDTDate))
          END AS SmallerDiffInDays
    FROM #ClosestDate
), SmallestDifferencesPlanIDPerCareID AS (
    SELECT CareID, PlanID, PerformanceStatus,
        -- partition the data by CareID, order it by SmallerDiffInDays ascending and as a tie-breaker use PlanID DESC
        ROW_NUMBER() OVER (PARTITION BY CareID ORDER BY SmallerDiffInDays, PlanID DESC) AS RwNr
    FROM Differences
)
SELECT CareID, PlanID, PerformanceStatus
FROM SmallestDifferencesPlanIDPerCareID
WHERE RwNr = 1;

代码片段包含注释，这些注释应解释我在上述查询中所做的工作以及原因。

查询的输出为：

CareID  | PlanID    | PerformanceStatus
1       | 1833      | 2
4       | 65        | 3
7       | 68        | 0
8       | 69        | 2
9       | 64        | 1
23      | 278       | 1
63      | 66        | 1
70      | 40        | 0
106     | 67        | 1
109     | 72        | 1
110     | 74        | 3
111     | 57        | 1
149     | 1640      | 1
199     | 110       | 1
205     | 73        | 1
209     | 109       | 3
222     | 104       | 4
228     | 136       | 3
304     | 4054      | 2
305     | 1507      | 2

这是你要找的吗？

Answer 2

这是我能想到的最快的方法，它涉及向表格添加新列，如果您不想更改表格结构，我建议您使用模板

alter table ClosestDate add MinDateDiff bigint;
update ClosestDate Set MinDateDiff = (Case 
                            When ABS(DATEDIFF(DD,DiagnosisDate, AgreedDate)) < ABS(DATEDIFF(DD,DiagnosisDate, MDTDate)) 
                            Then ABS(DATEDIFF(DD,DiagnosisDate, AgreedDate)) 
                            When MDTDate is null 
                            Then ABS(DATEDIFF(DD,DiagnosisDate, AgreedDate)) 
                            Else ABS(DATEDIFF(DD,DiagnosisDate, MDTDate)) 
                        End);
Select a.CareID, Max(PlanID) PlanID
from ClosestDate a
inner join (Select CareID, Min(MinDateDiff) MinDateDiff
from ClosestDate
group by CareID) as b on a.CareID = b.CareID and a.MinDateDiff = b.MinDateDiff
group by a.CareID;