Solved

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

Posted on 2010-09-06
15
490 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
  • 7
  • 7
15 Comments
 
LVL 3

Expert Comment

by:timbraun
Comment Utility
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
Comment Utility
Can you provide a  short list of the rows from the source table?
0
 

Author Comment

by:jtrapat1
Comment Utility
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
 

Author Comment

by:jtrapat1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:jtrapat1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Post the error that you are getting
0
 

Author Comment

by:jtrapat1
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now