MSSQL/PHP Rolling data issue

[Butterbean]

Hello everyone,   

<?php
$sql = ";WITH TOTAL_KWH_WINTER AS
(
SELECT CONVERT(VARCHAR(10),cdate,111)AS trans_date, datepart(hh, cdate) as trans_hour, comm_id,
MIN((total_energy_a+total_energy_b+total_energy_c)/100) AS minUsage,
MAX((total_energy_a+total_energy_b+total_energy_c)/100) AS maxUsage, meter_multiplier
  FROM [radiogates].[dbo].[purge_data] LEFT OUTER JOIN [radiogates].[dbo].[ops_invoice] on [radiogates].[dbo].[purge_data].[comm_id] = [radiogates].[dbo].[ops_invoice].[meter_id] 
  where comm_id='$comm_id'and meter_multiplier is not null 
  group by comm_id, CONVERT(VARCHAR(10),cdate,111), datepart(hh, cdate), meter_multiplier
)
SELECT *, datepart(weekday, trans_date) as trans_date_day, datepart(month, trans_date) as 

trans_date_month, ((maxUsage - minUsage)*meter_multiplier) as totalUsage
FROM TOTAL_KWH_WINTER 

where datepart(weekday, trans_date) IN ('1', '2', '3', '4', '5', '6', '7') 
AND DATEPART(MONTH, trans_date) IN ('10','11','12','1','2','3','4') 
and trans_date BETWEEN '$startdate2 00:00:01' AND '$enddate2 24:00:00' ";
$query = sqlsrv_query($conn, $sql);if ($query === false){  exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row = sqlsrv_fetch_array($query)){  	$sumUsageKWH += $row[totalUsage];}sqlsrv_free_stmt($query);?> 

**************For whatever reason, my browser will not let me put this code where it needs to go, so its up top.  Sorry for the inconvenience.*************

 

My concern are code lines 4 through 15

 

I have PHP code that queryies MSSQL for data called kilowatt hours (kWh), set in a combination of three columns.  kWh is set into the db table once every minute and it a value that starts at zero (0) at the beginning of the meters life, then continues until it reaches 10,000,000 before resetting back to zero (0).  This happens over weeks, months or years, but it does happen.  The code I have listed here makes no consideration of this reset.  Since we are alsways querying between a start date and a stop date, we need to know the kWh usage between that period. 

 

So, we used  maxUsage - minUsage and called that the totalUsage.  That works great until the data reset at 10M.  At that point, max usage will be some value (ex. 1 kWh), where min usage will be some value (ex. 9,999,999 kWh).  the system breaks down at that point.  I need to find an algorithm that helps me take into consideration this rolling number set. 

 

Thanks for any help! 

Edited January 16, 2015 by Butterbean

[Barand]

That seems to be same problem I pointed out to you last October

http://forums.phpfreaks.com/topic/291987-if-then-question/?do=findComment&comment=1494981

 

You need the reading at the beginning of the period and the reading at the end then subtract one from the other. If it is negative, add 10M.

[Butterbean]

Thank you for referring to that topic.   After revisiting it, I think I understand now.

Edited January 16, 2015 by Butterbean