# Calculated Column in Query Based On Sum of Differences in Consecutive Records

Hi-
I am trying to come up with a query to give me the following output (attached image)
This is a chart of fuel pump readings from a 10-day period;
For example, the 802.0 gallons shown here is the sum of four different pumps found by subtracting consecutive day's readings.
This is a delta or change between days readings.
Can anyone tell me how I can calculate these readings and display them in a chart like the attached image?
I have something liike-
pump 1 starting inv.- 1000  ending  200
pump 2 starting inv.- 1000  ending  200
pump 3 starting inv.- 1000  ending  200
pump 4 starting inv.- 1000  ending  202

Thus - when i sum them I get the 802.0 gallons that i need in the chart.
What kind of sql would i use  to get the sum of these differences?
thanks
john

image2.gif
###### Who is Participating?

Commented:
use tempdb
go

(trandate datetime,
recordnum int,

select '7/28/2010',907,873685.9,586133.9,386504.9,722661.9
select '7/29/2010',908,873885.9,586245.9,386679.9,722956.9
select '7/30/2010',909,874130.9,586342.9,386748.9,723250.9
select '7/31/2010',910,874130.9,586342.9,386876.9,723465.9
select '8/05/2010',913,874908.9,586988.9,386998.9,724067.9
select '8/09/2010',914,875303.7,587215.4,387195.5,724607.7
select '8/06/2010',915,875107.6,587158.3,387114.4,724350.6
select '8/10/2010',916,875381.8,587386.5,387253.6,724733.8

As (select      top 100  percent recordid = ROW_NUMBER() Over(Order By trandate asc),
trandate,
recordnum,
Order by trandate)
select today.recordid, today.trandate, today.recordnum,
join readingcte yday on yday.recordid = today.recordid - 1
0

Commented:
Hi,

Could you provide a little more information on the structure of your tables where the stuff like "starting inv.- 1000  ending  200" is stored.

It should not be a very hard thing to do but I cannot quite figure out what you need by the output desired.

Regards,
Tim
0

Commented:
Can you provide a  short list of the rows from the source table?
0

Author Commented:
i was at home and did not have the tables with me-
attached is an image of the table of readings;
it is linked to the main table by tank_id_fw
if you look at the differences between pump readings for the two highlighted records -
and do a difference between the readings for 7-29 and 7-30-
you wil see where i get the 802.0 gallons-
224 + 235 + 128 + 215 = 802.0

this  is the value of GALLONS PUMPED  in mygrid.

thanks
john

2.gif
0

Author Commented:
i got a little farther-
below is my current attempt-
i need to order by date but the subselect is kind of confusing-
can you help with the order by-
also attached is my current output-

SELECT
v.RECORD_number_FW,
a.RECORD_number_FW,
[transaction_date_fw],
a.pump1_meter_fw-v.pump1_meter_fw AS Difference1,
a.pump2_meter_fw-v.pump2_meter_fw AS Difference2,
a.pump3_meter_fw-v.pump3_meter_fw AS Difference3,
a.pump4_meter_fw-v.pump4_meter_fw AS Difference4
--,
--SUM([Difference1]+[Difference2]+[Difference3]+[Difference4]) AS Total
FROM
(SELECT RECORD_number_FW,
pump1_meter_fw,
pump2_meter_fw,
pump3_meter_fw,
pump4_meter_fw,
--transaction_date_fw,
ranking=(SELECT Count(DISTINCT RECORD_number_FW)
FROM USER_DEFINED56_FW u
WHERE u.RECORD_number_FW<=L.RECORD_number_FW)
FROM USER_DEFINED56_FW L) v
left JOIN
(SELECT RECORD_number_FW,
pump1_meter_fw,
pump2_meter_fw,
pump3_meter_fw,
pump4_meter_fw,
transaction_date_fw,
ranking=(SELECT Count(DISTINCT RECORD_number_FW)
FROM USER_DEFINED56_FW u
WHERE u.RECORD_number_FW<=L.RECORD_number_FW)
--AND
--          )
FROM USER_DEFINED56_FW L
WHERE SUBSTRING(TANK_ID_FW,7,3)='UL-'
--AND SUBSTRING(TANK_ID_FW,10,1)=1
AND [ARCHIVE_STATUS_FW]='N') a
ON (a.ranking=v.ranking+1)
WHERE a.RECORD_number_FW IS NOT NULL--BETWEEN 909 AND 919

