SQL Server find duplicates with selection

Question

There is such a table in our database (sorry for formatting):

Id EventLogId EventLogDate BatchName EventType UserName RoleName Details VerificationResult BatchId TaskId Workstation
4054618 1135432394 2020-02-28 17: 48: 29.000 CRB2020280217150603_1 Comleted task ipugacheva2 Senior DEC operator NULL SAVE_AND_FORWARD 11741417 4736352 SRVTE298
4054617 1135432379 2020-02-28 17: 48: 28.000 CRB2020280217225632_1 Received task oryazepova CWP operator advanced Receiving a task by a user NULL 11741432 4736382 SRVTE297
4054616 1135432360 2020-02-28 17: 48: 26.000 CRB2020280216560813_1 Comleted task oryazepova CVP operator extended NULL SAVE_AND_FORWARD 11741421 4736355 SRVTE297
4054615 1135432308 2020-02-28 17: 48: 11.000 CRB2020280217175055_1 Canceled task ymikheeva2 CVP operator NULL NULL 11741428 4736362 SRVTE296
4054614 1135432246 2020-02-28 17: 47: 58.000 CRB2020280217082625_1 Received task eluzina DPC Operator Receiving a job by a user NULL 11741429 4736378 SRVTE296
4054613 1135432027 2020-02-28 17: 47: 24.000 CRB2020280216230826_4 Received task oyukhimets Senior CVP operator Receiving a job by a user NULL 11741430 4736377 SRVTE295
4054612 1135432013 2020-02-28 17: 47: 21.000 CRB2020280216554384_2 Comleted task oyukhimets Senior DPC operator NULL SAVE_AND_FORWARD 11741411 4736329 SRVTE295

BatchName numbers have three EventType events: Received, Canceled, Comleted (yes, the letter is missing here). I need to understand if any numbers had duplicate assignments (Received task).

Examples - the correct situation:

EventLogDate BatchName EventType UserName
2020-02-28 07: 56: 38.000 WPS2020260218521199_1 Received task sshmeleva2
2020-02-28 07: 57: 02.000 WPS2020260218521199_1 Canceled task sshmeleva2
2020-02-28 09: 48: 45.000 WPS2020260218521199_1 Received task yeremina
2020-02-28 09: 50: 11.000 WPS2020260218521199_1 Comleted task yeremina

The correct situation:

EventLogDate BatchName EventType UserName
2020-02-28 12: 55: 27.000 EUR2020280212384237_1 Received task oguryleva
2020-02-28 12: 58: 01.000 EUR2020280212384237_1 Comleted task oguryleva
2020-02-28 13: 01: 49.000 EUR2020280212414737_1 Received task ivolkova2
2020-02-28 13: 03: 28.000 EUR2020280212414737_1 Canceled task ivolkova2
2020-02-28 13: 03: 30.000 EUR2020280212414737_1 Received task egureva
2020-02-28 13: 06: 52.000 EUR2020280212414737_1 Comleted task egureva

Incorrect situation:

EventLogDate BatchName EventType UserName
2020-02-28 17: 23: 17.000 CRB2020280216535440_1 Received task ivolkova2
2020-02-28 17: 24: 52.000 CRB2020280216535440_1 Received task adasaeva2
2020-02-28 17: 26: 33.000 CRB2020280216535440_1 Comleted task adasaeva2

In this case, we received Received task twice, which indicates some kind of error in the program.

At the moment, I am searching for such BatchName with the following script:

select * 
from 
    (select pe.BatchName, count (*) as PP1 
     from ProcessLog pe
     where 1 = 1
       and pe.EventType = 'Received task'
       and pe.EventLogDate> = DATEADD (HOUR, -24, GETDATE ())
     group by pe.BatchName
     having count (pe.BatchName) > 1) p1,
    (select pe.BatchName, count (*) as PP2 
     from ProcessLog pe
     where 1 = 1
       and (pe.EventType = 'Comleted task' or pe.EventType = 'Canceled task')
       and pe.EventLogDate> = DATEADD (HOUR, -24, GETDATE ())
     group by pe.BatchName
     having count (pe.BatchName) > 1) p2
where 
    p1.BatchName = p2.BatchName
    and abs (pp1 - pp2) > 1

But it looks rather disgusting and not a fact that in all cases it will work correctly.

How can I make the script more elegant and concise (or redo) so that it produces the required?

Thanks.

Answer 1

Thanks to all. I forgot to tell you how I did it. Created an additional table and stored procedure with the following contents:

DECLARE @BatchName nvarchar(max)
DECLARE BATCH_CURSOR CURSOR
  LOCAL STATIC READ_ONLY FORWARD_ONLY  
FOR 
SELECT DISTINCT BatchName 
FROM ProcessLog
WHERE EventLogDate >= DATEADD(MINUTE, -10, GETDATE())

OPEN BATCH_CURSOR
FETCH NEXT FROM BATCH_CURSOR INTO @BatchName

WHILE @@FETCH_STATUS = 0
BEGIN 
    with eventdup as (
            select pe.eventlogdate, pe.BatchName, pe.EventType,
            LEAD(pe.EventType) OVER (ORDER BY pe.BatchName, pe.EventLogDate) nexxt
            from ProcessLog pe
            where 1=1
            and pe.BatchName = @BatchName
    )
    insert into dbo.DuplicateBatch
    select *
    from eventdup
    where EventType = nexxt
    and EventType like '%eceive%'
    order by BatchName

    FETCH NEXT FROM BATCH_CURSOR INTO @BatchName
END
--select eventlogdate, BatchName  from dbo.DuplicateBatch
CLOSE BATCH_CURSOR
DEALLOCATE BATCH_CURSOR

Answer 2

If you are just looking for duplicate assignments per batch number, then you could do:

select BatchName,EventType,Count(*) as cnt
from Processlog
Group by Batchname,EventType
having count(*)>1

If you want all the fields in here, you can do that by adding a join here. Hope this helps.