调整/编辑SQL查询帮助

Question

我有下面的查询计算从第一个请求的日期到第一个完成日期已经过了多少时间。 但是在下面的例子中，有一个在2017-02-06创建的2017-02-02（backdated）的请求日期。

我想编辑的查询来选择所要求的日期和计算多少时间过去了，直到从FIRST创建日期

所以现在它返回1时，它应该是0 推理完成，因为第一次创建的日期是2017-02-03，并在同一天被要求和完成2017-02-03

任何帮助都很赞赏！

CREATE TABLE #temp 
(Identifier  VARCHAR(40) NOT NULL, 
Created_Date DATETIME NOT NULL, 
Requested_Date DATETIME NOT NULL, 
Completed_Date DATETIME NULL, 
SN_Type  VARCHAR(20) NOT NULL, 
SN_Status  VARCHAR(20) NOT NULL 
); 
INSERT INTO #temp 
VALUES 
('11111', 
'20170203', 
'20170203', 
'20170203', 
'Re-Activattion', 
'COMP' 
); 
INSERT INTO #temp 
VALUES 
('11111', 
'20170206', 
'20170202', 
NULL, 
'Re-Activattion', 
'N-CO' 
); 
SELECT * 
FROM #temp; 

-- calculate/identify Order start and Order End records 
WITH cte 
    AS (
    -- 1st Order start record i.e. earliest record in the table for a given "Identifier" 

    SELECT Identifier, 
      MIN(Created_Date) AS Created_Date, 
      CONVERT(VARCHAR(30), 'Created') AS RecordType, 
      1 AS OrderNumber 
    FROM #temp 
    GROUP BY Identifier 
    UNION ALL 
    -- All records with "COMP" status are treated as order completed events. Add 2 weeks to the completed date to create a "dummy" Order End Date 

    SELECT Identifier, 
      DATEADD(WEEK, 2, Created_Date) AS Created_Date, 
      'Completed' AS RecordType, 
      ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY Created_Date) AS OrderNumber 
    FROM #temp 
    WHERE SN_STATUS = 'COMP' 
    UNION ALL 
    -- Set the start period of the next order to be right after (3 ms) the previous Order End Date 

    SELECT Identifier, 
      DATEADD(ms, 3, DATEADD(WEEK, 2, Created_Date)) AS Created_Date, 
      'Created' AS RecordType, 
      ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY Created_Date) + 1 AS OrderNumber 
    FROM #temp 
    WHERE SN_STATUS = 'COMP'), 
-- Combine Start/End records into one record 
    OrderGroups 
    AS (
    SELECT Identifier, 
      OrderNumber, 
      MIN(Created_Date) AS OrderRangeStartDate, 
      MAX(Created_Date) AS OrderRangeEndDate 
    FROM cte 
    GROUP BY Identifier, 
       OrderNumber) 
    SELECT a.Identifier, 
      a.OrderNumber, 
      OrderRangeStartDate, 
      OrderRangeEndDate, 
      CASE 
       WHEN SUM(CASE 
          WHEN SN_STATUS = 'COMP' 
            AND SN_TYPE = 'Re-Activattion' 
          THEN 1 
          ELSE 0 
         END) > 0 
       THEN STR(DATEDIFF(day, MIN(CASE 
               WHEN SN_TYPE = 'Re-Activattion' 
               THEN Requested_Date 
               ELSE NULL 
              END), MIN(CASE 
                 WHEN(SN_TYPE = 'Re-Activattion' 
                   AND SN_STATUS = 'COMP') 
                 THEN Completed_Date 
                 ELSE NULL 
                END))) 
       WHEN SUM(CASE 
          WHEN SN_TYPE = 'Re-Activattion' 
          THEN 1 
          ELSE 0 
         END) > 0 
       THEN 'NOT COMP' 
       ELSE 'NO RE-ACT' 
      END AS RE_ACT_COMPLETION_TIME, 
      SUM(CASE 
        WHEN SN_STATUS = 'N-CO' 
        THEN 1 
        ELSE 0 
       END) AS [RE-AN NCO #] 
    FROM OrderGroups AS a 
      INNER JOIN #Temp AS b ON a.Identifier = b.Identifier 
            AND a.OrderRangeStartDate <= b.Created_Date 
            AND b.Created_Date <= a.OrderRangeEndDate 
    GROUP BY a.Identifier, 
       a.OrderNumber, 
       OrderRangeStartDate, 
       OrderRangeEndDate; 

Answer 1

我想编辑的查询来选择所要求的日期和计算 多少时间过去了，直到从第一个创建日期完成

更改此：

     ,MIN(case 
           when SN_TYPE = 'Re-Activattion' 
           then Requested_Date 
           else null 
          end 

对此：

     ,MIN(case 
           when SN_TYPE = 'Re-Activattion' 
           then Created_Date 
           else null 
          end