The point of the narrative is that this way there is no reason to sum the
data, only to retrieve the required records and do a subtraction. I will
illustrate with an example. Let us assume only one product, product number
1:
transid ProductId TimeStamp Qty CumQty
1 1 2004-01-01,10:18:43 6 6
2 1 2004-01-01,15:12:04 4 10
3 1 2004-01-02,08:17:22 3 13
4 1 2004-01-04,11:18:08 7 20
5 1 2004-01-12:13:12:22 7 27
To find the sales between 2004-01-02,01:00:00 and 2004-01-03,01:00:00
Sales at 2004-01-03,01:00:00 stood at 13 (transid 3)
Sales at 2004-01-02,01:00:00 stood at 10 (transid 2)
Sales over period = 13-10 = 3
Imagine how much faster this is instead of adding up all the records for a
year!
Hope this helps
[quoted text, click to view] "Simon Hayes" <sql@hayes.ch> wrote in message
news:407d4b7d$1_2@news.bluewin.ch...
>
> "news.inspire.net.nz" <tonyw@maxnet.co.nz> wrote in message
> news:fSYec.113$cY5.18360@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
>
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
