Solved

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

Posted on 2010-09-06
490 Views
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
0
Question by:jtrapat1
• 7
• 7

LVL 3

Expert Comment

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

LVL 7

Expert Comment

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

Author Comment

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 Comment

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 Comment

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

LVL 7

Expert Comment

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

Author Comment

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 Comment

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

LVL 7

Expert Comment

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

LVL 7

Expert Comment

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 Comment

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

LVL 7

Expert Comment

Post the error that you are getting
0

Author Comment

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

LVL 7

Accepted Solution

lundnak earned 500 total points
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

LVL 7

Expert Comment

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

## Featured Post

### Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, usâ€¦
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.