• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

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
0
jtrapat1
Asked:
jtrapat1
  • 7
  • 7
1 Solution
 
timbraunCommented:
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
 
lundnakCommented:
Can you provide a  short list of the rows from the source table?
0
 
jtrapat1Author Commented:
sorry aboutthat -
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
jtrapat1Author 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
 
jtrapat1Author 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
 
lundnakCommented:
Just to clarify, you need to see the sum of gallons pumped for all pumps broken down by day.  Is this correct?
0
 
jtrapat1Author 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
 
jtrapat1Author Commented:
Heres a little more info about the exact problem with this database design;
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
 
lundnakCommented:
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
 
lundnakCommented:
Here is what I got.
create table readings
(trandate datetime,
recordnum int,
pump1reading decimal (10,1),
pump2reading decimal (10,1),
pump3reading decimal (10,1),
pump4reading decimal (10,1))

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

select today.trandate, today.recordnum,
	today.pump1reading - yday.pump1reading pump1,
	today.pump2reading - yday.pump2reading pump2,
	today.pump3reading - yday.pump3reading pump3,
	today.pump4reading - yday.pump4reading pump4,
	(today.pump1reading - yday.pump1reading +
		today.pump2reading - yday.pump2reading +
		today.pump3reading - yday.pump3reading +
		today.pump4reading - yday.pump4reading) total
from readings today
join readings yday on yday.recordnum = today.recordnum - 1

Open in new window

0
 
jtrapat1Author 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
 
lundnakCommented:
Post the error that you are getting
0
 
jtrapat1Author 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
 
lundnakCommented:
use tempdb
go

drop table readings

create table readings
(trandate datetime,
recordnum int,
pump1reading decimal (10,1),
pump2reading decimal (10,1),
pump3reading decimal (10,1),
pump4reading decimal (10,1))

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

;with readingcte
 As (select      top 100  percent recordid = ROW_NUMBER() Over(Order By trandate asc),
                  trandate,
                  recordnum,
                  pump1reading,
                  pump2reading,
                  pump3reading,
                  pump4reading
      From readings
      Order by trandate)
select today.recordid, today.trandate, today.recordnum,
      today.pump1reading - yday.pump1reading pump1,
      today.pump2reading - yday.pump2reading pump2,
      today.pump3reading - yday.pump3reading pump3,
      today.pump4reading - yday.pump4reading pump4,
      (today.pump1reading - yday.pump1reading +
            today.pump2reading - yday.pump2reading +
            today.pump3reading - yday.pump3reading +
            today.pump4reading - yday.pump4reading) total
from readingcte today
join readingcte yday on yday.recordid = today.recordid - 1
0
 
lundnakCommented:
Sorry forgot to post as code attachment.
use tempdb
go

drop table readings

create table readings
(trandate datetime,
recordnum int,
pump1reading decimal (10,1),
pump2reading decimal (10,1),
pump3reading decimal (10,1),
pump4reading decimal (10,1))

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

;with readingcte
 As (select	top 100  percent recordid = ROW_NUMBER() Over(Order By trandate asc),
			trandate,
			recordnum,
			pump1reading,
			pump2reading,
			pump3reading,
			pump4reading
	From readings
	Order by trandate)
select today.recordid, today.trandate, today.recordnum,
	today.pump1reading - yday.pump1reading pump1,
	today.pump2reading - yday.pump2reading pump2,
	today.pump3reading - yday.pump3reading pump3,
	today.pump4reading - yday.pump4reading pump4,
	(today.pump1reading - yday.pump1reading +
		today.pump2reading - yday.pump2reading +
		today.pump3reading - yday.pump3reading +
		today.pump4reading - yday.pump4reading) total
from readingcte today
join readingcte yday on yday.recordid = today.recordid - 1

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now