根据T-SQL中的日期范围运行记录总数

debugcn 发表于 Dev

用户名

样本数据：

groupID CustomerID  CustomerAddr    work_date   work_order
CA123   ABC12345    123 MAIN ST     2/1/2012    WORKNEW
CA123   ABC12345    123 MAIN ST     10/9/2012   ZZZ888
CA123   ABC12345    123 MAIN ST     3/9/2013    ZZZ131
WA999   ZZZ99909    451 EAST ST     1/13/2013   SY1234
WA999   ZZZ99909    451 EAST ST     4/15/2013   WORKOTHER
WA999   ZZZ99909    451 EAST ST     5/17/2013   SY1244
WA999   ZZZ99909    451 EAST ST     12/8/2013   SY1334
CA123   ABD54321    522 AVE A       4/21/2013   WW9999

对于给定的样本数据，我需要计算在6、12和18个月的间隔内出现具有相同groupID和CustomerID的记录的次数。该计数应独立于具有相同groupID和customerID的其他记录。

输出应如下所示：

groupID CustomerID  work_date   six_month_count twelve_month_count  twenty_four_month_count
CA123   ABC12345    2/1/2012    0               1                   2
CA123   ABC12345    10/9/2012   1               1                   1
CA123   ABC12345    3/9/2013    0               0                   0
WA999   ZZZ99909    1/13/2013   2               3                   3               
WA999   ZZZ99909    4/15/2013   1               2                   2
WA999   ZZZ99909    5/17/2013   0               1                   1
WA999   ZZZ99909    12/8/2013   0               0                   0
CA123   ABD54321    4/21/2013   0               0                   0

我唯一想做的就是获取每行的当前记录和最大记录，但是我不知道如何计算给定范围的记录。

任何帮助，将不胜感激。

斯图尔特

您可以将表连接到自身，然后通过聚合计算出6,12和24列。我已将其用作Work_Order避免在自我联接过程中对记录与自身进行计数的关键。

WITH cteCountInRange
AS
(
    SELECT c1.CustomerID, 
           c1.GroupID, 
           c1.Work_Date as WorkDate1, 
           c2.Work_Date as WorkDate2, 
           CASE WHEN c2.Work_Date > c1.Work_Date AND c1.Work_Order <> c2.Work_Order 
                THEN 1 
                ELSE 0 
           END AS Eligible,
           DATEDIFF(M, c1.Work_Date, c2.Work_Date ) AS MonthsDiff
    FROM CustomerData c1 INNER JOIN CustomerData c2
        ON c1.CustomerID = c2.CustomerID AND c1.GroupID = c2.GroupID
)
SELECT cir.CustomerID, cir.GroupID, cir.WorkDate1, 
    SUM(CASE WHEN Eligible = 1 AND MonthsDiff <= 6 THEN 1 ELSE 0 END) AS SixMonthCount,
    SUM(CASE WHEN Eligible = 1 AND MonthsDiff <= 12 THEN 1 ELSE 0 END) AS TwelveMonthCount,
    SUM(CASE WHEN Eligible = 1 AND MonthsDiff <= 24 THEN 1 ELSE 0 END) AS TwentyFourMonthCount
FROM cteCountInRange cir
GROUP BY cir.CustomerID, cir.GroupID, cir.WorkDate1;

在这里摆弄

编辑
附加注释-之所以无法在CTE中过滤掉“不合格”行，是因为如果当前行之后没有其他匹配的行，则需要一行ZEROES。如果我们对它们进行了过滤，我们将需要另一个LEFT JOIN返回原始表以包含零行。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。