Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2008
    Posts
    3

    Unanswered: SQL 2005 return a list of missing rows by date?

    Hi,

    I have written a reporting application which has a SQL2005 backend. An import routine into SQL, written by a 3rd party, frequently fails. The main problems are missing rows in certain tables.

    I am going to write an SP that will accepts a from and to date. I then want to search for rows of type X between those dates that do not exist so we then know between a date range, we have no data for these XYZ days.

    I have this working by returning all rows between the dates into a dataset, sorted by date, and then running through the rows and testing if the next rows date is the next expected date. This works but I think is a very poor solution. This is all done on the client in C#.

    I want to learn and implement the most efficent way of doing this. My only solution in a SP was to make a temporary table of all dates between the date range for row type X and then do a right outer join against the data table, returning all rows which are missing.

    Something like this:

    SELECT twmd.date
    FROM #temp_table_with_all_dates ttwad
    RIGHT OUTER JOIN table_with_missing_date twmd
    ON ttwad.date = twmd.date
    WHERE twmd.date IS NULL

    Would this be a good, efficent solution, or should I just stick to my processing of a dataset in C#?

    Many thanks in advance,
    CB

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    A calendar table is the way to go, but make it a permanent table.
    A numbers table can also work for you to generate the date range
    e.g.
    Code:
    declare @fromdt datetime, @todt datetime
    select @fromdt='20070101', @todt='20080229'
    select dateadd(dd,number,@fromdt) dt
    from master..spt_values 
    where type='P' 
      and number<=datediff(dd,@fromdt,@todt)

  3. #3
    Join Date
    Feb 2008
    Posts
    3
    Many thanks, excellent.

  4. #4
    Join Date
    Feb 2008
    Posts
    3
    One more quick question, why is it limited to only returning a maximum of 2048 rows? I've tried casting number to int to get around it with no joy.

    Thanks,
    CB

  5. #5
    Join Date
    Jan 2008
    Location
    Billings, MT
    Posts
    14
    The query returns a max of 2048 rows because spt_values stores 2048 values of type='P'. That's 5+ years of data. Will your date range be longer than that?

Posting Permissions

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