Find how many ticket removed, added and existing with SQL query

Cvin 21 Reputation points
2020-12-17T08:07:03.957+00:00

I have table with similar case number for different products. Hence I want to find the the case number has been added, removed and existing according to the date. I have a data something like below
49092-edit.png

So the code should take data from 2020-12-02 compare with data from 2020-12-11 and then it needs to compare data from 2020-12-11 with data from 2020-12-14
You may come up with entire new solution based on my desire output it will be very much helpful to me

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,655 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 111.8K Reputation points
    2020-12-17T09:03:26.37+00:00

    Check a possibility:

    declare @table table ([date] date, CapVersion varchar(max), CaseNumber int )
    
    insert @table values
    ( '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', 1002 ),
    ( '2020-12-14', 'CAP C', 1003 ),
    ( '2020-12-14', 'CAP C', 1005 )
    
    select * 
    from @table
    order by [date], CaseNumber
    
    ---
    
    ;
    with E as
    (
        select [date], lag([date]) over (order by [date]) as pd
        from @table
        group by [date]
    )
    select [date], 
        (select top(1) CapVersion from @table where [date] = E.[date]) as CapVersion,
        (select count(*) from
            (
                select CaseNumber from @table where [date] = E.[date]
                except
                select CaseNumber from @table where [date] = E.pd or E.pd is null
            ) d 
        ) as CaseNumberAdded,
        (select count(*) from
            (
                select CaseNumber from @table where [date] = E.pd
                except
                select CaseNumber from @table where [date] = E.[date]
            ) d 
        ) as CaseNumberRemoved,
        (select count(*) from
            (
                select CaseNumber from @table where [date] = E.[date]
                intersect
                select CaseNumber from @table where [date] = E.pd or E.pd is null
            ) d 
        ) as CaseNumberExisting 
    from E 
    order by [date]
    
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful