Solved

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

Posted on 2010-09-06
15
498 Views
Last Modified: 2012-06-21
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
Comment
Question by:jtrapat1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 3

Expert Comment

by:timbraun
ID: 33613160
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

by:lundnak
ID: 33614295
Can you provide a  short list of the rows from the source table?
0
 

Author Comment

by:jtrapat1
ID: 33618006
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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 

Author Comment

by:jtrapat1
ID: 33625972
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

by:jtrapat1
ID: 33631644
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

by:lundnak
ID: 33631926
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

by:jtrapat1
ID: 33636494
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

by:jtrapat1
ID: 33641215
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
 
LVL 7

Expert Comment

by:lundnak
ID: 33641275
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

by:lundnak
ID: 33642964
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
 

Author Comment

by:jtrapat1
ID: 33643034
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

by:lundnak
ID: 33643122
Post the error that you are getting
0
 

Author Comment

by:jtrapat1
ID: 33646279
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

by:
lundnak earned 500 total points
ID: 33648377
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
 
LVL 7

Expert Comment

by:lundnak
ID: 33648396
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question