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