Results 1 to 2 of 2

Thread: Date Gap Audit

  1. #1
    Join Date
    Nov 2004

    Unanswered: Date Gap Audit

    I am trying to write a query that will identify items that have a gap in the dates. IE, for one acct, actions take place on the account that cause a start and end date. The start date always defaults to 0001-01-01 on the initial record, and ends with 9999-12-31. So a record with no changes will have 0001-01-01 and 9999-12-31 as the start and end date. But if that record had changes it may have:

    0001-01-01 2004-10-29
    2004-10-31 9999-12-31

    I want to identify more than a one day lapse period, ie. like 10-29-2004 to 10-31-2004. I wrote out the following query in oracle and it works, but not in db2. I am not really familar with db2.

    select acct
    from dtest
    group by acct
    having datediff(day, min(edate), max(sdate)) <>
    (select ISNULL(sum(datediff(day, sdate, edate))+count(*)+1, 1)
    from dtest d3
    where d3.acct = dtest.acct and d3.sdate <> '0001-01-01' and d3.edate <> '9999-12-31')
    and count(*) > 1

    I have since gone to the days function in db2 but can't quite figure out how this should work. I basically need to sum the begin and end dates and compare them to the sum of (0001-01-01)-(9999-12-31). Any ideas?

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    Would this work for you?
    select next.acct, prev.sdate as prev_start, prev.edate as prev_end, next.sdate as next_start, next.edate as next_end
    from dtest next, dtest prev
    where next.acct=prev.acct
    and prev.edate = (select max(tc.edate) from dtest tc where tc.edate < next.sdate and tc.acct = next.acct)
    and next.sdate - prev.edate > 1
    order by 1,2;

Posting Permissions

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