使用SQL查询查找已删除,已添加和已存在的故障单数量

哈文·钱德兰

我在不同产品的表上都有类似的案例编号。因此,我想查找根据日期已添加,删除和存在的案例编号。我有如下数据

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;
乔治·贝索斯(Giorgos Betsos)

我认为应该这样做:

;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

DB小提琴演示

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

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

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

从“已添加” /“已删除”列中仅查找活动项

来自分类Dev

如何查找和恢复已删除的项目?

来自分类Dev

如何使用git查找已删除的行?

来自分类Dev

当前已删除记录的数量

来自分类Dev

Postgres查询以查找联结表中是否已存在组合

来自分类Dev

使用已运行的Sql查询填充TextBox

来自分类Dev

在已存在的列上添加“更新时/删除时”选项

来自分类Dev

查找并删除已打开但已删除的大文件

来自分类Dev

SQL查询来查找已购买最大价格的名称?

来自分类Dev

SQL查询来查找已购买最大价格的名称?

来自分类Dev

在Sql Server 2008中查找已删除的记录

来自分类Dev

Docker恢复已删除容器的数量?

来自分类Dev

如何获取已删除记录的数量?

来自分类Dev

比较两个列表并对已添加和已删除条目进行排序

来自分类Dev

加速SQL查询,已奏效

来自分类Dev

如何使用JDBC查找已删除记录的主键?

来自分类Dev

如何使用JDBC查找已删除记录的主键?

来自分类Dev

使用SQL Server 2016时态表查询已删除记录的最佳方法是什么?

来自分类Dev

如何恢复已删除的存储库和已删除的分支

来自分类Dev

如何恢复已删除的存储库和已删除的分支

来自分类Dev

使用grep从字典中删除词根已存在的词

来自分类Dev

如何使用PHP在已存在的JSON中添加嵌套元素

来自分类Dev

使用中的已删除文件

来自分类Dev

子查询以查找已下订单的客户

来自分类Dev

在JTextPane中查找已删除的文本

来自分类Dev

查找已删除的唯一对的索引

来自分类Dev

从合并复制中查找已删除的行

来自分类Dev

在JTextPane中查找已删除的文本

来自分类Dev

按日期查找已删除的提交

Related 相关文章

热门标签

归档