Date Gap Audit

weturner_2001 · #1 (**permalink**) 11-29-04, 14:39

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?

n_i · #2 (**permalink**) 11-29-04, 20:22

Would this work for you?

Code:

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech