I've got tblTrade which lists buys and sells for securities
(SecurityID, TradeDate, Amount, TradeType).

For any given date, we can determine whether a security is held
or not by summing the trades up to and including that date. If
the sum is zero, the security was not held. If the sum is > 0,
the security was held, and if the sum is < 0 we have an error.

There is also a tblPriceHistory, which contains the price of each
security for a given date.

In cases where a security's price is relatively static, we save
the user the effort of typing in the same price day-after-day by
rolling over prices at the beginning of each day. If the price
was 99 yesterday, we create a new tblPriceHistory for that
SecurityID where PriceDate=Today and PriceAmount=99.

If the user doesn't like a price, they change it.

So far, so good...

But there can be gaps in a security's holding status.

e.g. Security XYZ:
- Held 4.5 mil on 2/23

- Sold everything on 2/24

- Bought 1 mil on 3/28

Now we have a gap where XYZ was not held between 2/25 and 3/27.

Needless-to-say, there can be multiple gaps.... dozens, even.

Here's the zinger: The user does not want prices tb rolled over
into dates where the security was not held.

The brute force approach is a function:
"Security_IsHeld_AsOfDate()" called each time we are about to
roll over a price.

But there are that approach takes too long to execute: too many
securities, too many trades.


All I can come up with is a new table: tblSecurityHeldDates where
there is a record for each date a security was held (SecurityID,
HoldingDate).

Said table could be initially populated, and then maintained as
securities are bought and sold.

I'm guessing that doing table lookups to that table would be fast
enough to work.


But is there a more elegant solution? I'm thinking some kind of
join to a query that returns a list of SecurityID|Date based on a
running sum of holdings up to each date. Functionally the
abovementioned table on-the-fly. Way, way beyond my level of
SQL expertise... but it seems like the most database-like
solution.

??






--
PeteCresswell

Hi Pete

Not sure if this helps...

Why not include one more field...txtActivity = 0 or 1

Set the Criteria in your query to ( 1 ) this way any Inactive ( 0 )Trading
will not show up in the query.

Conversely, in your example:

If XYZ has sold all there security/stock/whatever you would then enter a new
record for 2/25, but have a zero in the activity field so that you could, if
needed do a search on any inactive traders and or periods of inactivity.


HTH
Mick

"(PeteCresswell)" <x@y.Invalid> wrote in
news:uf3up6tak15idsdqdhsodv1dn0ggt94if7 (AT) 4ax (DOT) com:

Is there a SQL statement that returns a resultset that could be used
to determine if the security is held? I'm thinking of a subquery
evaluated with EXISTS, so that your SQL statement that inserts the
new record each day would only run in the cases that EXISTS ([SQL
statement]) returns TRUE.

I would favor EXISTS here over totalling the positions because it
would not require any summarizing, which is more expensive than just
returning records.

Is there a table that holds records that represent the period the
security is held? If so, you'd filter that table on the dates in
question, and if any records are returned, insert the new record. If
no records are returned, you don't.

Does that make any sense?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/