Groups | Search | Server Info | Keyboard shortcuts | Login | Register

Groups > comp.databases.ms-access > #1165

Re: Determining Latest "First" Occurrance In Discontiguous Stream?

Show all headers | View raw

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:
> 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?

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Find similar

Thread

Determining Latest "First" Occurrance In Discontiguous Stream? "(PeteCresswell)" <x@y.Invalid> - 2011-05-02 10:22 -0400
  Re: Determining Latest "First" Occurrance In Discontiguous Stream? John Spencer <JSPENCER@Hilltop.umbc> - 2011-05-02 12:13 -0400

csiph-web