Got a table "tblTrancheHeldStatus":

TrancheID
StatusDate
StatusTimeStamp

Contains one record for each day a Tranche was held.

If we own a Tranche on a date, a record gets added to the table.

If a Tranche was not owned on a date, there is no record in the
table.

On the date a Tranche is sole completely, there is no record in
the table.

Tranches get purchased, sold completely at some later date, and
then re-purchased at a still later date multiple times.

Based on the table, I need to determine the most recent date upon
which a Tranche was re-purchased.

e.g.
01/01 = initial buy (record added to table)
01/02-01/20 = held on all those days
(record added to table for each day)
01/21 = sold completely (no record in table)

02/02 = re-purchased (record added to table)
02/03-03/01 = held on all those days
(record added to table for each day)
03/02 = sold completely (no record in table)

04/29 = re-purchased (record added to table)
04/30-present = held
(record added to table for each day)


Somehow, I need to come up with 4/29 without bringing the user's
PC to it's knees.

I could add a "PurchasedNotHeld" column to the table and set it
to "True" on the dates a Tranche is purchased when there is no
balance for it - and then just query for the most recent record
where it is set.

But that would add more code to the app and also complicate both
initial population and re-creation of the table.


Can anybody suggest something based on the table as it currently
is?
--
PeteCresswell

Step One: Identify all the Purchase Dates for each tranche
SELECT A.TrancheID, A.StatusDate
FROM tblTrancheHeldStatus as A LEFT JOIN tblTrancheHeldStatus as B
ON A.TrancheID = B.TrancheID
AND A.StatusDate = B.StatusDate - 1
WHERE B.TrancheID is Null

Step 2: Now get the maximum purchase date for each TrancheID
SELECT TrancheID, Max(StatusDate) as LatestPurchaseDate
FROM qPurchaseDate
GROUP BY TrancheID

If that works you can probably combine that into one query
SELECT TrancheID, Max(StatusDate) as LatestPurchaseDate
FROM (
SELECT A.TrancheID, A.StatusDate, A.Status
FROM tblTrancheHeldStatus as A LEFT JOIN tblTrancheHeldStatus as B
ON A.TrancheID = B.TrancheID
AND A.StatusDate = B.StatusDate - 1
WHERE B.TrancheID is Null) as Results
GROUP BY TranceID


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

On 5/2/2011 10:22 AM, (PeteCresswell) wrote: