Consumption Calculation

[karenn1]

Hey everyone,

 

I have a SQL query that returns a table of information as follows:

 

 

   

Date

   

Reading

   

Consumption

 

 

   

12 Jan

   

39

   

0

 

 

   

13 Jan

   

54

   

15

 

 

   

14 Jan

   

59

   

5

 

 

   

15 Jan

   

63

   

4

 

 

   

16 Jan

   

83

   

20

 

 

The Query itself is as follows:

 

$sql_ref = "SELECT * FROM devices WHERE Chan1_reference = '".$_REQUEST['reference']."'";
  $result_ref = mysql_query($sql_ref);
  $rs_ref = mysql_fetch_array($result_ref);
  
  
  //List of filtered data based on Ref code above
  $sql_list = "SELECT * FROM datalog WHERE Device_Code = '".$rs_ref['Device_Code']."'
  	AND Log_Date BETWEEN '".$_REQUEST['day_from']."' AND '".$_REQUEST['day_to']."'
  	AND Zone_Code = '".$_REQUEST['zone']."'";
  $result_list = mysql_query($sql_list);
  $rs_list = mysql_fetch_array($result_list);

 

Basically, I want to calculate the consumption as in the table above. This value isn't in the table. I need it to take the last value in the range and minus the value just before that. That gives the consumption for that entry.

 

I have no idea how to do this with SQL. Can someone please help me?

 

 

Thanks!

 

Karen

[karenn1]

Can anybody please help???

[fenway]

Huh? Give an example... and the table structure.

[karenn1]

Hi fenway!

 

Thanks for replying. I'm pulling information from two tables into one. This is done to match up the searched reference number from the devices table to its channel in the datalog table. This is my latest query:

 

$sql = "SELECT Log_Date, Log_Reading, Channel, Device_Code, Zone_Code FROM datalog
		WHERE Zone_Code = '".$_REQUEST['zone']."'
		AND Log_Date BETWEEN '".$_REQUEST['day_from']."' AND '".$_REQUEST['day_to']."'
		AND (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan1_Reference='".$_REQUEST['reference']."') AND Channel=1)   
		OR  (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan2_Reference='".$_REQUEST['reference']."') AND Channel=2)  
		OR  (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan3_Reference='".$_REQUEST['reference']."') AND Channel=3)  
		OR  (Device_Code IN (SELECT Device_Code FROM devices WHERE Chan4_Reference='".$_REQUEST['reference']."') AND Channel=4) 
		ORDER BY Channel, Log_Date";
$result = mysql_query($sql);
$rs = mysql_fetch_array($result);

 

This returns data that I put into an HTML table using php and it looks like this:

 

Log_Date      Log_Reading        Channel

12 Jan                39                  1

13 Jan                54                  1

14 Jan                59                  1

15 Jan                63                  1

16 Jan                83                  1

 

My client wants to know how much water was consumed from one reading to the next, ie. I want another field for consumption so the above HTML table must look like this:

 

Log_Date      Log_Reading        Channel            Consumption

12 Jan                39                  1                      ----

13 Jan                54                  1                        15

14 Jan                59                  1                        5

15 Jan                63                  1                        4

16 Jan                83                  1                        20

 

I have been able to do this using PHP and a while loop but I'm having problems now using a PHP graph because I can't have it within the while loop. I need the graph to pull it's information from the SQL query.

 

Would it be possible to work out the consumption using mySQL? Is there anybody else who can help?

 

 

Thanks!

 

Karen

 

[karenn1]

Can anyone please help?

[fenway]

Well, you can use user variables for this task... but I still don't see why you can't use a multi-pass approach in php.