Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1

    Unanswered: Verify file load by missing dates

    I have a couple of scheduled SSIS Packages that loads run daily. I would like to run some reports weekly that would let me know everything loaded correctly. One of those is a date check. I found something on a site that indicated I needed to build a temp table with the dates I am looking for, then compare that to the days I have loaded. It works but is slow. I can sit and watch it adding records to the temp table. Here's what I have, I was wondering if there is a better way?

    This example is for the weekly file load check.

    Code:
    IF OBJECT_ID('tempdb.dbo.#CalendarCycles') IS NOT NULL
        DROP TABLE #CalendarCycles; 
    GO 
    
    CREATE TABLE #CalendarCycles 
    (
      tmpDate DATE,
      PRIMARY KEY (tmpDate)
    )
    GO
    DECLARE @Offset  INT = 7
    DECLARE	@StartDate DATE
    DECLARE @NextDate DATE
    	
    SET @StartDate = (SELECT MIN(MailDate) FROM IMB_TBO_Data.dbo.TBO_Jobs_Table)
    
    SET @NextDate =  @StartDate
    
    WHILE (@NextDate <= GETDATE())
    BEGIN
      INSERT INTO #CalendarCycles 
      (tmpDate)
       VALUES (@NextDate)  
      SELECT @NextDate = DATEADD(d, @Offset, @NextDate)
    END
    
    SELECT * FROM #CalendarCycles 
    WHERE tmpDate Not IN 
    	(SELECT MailDate FROM IMB_TBO_Data.dbo.TBO_Jobs_Table)

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you have SQL 2014 (maybe SQL 2012, but I am not sure) available to you?

    Code:
    with FileLoadDates (thisfiledate, nextfiledate)
    as
    (SELECT MailDate, lead (maildate, 1) over (order by maildate)
     FROM IMB_TBO_Data.dbo.TBO_Jobs_Table)
    
    select *
    from FileLoadDates
    where datediff (dd, thisfiledate, nextfiledate) > 1
    This will show you when the gaps happened. What you do after that us up to you.

  3. #3
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    with FileLoadDates (thisfiledate, nextfiledate)
    as
    (SELECT MailDate, LEAD(maildate, 7) over (order by maildate)
    FROM IMB_TBO_Data.dbo.TBO_Jobs_Table)

    select *
    from FileLoadDates
    where datediff (dd, thisfiledate, nextfiledate) > 7
    Thanks for the answer, I kind of see where you are going with this but as it sits I get a error on the LEAD. LEAD is not a recognized built_in function name. I don't think its a version problem, because I looked up lead and it came up as valid for 2012.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •