Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Reflect days where data not available

    Hello Experts !


    i have db there many record exists of different item names. I need to query data suppose i have item name "Scott" i need that sql query reflect all the dates where the data is not exists my start date would be 1980?


    Thank
    farrukh

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Farrukh,

    A little more information on your table structure would be helpful.
    Is the date field just a year or a whole date day,month,year?
    Are all dates in the table, it would take VBA to determine missing dates for "scott" if all dates were not in the table.
    Perhaps you could provide some sample data and your table definition.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sir,

    The date are based on daily ranges( i need the missing dates of item name " Scott " or it is possible that i find all the item names missing dates at ones?

    No all the dates are not in the table.

    I have attached the workbook.


    Thanks
    Attached Files Attached Files
    Last edited by farrukh; 2012-10-01 at 10:12. Reason: No all the dates are not in the table.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Farrukh,

    I really don't know what to tell you on this one. Since you want to start at 1980 (30+ years of data?).
    I know of no way to craft SQL that will find data that doesn't exist.

    Two possible approaches I can think of are:

    Method ONE


    • Create a table with all dates from 1/1/1980 through the present with a second field for ITEM_NAME.
    • Update this tables ITEM_NAME field from the existing table where Scott has data
    • Report all dates which do not show Scott.

    Method TWO

    • Write a VBA routine to loop through all dates from 1/1/1980 to present and do finds on the DB for each date and ITEM_NAME = "Scott"
    • Store NOT matched dates in an array.
    • Write report from array.

    Maybe one of the Lounge DB guy with deeper experience can come up with a better solution.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    It takes quite a bit of programming to do it. I had to do something a bit similar once and it was quite a bit of work, if you choose not to add database records more than the ones you must absolutely have.

  6. #6
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Is the programming that difficult? I would have thought two loops, one to iterate through each unit of data eg Scott, and one for each day since 1980, each seeking whether data .

    Maybe a huge number of iterations taking maybe a long time but not complex.

Posting Permissions

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