Avatar of swaggrK
swaggrK
 asked on

Track new filesystem files in a SQL

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

Open in new window


results.xlsx

SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
ste5an

8/22/2022 - Mon
ste5an

Well, you need a table for the schedule. Then you can compare the arrived files with the expected files.
swaggrK

ASKER
@Ste5an...How do I go about creating a column and/or table to hold the dates? Some files we receive monthly and others we receive daily. I am not sure how to create a column  to hold these values.
swaggrK

ASKER
@Ste5an.I created a table that holds all dates for the next several years.export_dateDimTbl.xlsx

But, not sure how to connect this table.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
swaggrK

ASKER
ste5an

No. You need a calendar table (FileID, ScheduledDate), which simply contains the dates, when a file should arrive. This is the core table for a non-equi-join to get the files, which did not arrive.

This table can be materialized or calculated. In most scenarios you'll need a schedule table, where you store the schedule. Then you calculate the materialized calendar from it.
swaggrK

ASKER
@Ste5an...but how do I account for dates that are daily or weekly. In these cases, I do not know the physical date because it is random.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question