I am currently tracking when we receive new files from our third party vendors. I now need to provide a report of when a file does not arrive on time. For example, if a file is scheduled to arrive on the 25th of every month but does not arrive. I am struggling with the best approach to do this. The following are the tables I currently use to track when files are loaded. Can someone recommend additional columns or tables that might help me accomplish the goal of keeping track if we have received a file on time or not.
SELECT jh.JobID, jh.JobTypeID, jt.Name AS JobTypeName, jh.FileID, f.Name AS [File Name], s.SourceID, s.Name AS [Source Name], jh.Status, DATEADD(hh, - 5, jh.Timestamp) AS [CST Timestamp]
FROM dbo.JobsHistory AS jh LEFT OUTER JOIN dbo.JobTypes AS jt ON jh.JobTypeID = jt.JobTypeID LEFT OUTER JOIN dbo.Files AS f ON f.FileID = jh.FileID LEFT OUTER JOIN dbo.Sources AS s ON f.SourceID = s.SourceID WHERE f.Name = 'WBGA_MA_IP_dlycenus_ver2_20211110.xlsx' and jh.JobTypeID = 1 SELECT [SourceID], [Name] FROM [Wbdm-API].[dbo].[Sources] WHERE [SourceID] = 2 SELECT [JobID], [JobTypeID], [FileID], [Status], [Timestamp] FROM [Wbdm-API].[dbo].[JobsHistory] WHERE [JobID] = 63089 and [FileID] = 52965 SELECT [FileID], [Name], [SourceID], [OriginalTimeStamp] FROM [Wbdm-API].[dbo].[Files] WHERE [FileID] = 52965