sql-server - 根据接收和修复日期计算每周结束时未修复的索赔数量

Question

我有一个索赔表，其中保存了从客户那里收到的索赔，如下所示

create table claims(id int identity(1,1), Reception_Date datetime, Fixing_Date datetime)

如果 Fixing_Date 为 Null，则表示声明未修复，否则已修复。

我想创建一个存储过程，它返回给定年份的以下数据：返回的列是

1. 周数

2. 该周结束的日期（星期日）

3. 收到的索赔数量

4. 固定索赔数量

5. 当周结束时的非固定索赔数量

数据示例：

    insert into claims (reception_date,fixing_date)
values
('02/20/2019 16:15','01/03/2020 17:00'),('01/04/2020 16:15','01/06/2020 17:00'),
('01/09/2020 16:15','09/21/2020 17:00'),('01/10/2020 16:15','10/21/2020 17:00'),
('10/10/2020 16:15','10/25/2020 17:20'),('10/24/2020 16:15','10/29/2020 14:20'),
('10/10/2020 16:15',NULL),('10/30/2020 16:15','10/31/2020 17:20'),
('10/10/2020 16:15','01/11/2020 16:22'),('11/01/2020 16:15','10/17/2020 08:20'),
('02/11/2020 16:15',NULL),('03/11/2020 16:15','10/11/2020 08:00'),
('05/11/2020 16:15',NULL),('05/11/2020 16:15','06/11/2020 11:20'),
('06/11/2020 16:15',NULL)

标签： sql-servertsqlrecursioncommon-table-expressiondatepart

Answer 1

不要在每次执行时重新计算你的周表，而是建立一个永久的日期表并重复使用它。在我这边，您的查询平均需要 140 毫秒，而使用永久日期表则需要 6 毫秒

也不要过于依赖隐式转换，DATE 到 DATETIME 将被隐式转换并减慢您的查询速度。它还可能使您的查询成为非 SARGABLE，这将忽略索引并导致表扫描。

这是一个基本的日期表填充脚本，对于这个演示，我将其保留为临时表，最好将其创建为实际的索引表

DECLARE @StartDate DATETIME = '2020-01-01 00:00:00'
DECLARE @EndDate DATETIME = '2021-01-01 00:00:00'

IF OBJECT_ID('tempdb..#Dates') IS NOT NULL DROP TABLE #Dates
CREATE TABLE #Dates (
    DateKey INT,
    WeekId INT,
    WeekStartDate DATE,
    StartDate DATETIME,
    EndDate DATETIME,
    DayNumber INT
)

WHILE (@StartDate < @EndDate)
BEGIN

INSERT INTO #Dates VALUES (FORMAT(@StartDate,'yyyyMMdd'), 
       DATEPART(WEEK,@StartDate),      
       DATEADD(DAY , 7-DATEPART(WEEKDAY,@StartDate),@StartDate),
       @StartDate,
       DATEADD(SECOND,-1,DATEADD(DAY,1,@StartDate)),
       DATEPART(WEEKDAY,@StartDate))

SET @StartDate = DATEADD(DAY,1,@StartDate)

END

一旦你有了你的日期表，你就可以开始利用它了，这个查询平均运行 4 到 6 毫秒

 DECLARE @year INT = 2020
 DECLARE @ClaimStartDate DATETIME = CONVERT(VARCHAR(4),@year)+'-Jan-01'
 DECLARE @ClaimEndDate DATETIME = DATEADD(SECOND,-1,DATEADD(YEAR,1,@ClaimStartDate))
 
 ;WITH WeekBase
 AS
 (SELECT D.WeekStartDate,
         D.WeekId
 FROM #Dates D
 WHERE D.StartDate BETWEEN @ClaimStartDate AND @ClaimEndDate
 GROUP BY D.WeekStartDate,
          D.WeekId),
 NotFixed
 AS
 (SELECT d.WeekStartDate,
       COUNT(1) NotFixedCount
 FROM WeekBase d
     INNER JOIN #CLAIMS ON reception_date < d.WeekStartDate
                        AND reception_date IS NOT NULL
                        AND (fixing_date IS NULL OR fixing_date > d.WeekStartDate)
 GROUP BY d.WeekStartDate),
 Fix
 AS 
 (SELECT D.WeekStartDate AS WeekStartDate,
         COUNT(1) AS FixedCount
 FROM #Dates D
      INNER JOIN #Claims C ON C.fixing_date BETWEEN D.StartDate AND D.EndDate
 WHERE D.StartDate BETWEEN @ClaimStartDate AND @ClaimEndDate
 GROUP BY D.WeekStartDate),
 Received
 AS
 (SELECT D.WeekStartDate AS WeekStartDate,
         COUNT(1) AS ReceivedCount
 FROM #Dates D
      INNER JOIN #Claims C ON C.reception_date BETWEEN D.StartDate AND D.EndDate
 WHERE D.StartDate BETWEEN @ClaimStartDate AND @ClaimEndDate
 GROUP BY D.WeekStartDate)
 SELECT D.WeekStartDate AS WeekStartDate,
        D.WeekId AS W,
        ISNULL(REC.ReceivedCount,0) AS Received,
       ISNULL(FIX.FixedCount,0) AS Fixed,
       ISNULL(NF.NotFixedCount,0) AS NotFixed
 FROM WeekBase D
      LEFT JOIN Received REC ON REC.WeekStartDate = D.WeekStartDate
     LEFT JOIN Fix FIX ON FIX.WeekStartDate = D.WeekStartDate
     LEFT JOIN NotFixed NF ON NF.WeekStartDate = D.WeekStartDate