Complex Record Selection

[LisaInAz]

I am totally at a lost on even what to do here. Below is a sample of over
13,000 records that need to be analyzed and reported. The goal is to analyze
each ID1 group, determine if any of the records in the group meet the "gap"
criteria and give numbers and %.

The criteria for each group is to determine if the next "START_DT" in the
group is equal to or greater than the "Gap" field.

In an essence I need to loop through the group starting with the first
record/row "hold" the gap date check the next record if the start date GE the
gap report out each record. If not Hold the second records "gap" date check
the next record and so one.

First group I would report both record only as the second record START_DT is
GE than the gap date of the first record

Second group I would report the third record only as the second record
START_DT is not GE than the gap date of the first record, but the third
record is GE to the gap date of the second record.

Third group I would report the forth record only as it is the only record to
meet the criteria


ID1 START_DT EndDt TYPE RSN Gap
2241 1/30/2006 12/31/2007 BW RE 1/31/2008
2241 4/8/2008 6/30/2008 BW FS 7/31/2008

5830 10/18/2006 9/30/2007 BW RE 10/31/2007
5830 10/16/2007 3/31/2008 BW RE 5/1/2008
5830 5/6/2008 6/30/2008 BW FS 7/31/2008

9415644 5/30/2007 7/31/2007 JB TR 8/31/2007
9415644 8/1/2007 8/12/2007 JB TR 9/12/2007
9415644 8/13/2007 8/31/2007 JB EC 10/1/2007
9415644 10/9/2007 11/11/2007 BW GE 12/12/2007
9415644 11/12/2007 1/31/2008 TC RE 3/2/2008
9415644 2/1/2008 2/24/2008 JB EC 3/26/2008
9415644 2/25/2008 3/12/2008 EA EC 4/12/2008
9415644 3/1/2008 3/31/2008 JB EC 5/1/2008
9415644 3/13/2008 3/31/2008 JB EC 5/1/2008
9415644 4/1/2008 9/12/2008 TC EI 10/13/2008

I may be going at this all wrong so any suggestion is greatly appreciated.

 

[John Spencer]

First of all you are not being consistent in what you want to report. IF you
were consistent, you would only return one record from the first group.

What I think you want is to return records where the Start_Dt of the current
record is greater than or equal to the GAP date of the previous record. The
previous record being defined as one with the SAME ID1 and the most recent
prior date.

You Can try the following UNTESTED SQL.

First construct a query like the following and save it as qDates

SELECT Ya.ID1, Yb.Start_Dt as CurrDate, Max(Ya.Start_DT) as Prior
FROM YourTable as Ya INNER JOIN YourTable as Yb
ON Ya.ID1 = Yb.ID1 AND
Ya.Start_Dt <=Yb.Start_Dt
Group By Ya.ID1, Yb.Start_dt

Now you can use that in another query.

SELECT YC.*
FROM (YourTable as Yc INNER JOIN qDates
On Yc.ID1 = QDates.ID1
and Yc.Start_Dt = qDates.CurrDate)
INNER JOIN YourTable as Yd
ON Yd.ID1 = qDates.ID1
AND Yd.Start_Dt = qDates.Prior
WHERE YC.StartDate >= YD.Gap

If this does not work, then repost.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County