Need a query to tell me how much of something was used

Well, I posted a w or so about a problem I had and someone was kind
enough to respond with help. I'm on my last hurdle for a report I'm doing.
I hope someone can help me out on this as well. First of all, before I go
any further, I hope someone can recommend a good book to help me learn how
to do problems such as the one below. I'm fairly compentent in T-SQL, but
problems such as the one below and the one I posted last w are leaving me
a bit stumped.
The problem: I need to find how much of an item (we'll say a liquid) was
actually used within a given time span, say a timespan of 4 hours. All
values are stored in a database where a row is inserted every second or so.
We'll say a second to keep this simple. During any given 4 hour time block,
I need to know how much of this liquid was used. We'll say the liquid is
water. Each row contains the liquid description (water in this case), the
amount of the liquid in tank at the time the row was inserted (the amount is
in gallons), and a column that is a datetime value where the date/time is
the timestamp representing the time when the reading was taken from the
tank. A typical row would look like:
water / 45.8 / 2005-4-11 8:30
On the surface, this problem seems simple. Extract the first row in the
time span, extract the last row in this time span, and subtract the two
values to arrive at how much of the liquid was used. The problem is, the
tank the liquid is in is periodically refilled while readings are being
taken. In other words, in any given four hour time span the liquid might be
at 45.8 gallons at the start of the time segment. The level might drop to
20 gallons 1.5 hours into the time segment at which time the tank might get
filled to 100 gallons. At the end of the time segment, the tank level might
be at 90 gallons. As such, simply subtracting values won't do the trick.
I'm aware this query won't be 100% exact. During the time period the tank
is being refilled, there is no way to know exactly how much liquid is being
used as the level recorded will include the level of the tank as it's being
refilled. However, the time it takes to refill the tank is pretty short.
The people who are expecting this report have been told of this. We have
all agreed the error in accuracy will be acceptable as this report will be
used to help to decide when to order new supplies.
Any thoughts or suggestions on this would be greatly appreciated.
Thanks all!
Chris SmithSince you, (and your client) are willing to accept the inaccuracys from the
usage that takes place during the period of time when the tanks is being
refilled, and since, (I assume from your post) there is no Accurate Record o
f
exactly how much fluid has been added during these refillings, then you can
simply add up the sum of the differences in the Tank Qty values of every pai
r
of adjacent entries within the specified time frame, where the qty
decreased...
Select Sum(N.Qty - P.Qty)
From Tank P Join Tank N
On N.Fluid = P.Fluid
And N.Qty < P.Qty
And N.LogDT =
(Select Min(LogDT) From Tank
Where Fluid = P.FLuid
And LogDT > P.LogDT)
Where P.LogDT > @.StartDT
And N.LogDT < @.EndDT
"someone" wrote:

> Well, I posted a w or so about a problem I had and someone was kind
> enough to respond with help. I'm on my last hurdle for a report I'm doing
.
> I hope someone can help me out on this as well. First of all, before I go
> any further, I hope someone can recommend a good book to help me learn how
> to do problems such as the one below. I'm fairly compentent in T-SQL, but
> problems such as the one below and the one I posted last w are leaving
me
> a bit stumped.
> The problem: I need to find how much of an item (we'll say a liquid) was
> actually used within a given time span, say a timespan of 4 hours. All
> values are stored in a database where a row is inserted every second or so
.
> We'll say a second to keep this simple. During any given 4 hour time bloc
k,
> I need to know how much of this liquid was used. We'll say the liquid is
> water. Each row contains the liquid description (water in this case), the
> amount of the liquid in tank at the time the row was inserted (the amount
is
> in gallons), and a column that is a datetime value where the date/time is
> the timestamp representing the time when the reading was taken from the
> tank. A typical row would look like:
> water / 45.8 / 2005-4-11 8:30
> On the surface, this problem seems simple. Extract the first row in the
> time span, extract the last row in this time span, and subtract the two
> values to arrive at how much of the liquid was used. The problem is, the
> tank the liquid is in is periodically refilled while readings are being
> taken. In other words, in any given four hour time span the liquid might
be
> at 45.8 gallons at the start of the time segment. The level might drop to
> 20 gallons 1.5 hours into the time segment at which time the tank might ge
t
> filled to 100 gallons. At the end of the time segment, the tank level mig
ht
> be at 90 gallons. As such, simply subtracting values won't do the trick.
> I'm aware this query won't be 100% exact. During the time period the tank
> is being refilled, there is no way to know exactly how much liquid is bein
g
> used as the level recorded will include the level of the tank as it's bein
g
> refilled. However, the time it takes to refill the tank is pretty short.
> The people who are expecting this report have been told of this. We have
> all agreed the error in accuracy will be acceptable as this report will be
> used to help to decide when to order new supplies.
> Any thoughts or suggestions on this would be greatly appreciated.
> Thanks all!
> Chris Smith
>
>|||someone wrote:
> Well, I posted a w or so about a problem I had and someone was
kind
> enough to respond with help. I'm on my last hurdle for a report I'm
doing.
> I hope someone can help me out on this as well. First of all, before
I go
> any further, I hope someone can recommend a good book to help me
learn how
> to do problems such as the one below. I'm fairly compentent in
T-SQL, but
> problems such as the one below and the one I posted last w are
leaving me
> a bit stumped.
> The problem: I need to find how much of an item (we'll say a liquid)
was
> actually used within a given time span, say a timespan of 4 hours.
All
> values are stored in a database where a row is inserted every second
or so.
> We'll say a second to keep this simple. During any given 4 hour time
block,
> I need to know how much of this liquid was used. We'll say the
liquid is
> water. Each row contains the liquid description (water in this
case), the
> amount of the liquid in tank at the time the row was inserted (the
amount is
> in gallons), and a column that is a datetime value where the
date/time is
> the timestamp representing the time when the reading was taken from
the
> tank. A typical row would look like:
> water / 45.8 / 2005-4-11 8:30
> On the surface, this problem seems simple. Extract the first row in
the
> time span, extract the last row in this time span, and subtract the
two
> values to arrive at how much of the liquid was used. The problem is,
the
> tank the liquid is in is periodically refilled while readings are
being
> taken. In other words, in any given four hour time span the liquid
might be
> at 45.8 gallons at the start of the time segment. The level might
drop to
> 20 gallons 1.5 hours into the time segment at which time the tank
might get
> filled to 100 gallons. At the end of the time segment, the tank
level might
> be at 90 gallons. As such, simply subtracting values won't do the
trick.
> I'm aware this query won't be 100% exact. During the time period the
tank
> is being refilled, there is no way to know exactly how much liquid is
being
> used as the level recorded will include the level of the tank as it's
being
> refilled. However, the time it takes to refill the tank is pretty
short.
> The people who are expecting this report have been told of this. We
have
> all agreed the error in accuracy will be acceptable as this report
will be
> used to help to decide when to order new supplies.
> Any thoughts or suggestions on this would be greatly appreciated.
> Thanks all!
> Chris Smith
You could either create a cursor which goes through the rows one at a
time and takes the difference between the current row and the one
before it off the total
or use a query like the following one:
select type, sum(quantity-lastquantity)
from (
select type,
quantity,
recordtime,
(select top 1 quantity
from items i
where i.type = items.type
and i.recordtime < items.recordtime
order by i.recordtime) as lastquantity
from items
where recordtime between <start> and <end> ) as diffsource
David Rowland
User clogging resources? Find out who. Download DBMonitor
http://dbmonitor.tripod.com|||>> I hope someone can recommend a good book to help me learn how to do
problems such as the one below. <<
I like SQL FOR SMARTIES myself :) But for temporal stuff, you need to
go to the university of Arizona website and get a copy of the Rick
Snodgrass book on temporal quereis in SQL
The basic model of time is durations, not points (see Snodgrass, Zeno
and Einstein for details). This means that you need to get a complete
fact into your table, not "half-facts"; try something like this:
CREATE TABLE Samplings
(start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL UNIQUE
end_time DATETIME, -- null means still in process
CHECK (start_time < end_time),
start_level DECIMAL (5,2) NOT NULL,
end_level DECIMAL (5,2), -- null means not measured yet
..);
(end_level - start_level)/ (end_time - start_time) will give you the
flow rate in each row, so the samples do not have to be evenly spaced.
Other basic calculations are easy.
You will need a stored procedure to do an update to the row where
(end_time IS NULL) and then insert a new row with the current start
time and level.
The other way is to construct a query with a self-join to assemble the
two parts of the duration for you -- that is how you know that you are
missing something in the design. It is also expensive to keep
re-computing this.