9072010.gif
0

Author Commented:
I got past the last issue with the order by ....
Now, I have to find a workaround with my data;
Heres the problem:
(I have included a screen shot)
After executing the above code (from 9/08/2010)
I get the attached recordset image.
My results start off correctly but the record numbers are kind of screwed up in the table-
this record_number field is not an identity field; and i cant change this field because this is a canned package;
The problem is in the transaction date of 08-06-2010
Notice how the record_number on the left changes;
maybe some external job entered these records but you can see how 912 and 913 are out of order.

Can anyone find a way around this?
Or does anyone have an alternative?
Thanks
John
final.gif
0

Commented:
Just to clarify, you need to see the sum of gallons pumped for all pumps broken down by day.  Is this correct?
0

Author Commented:
lundnak-
I need to sum the difference of four pumps, between consecutive days and total them in a 10-day span.

the first attachment i showed - called image2.gif is the final version i would like to display.

thanks
john
0

Author Commented:
Ive attached an image - but basically because the record_number_fw is not an identity field,
I have a problem.
What are my options here?
Because now I am trying a CTE with ROW_NUMBER() and I have to select these results into a temporary table and reissue an identity field so I can order by this field to get the records in order.
very time consuming..
if anyone knows another (easy) solution, please chime in.

thanks

error.gif
0

Commented:
Are the record_number_fw fields consecutive?

I worked on this last night, but I must have forgot to post my solution.  I will post it tonight.
0

Commented:
Here is what I got.
``````create table readings
(trandate datetime,
recordnum int,

select '7/28/2010',907,873685.9,586133.9,386504.9,722661.9
select '7/29/2010',908,873885.9,586245.9,386679.9,722956.9
select '7/30/2010',909,874130.9,586342.9,386748.9,723250.9
select '7/31/2010',910,874130.9,586342.9,386876.9,723465.9

select today.trandate, today.recordnum,
join readings yday on yday.recordnum = today.recordnum - 1
``````
0

Author Commented:
thanks - ill try this tomorrow morning-
the problem im having is, in the self join, i can't order by a certain field inside the alias to itself.
It gives me an error;:
I may have a workaround:though to try
For now Ive switched the ranking system from record_number to transaction_date_fw
since the distci
ill post back tomorrow
thanks
john
0

Commented:
Post the error that you are getting
0

Author Commented:
hi-lundnak;
your query looks correct- the problem is my data;
If you look at the image i posted yesterday - because those records are not sequential -
for dates - 8/5 and 8/6-
this screws up my accumulating total.
I tried your code and i cannot order by the record_number_fw field of either today or yday.
therefore i get the negative values seen above.

the record_number_fw field is unique but if i use the date field as my ranking system - i can get the results I need;
but, there may be some conversion issues..
thanks

john
0

Commented:
Sorry forgot to post as code attachment.
``````use tempdb
go

(trandate datetime,
recordnum int,

select '7/28/2010',907,873685.9,586133.9,386504.9,722661.9
select '7/29/2010',908,873885.9,586245.9,386679.9,722956.9
select '7/30/2010',909,874130.9,586342.9,386748.9,723250.9
select '7/31/2010',910,874130.9,586342.9,386876.9,723465.9
select '8/05/2010',913,874908.9,586988.9,386998.9,724067.9
select '8/09/2010',914,875303.7,587215.4,387195.5,724607.7
select '8/06/2010',915,875107.6,587158.3,387114.4,724350.6
select '8/10/2010',916,875381.8,587386.5,387253.6,724733.8

As (select	top 100  percent recordid = ROW_NUMBER() Over(Order By trandate asc),
trandate,
recordnum,
Order by trandate)
select today.recordid, today.trandate, today.recordnum,
join readingcte yday on yday.recordid = today.recordid - 1
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.