Direct access to records via timestamp

[news.inspire.net.nz]

Let us consider the example of a stock system. Suppose that in each
transaction, cumulative totals of sales are kept. Now to find the
cumulative sales at any time a composite index of stockcode and transaction
timestamp could be stored. To access the index a composite key of stock
code and timestamp (for the time at which we want to know the stock level)
could be used to access the index. In all likelyhood there was no
transaction at exactly that time. So we need to back up the index to the
previous record. If there is no previous record, or the previous record has
a different stock code then we know that the cumulative sales at that time
is zero.

How do I do this with MS Sql server?

Looking forward to your replies.

Tony

[chris nolan]

Are you using MSSQL7 or 2000?

"news.inspire.net.nz" <to***@maxnet.co.nz> wrote in message news:<fS*****************@news02.tsnz.net>...

Let us consider the example of a stock system. Suppose that in each
transaction, cumulative totals of sales are kept. Now to find the
cumulative sales at any time a composite index of stockcode and transaction
timestamp could be stored. To access the index a composite key of stock
code and timestamp (for the time at which we want to know the stock level)
could be used to access the index. In all likelyhood there was no
transaction at exactly that time. So we need to back up the index to the
previous record. If there is no previous record, or the previous record has
a different stock code then we know that the cumulative sales at that time
is zero.

How do I do this with MS Sql server?

Looking forward to your replies.

Tony

[Simon Hayes]

"news.inspire.net.nz" <to***@maxnet.co.nz> wrote in message
news:fS*****************@news02.tsnz.net...

Let us consider the example of a stock system. Suppose that in each
transaction, cumulative totals of sales are kept. Now to find the
cumulative sales at any time a composite index of stockcode and transaction timestamp could be stored. To access the index a composite key of stock
code and timestamp (for the time at which we want to know the stock level)
could be used to access the index. In all likelyhood there was no
transaction at exactly that time. So we need to back up the index to the
previous record. If there is no previous record, or the previous record has a different stock code then we know that the cumulative sales at that time
is zero.

How do I do this with MS Sql server?

Looking forward to your replies.

Tony

I don't really follow your narrative - you may want to consider posting
sample DDL to illustrate your issue. Without more information I don't know
exactly what you're looking for, but the easiest way to find cumulative
sales/shipments for a given period of time would probably be to get the sum
of all sales/shipments quantities between two points in time:

SELECT SUM(QuantityShipped)
FROM dbo.Orders
WHERE PartNumber = 1234
AND ShippedDate BETWEEN '20040101' AND '20040331'

This is an artificially simple example of course, and may not be what you're
looking for at all - I'm just guessing.

Simon