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

• vikasjagadale8

  Say Hey Kid

  Points: 704

  More actions

  November 8, 2022 at 10:08 am

  #4109978

  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

  Output:

  

  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.

  • This topic was modified 2 years ago by  vikasjagadale8.

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  November 9, 2022 at 10:10 am

  #4110437

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  November 9, 2022 at 10:32 am

  #4110452

  WITH AnyProcessed
  AS
  (
      SELECT [Name], FileId, recordCount
          ,MAX(CASE WHEN processed = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY [Name]) AS processed
          ,[status], [Timestamp]
          ,ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [TimeStamp] DESC)  AS rn
      FROM dbo.Zuora_JobStatus_test
  )
  SELECT [Name], FileId, recordCount, processed, [status], [Timestamp]
  FROM AnyProcessed
  WHERE processed = 0
      AND rn = 1;