Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Unanswered: Query to find Date Gaps

    Hello ,

    I have some data that needs has two dates Date Effective and Date Expiration with a Producer ID (PK). I need a query to search the Producer ID and find date gaps between these two Date Fields (date Eff), (Date Exp).

    Can someone guide me how to do this in a query? I have like 90,000 Records. Thanks

    ProducerId Year DateEff DateExp
    0000300063 2007 1/1/2007 12/31/2007
    0000300063 2008 1/1/2008 12/31/2008
    0000300063 2009 1/1/2009 12/31/2009
    0000300063 2010 1/1/2010 12/31/2010
    0000300063 2011 1/1/2011 12/31/2011
    0000300063 2012 1/1/2012 12/31/2012
    0000300063 2013 1/1/2013 12/31/2013
    0000300063 2014 1/1/2014 12/31/2014
    0000300063 2015 9/22/2015 12/31/2015
    0000300063 2016 1/1/2016 1/1/2200
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What do you mean by find date gaps?
    Im guessing find rows where the dateeff does not have a matchung row with a dateexp precisely one day earlier than thr dateff
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    4
    yes that is correct. any lapse between rows by primary key (producer id)

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OL
    So there are various steps
    the first one is to use a JOIN to join two tables together,the fact we are joining the same table doesn't matter
    Code:
    SELECT S.ID, S.ProdYear, S.DateEff, P.DateExp
    FROM mytable as S left JOIN mytable AS P ON S.ID = p.ID AND S.DateEff = dateadd("d", 1, P.DateExp);
    i have chosen to alias the table names to the first reference is the subsequent date, so I;ve called it S, the second table is the preceeeding row sow Ive called it P
    you will need to substitute your proper table name for mytable, and any columns
    A left join means return all rows from the table on the table on the left hand side of the join AND any rows that match from the table on the right hand side. if there are no rows that match on the right hand side, return NULL values for columns in the select.
    the expression dateadd("d", 1, P.DateExp) means add 1 one day to the value of p.DateEXP


    that gives:-
    Code:
    ID	ProdYear	DateEff	DateExp
    0000300063	2007	01-Jan-2007	
    0000300063	2008	01-Jan-2008	31-Dec-2007
    0000300063	2009	01-Jan-2009	31-Dec-2008
    0000300063	2010	01-Jan-2010	31-Dec-2009
    0000300063	2011	01-Jan-2011	31-Dec-2010
    0000300063	2012	01-Jan-2012	31-Dec-2011
    0000300063	2013	01-Jan-2013	31-Dec-2012
    0000300063	2014	01-Jan-2014	31-Dec-2013
    0000300063	2015	22-Sep-2015	
    0000300063	2016	01-Jan-2016	31-Dec-2015
    the next step is to eliminate rows that we have found a match
    Code:
    SELECT S.ID, S.ProdYear, S.DateEff, P.DateExp
    FROM mytable as S left JOIN mytable AS P ON S.ID = p.ID AND S.DateEff = dateadd("d", 1, P.DateExp)
    where isNULL(P.DateExp);
    we do that by only wantign rows with no preceeding exipiry date, becuase e used a left join if we cannot find a match in the table on the right hand side of the join (the P table) any rows returned will have NULL values in the P columns
    so requiring isNULL(P.DateExp) means only return rows where the P.DateEXP is NULL

    that gives
    Code:
    ID	ProdYear	DateEff	DateExp
    0000300063	2007	01-Jan-2007	
    0000300063	2015	22-Sep-2015
    but that still means you have the first row.....
    if you need to eliminate that row then the requery will need further refinement

    Joins are one of the key things to get to grips with as you devlop more complex queries, it will be woirth your while reading up on JOINS. Sadly the default SQL engine used in Access uses its own flavour to write joins as opposed to sticking to ANSI SQL meaning the precise syntax varies, but the concepts remaan the same
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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