调整/编辑SQL查询帮助
问题描述:
我有下面的查询计算从第一个请求的日期到第一个完成日期已经过了多少时间。 但是在下面的例子中,有一个在2017-02-06创建的2017-02-02(backdated)的请求日期。调整/编辑SQL查询帮助
我想编辑的查询来选择所要求的日期和计算多少时间过去了,直到从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;
答
我想编辑的查询来选择所要求的日期和计算 多少时间过去了,直到从第一个创建日期完成
更改此:
,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
但我不想从创建的日期算起。我想从第一个创建日期的请求日期算起。有没有办法做到这一点 – Danielle
是的,有一个子查询获得最高1 RequestedDate,ORDER BY CreatedDate。 –
你能证明吗,不知道你的意思。 – Danielle