SQL - How to get rows for a unique object which are not processed even once for current date-CodePudding

I have a scenario, wherein I want to return the Zuora Object name from my log table of MSSQL 2016. It should only return in result if it is not processed even once for the current date.

For example, Account object out of 3 runs was not processed in the first, processed in the second and then not processed in the third run then the query should not return Account in the result as it is processed at least once.

Similarly, if another object like Subscription has not yet been processed in all 3 runs it should print it.

I tried the below query but it is not giving me the expected result.

    Create table dbo.Zuora_JobStatus_test (
      Id bigint identity(1,1) not null,
      [Name] varchar(100),
      FileId varchar(100),
      recordCount bigint,
      processed bit,
      [status] varchar(100),
      [Timestamp] datetime
      )
    
    INSERT INTO dbo.Zuora_JobStatus_test ([Name], FileId, recordCount, processed, [status], [Timestamp])
    VALUES ('Subscription','FS1',10, 0, 'completed','2022-11-08 13:05:00.000'),
      ('Account','FA1',1000, 0, 'completed','2022-11-08 13:50:00.000'),
    ('Subscription','FS2',15, 0, 'completed','2022-11-08 15:05:00.000'),
      ('Account','FA2',1003, 1, 'completed','2022-11-08 15:10:00.000'),
        ('Account','FA3',1004, 0, 'completed','2022-11-08 16:10:00.000')
    
-- Below query prints input data
    SELECT * FROM dbo.Zuora_JobStatus_test ORDER BY NAME ASC, timestamp desc  
    

    -- Below query along with the Subscription also prints the Account row, which is not required as it was processed once for the current date.
    
     SELECT fileId, name, status, recordCount,[timestamp],processed
      FROM
       (
       SELECT  fileId, name, status, recordCount,[timestamp],processed,rn 
       FROM 
       (
         SELECT fileId, name, status, recordCount, [timestamp], processed 
         , ROW_NUMBER() OVER (PARTITION BY Name ORDER BY [TimeStamp] DESC) rn
         FROM dbo.Zuora_JobStatus_test
         WHERE [status] = 'Completed' AND [Name]  in ('Account','Subscription')
       ) x
       WHERE x.rn = 1 
       )x2
       WHERE x2.processed = 0

Using the above query I am still seeing Account in output which should not be the case as it is processed = 1 once for the current date in the second run.

CodePudding user response：

This will do the trick. You just need to count/sum the number of successful processing by Object and date, then filter on that

SELECT 
    name,
    CAST([timestamp] AS date) AS ExecDate,
    SUM(CASE WHEN processed = 1 THEN 1 ELSE 0 END) as NumOfSuccess
FROM dbo.Zuora_JobStatus_test
WHERE
    [status] = 'Completed' 
    AND [Name]  in ('Account','Subscription')
GROUP BY 
    name,
    CAST([timestamp] AS date)
HAVING SUM(CASE WHEN processed = 1 THEN 1 ELSE 0 END) = 0

CodePudding user response：

use not exists() to check for exitance of the processed line

select *
from
(
       select *,
              rn = row_number() over (partition by [Name] order by TimeStamp desc, id desc) 
       from   dbo.Zuora_JobStatus_test t
       where  [status] = 'Completed' 
       and    [Name]  in ('Account','Subscription')
       and    not exists
              (
                 select *
                 from    dbo.Zuora_JobStatus_test x
                 where   x.[Name] = t.[Name]
                 and     x.processed = 1
              )
) d
where d.rn  = 1