我在不同产品的表上都有类似的案例编号。因此,我想查找根据日期已添加,删除和存在的案例编号。我有如下数据
Date CapVersion CaseNumber
----------------------------------
2020-12-02 CAP A 1002
2020-12-02 CAP A 1003
2020-12-02 CAP A 1004
2020-12-11 CAP B 1002
2020-12-11 CAP B 1003
2020-12-14 CAP C 1003
2020-12-14 CAP C 1004
2020-12-14 CAP C 1005
2020-12-15 CAP D 1005
2020-12-15 CAP D 1007
我想要的输出
Date CapVersion CaseNumberAdded CaseNumberRemoved CaseNumberExisting
--------------------------------------------------------------------------
2020-12-02 CAP A 0 0 3
2020-12-11 CAP B 0 1 2
2020-12-14 CAP C 2 1 1
2020-12-14 CAP D 1 2 1
我现在得到的输出:
Date CapVersion CaseNumberAdded CaseNumberRemoved CaseNumberExisting
--------------------------------------------------------------------------
2020-12-02 CAP A 0 0 3
2020-12-11 CAP B 0 1 2
2020-12-14 CAP C 1 1 2
2020-12-15 CAP D 2 3 0
因此,代码应将2020-12-02中的数据与2020-12-11中的数据进行比较,然后需要将2020-12-11中的数据与2020-12-14中的数据进行比较,但是我当前的代码正在做什么将2020-12-02的数据与2020-12-11的数据进行比较,然后再次将2020-12-02与2020-12-14进行比较。
我将在下面提供一个小提琴和一个代码。
打开小提琴链接,您将完全访问我的代码https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=426aaaf1b851462d975909e56a08d4be
--this is the my error code
WITH BaseDate AS
(
SELECT MIN(DataTable.Date) BaseDate
FROM DataTable
),
Dates AS
(
SELECT DISTINCT DataTable.Date, DataTable.CapVersion
FROM DataTable
),
BaseData AS
(
SELECT Dates.Date, DataTable.CapVersion, DataTable.CaseNumber
FROM DataTable
CROSS JOIN Dates
JOIN BaseDate ON DataTable.Date = BaseDate.BaseDate
),
Detailed AS
(
SELECT
COALESCE(DataTable.Date, BaseData.Date) Date,
COALESCE(DataTable.CaseNumber, BaseData.CaseNumber) CaseNumber,
CASE
WHEN DataTable.CaseNumber IS NOT NULL AND BaseData.CaseNumber IS NULL
THEN 1
ELSE 0
END CaseNumberAdded,
CASE
WHEN DataTable.CaseNumber IS NULL AND BaseData.CaseNumber IS NOT NULL
THEN 1
ELSE 0
END CaseNumberRemoved,
CASE
WHEN DataTable.CaseNumber IS NOT NULL AND BaseData.CaseNumber IS NOT NULL
THEN 1
ELSE 0
END CaseNumberExisting
FROM
BaseData
FULL JOIN
DataTable ON DataTable.CaseNumber = BaseData.CaseNumber
AND DataTable.Date = BaseData.Date
)
SELECT
Detailed.Date,
Dates.CapVersion,
SUM(Detailed.CaseNumberAdded) CaseNumberAdded,
SUM(Detailed.CaseNumberRemoved) CaseNumberRemoved,
SUM(Detailed.CaseNumberExisiting) CaseNumberExisting
FROM
Detailed
JOIN
Dates ON Detailed.Date = Dates.Date
GROUP BY Detailed.Date, Dates.CapVersion;
我认为应该这样做:
;WITH DataTableBase AS
(
SELECT Date, CapVersion, ROW_NUMBER() OVER (ORDER BY Date) AS seq
FROM DataTable
GROUP BY Date, CapVersion
), DataTableSections AS
(
SELECT Date, CapVersion, CaseNumber, DENSE_RANK() OVER (ORDER BY Date) AS seq
FROM DataTable
), DataTableCombined AS
(
SELECT dt1.seq, dt2.seq AS seqBefore
, dt1.CaseNumber, dt2.CaseNumber AS CaseNumberBefore
, dt1.Date, dt1.CapVersion, dt2.Date AS DateBefore
, dt2.CapVersion AS CapVersionBefore
FROM DataTableSections AS dt1
FULL OUTER JOIN DataTableSections AS dt2
ON dt1.seq = dt2.seq + 1 AND dt1.CaseNumber = dt2.CaseNumber
)
SELECT dt.seq
, dt.Date
, dt.CapVersion
, COUNT(CASE WHEN dt.seq != 1 AND CaseNumberBefore IS NULL THEN 1 END) AS CaseNumberAdded
, COUNT(CASE WHEN CaseNumber IS NULL THEN 1 END) AS CaseNumberRemoved
, COUNT(CASE WHEN (dt.seq = 1) OR (CaseNumber IS NOT NULL AND CaseNumberBefore IS NOT NULL) THEN 1 END) AS CaseNumberExisiting
FROM DataTableBase AS dt
INNER JOIN DataTableCombined AS dtc ON dt.seq = COALESCE(dtc.seq, dtc.seqBefore + 1)
GROUP BY dt.seq, dt.Date, dt.CapVersion
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句