Re: Difference within a field between records
Date: Mon, 21 Jan 2002 07:41:39 -0500
> CREATE TABLE Foobar
> (week_nbr INTEGER NOT NULL,
> tank_id INTEGER NOT NULL,
> mass INTEGER NOT NULL,
> PRIMARY KEY (week_nbr, tank_id));
> SELECT F1.week_nbr, F1.tank_id, F1.mass,
> (SELECT F2.mass
> FROM Foobar AS F2
> WHERE F1.tank_id = F2.tank_id
> AND F1.week_nbr = F2.week_nbr + 1) - F1.mass
> FROM Foobar AS F1;
> I am making a lot of assumptions about NULLs, keys, constraints, week
> numbering, etc. because you did not bother to post DDL or clear specs.
Thanks for the reply.
WRT the assumptions etc.
This is not a database (yet), but a simplified description of a fairly major real world issue. I have a spreadsheet for this right now. The "data" tab goes to column GR. There are 16 further tabs with calculated values. In the process industries we are often required to reconcile and allocate items based on more than one measure. In this case, I must get a physical measurement of mass in a tank (itself a calculation based on the depth of material in the tank) This mass is compared to last weeks mass to determine an increase in inventory. This is combined with sales to determine total production. I use this to check our density transmitters, flowmeters, level transmitters, PLC totalizers etc. I put the example in the form shown to make it as simple as possible. The headings were just a guess at what might be useable.
Further to the assumptions. At this time I am required to use Access.
JE Received on Mon Jan 21 2002 - 13:41:39 CET