Search for lost data

A simplified description of a workflow in one of our supported application is:

AnotherServer is sending data through a middleware to MyAppServer. We store the received data in a MS-SQL database an process the data.

Now there is a problem in the AnotherServer system and they did not know, which data they have send to MyAppServer. So they send us a list of data IDs from that day and asked us to check in our application, which data was not received or received but not processed.

The list contains 600 IDs and it would take ~ 1 minute to check a single ID. So this would be work for ~10 hours. And not one single hour would be fun. After a short discussion we decided to do the data analytics directly in the MyApp database.

List of values

First problem is, that they send us an excel file containing the data IDs and we have to transform this in a way, so we can use this IDs in a SQL statement:

SELECT TempTable.Field1
FROM ( VALUES
(10),
(11),
(12)
      ) AS TempTable (Field1)

Connect list to table

LEFT OUTER JOIN [MyAppSchema].[ReceivedDataTable]
ON TempTable.Field1 = ReceivedDataTable.Data_ID

GROUP BY Field1, Data_ID, is_Processed
ORDER BY is_Processed DESC

Filter processed data

WHERE is_Processed != 1 
   OR is_Processed IS NULL