Unexpected Math Result

[Butterbean]

 In a past post I mentioned that my ultimate goal of this is code is to query our SQL system for a start date and a stop date.  At both of those points, the query would spit out a value for that the column based on a determined time and day.  The first point would be a min or minUsage.  The second point would be max or maxUsage.  These points will be subtracted from each other to create a value.  See below.  Not that it matters but the value is the amount of kWh per months.  

 

So, now, Min and Max are both defined below but I suspect the value is being truncated for each.  Also, Im not sure the calculation is right because I am getting an unexpected solution.  I am asking that someone look at this code and tell me what should be happening mathematically.  (down to the accuracy of the answer (pre calculation and post calculation)  It would help me better see my mistakes. 

 

To give you an example of some data.  I have 

 

Max ( or July 31)

 

total_energy_a =26872

total_energy_b =27619

total_energy_c =26175

 

Min ( or July 1)

total_energy_a =20347

total_energy_a =20914

total_energy_a =19808

 

Max - Min 

19597

 

With these numbers, would you expect this outcome from this code?

 

Thanks.

<?php
$sql = ";WITH TOTAL_KWH_SUMMER 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, repeated
  FROM [radiogates].[dbo].[purge_data] 
  where comm_id='$comm_id' 
  group by comm_id, CONVERT(VARCHAR(10),cdate,111), datepart(hh, cdate), repeated
)
SELECT *, datepart(weekday, trans_date) as trans_date_day, datepart(month, trans_date) as 

trans_date_month, maxUsage - minUsage as totalUsage
FROM TOTAL_KWH_SUMMER 

where datepart(weekday, trans_date) IN ('1', '2', '3', '4', '5', '6', '7') 
AND DATEPART(MONTH, trans_date) IN ('5','6','7','8','9','10') 
and trans_date BETWEEN '$startdate2 00:00:01' AND '$enddate2 24:00:00' and repeated <> '1' ";
$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);?> 

[Barand]

1.which the real min value for total_energy_a, and why no min values for _b and _c?

 

2. Are you intending to plaster this whole forum with variations of the same question?

[Butterbean]

1.  I see.  that was a cut an paste error.   

 

2.  I wasn't aware I asked this question before.   Since i marked the last question solved, I thought it was fitting to start a new topic.  

Edited October 17, 2014 by Butterbean

[Butterbean]

Corrected values.

 

To give you an example of some data.  I have 

 

Max ( or July 31)

 

total_energy_a =26872

total_energy_b =27619

total_energy_c =26175

 

Min ( or July 1)

total_energy_a =20347

total_energy_b =20914

total_energy_c =19808

 

Max - Min 

19597

Edited October 17, 2014 by Butterbean

[Barand]

From your previous topic (which I now have to search for to get the background on this problem) you are trying to get the min values (as at 1st of the month) and max values (as at end of the month) and you get the difference as the usage total.

 

That being the case why get the MAX and MIN for each hour of each day in your TOTAL_KWH_SUMMER table subquery?

[Butterbean]

From your previous topic (which I now have to search for to get the background on this problem) you are trying to get the min values (as at 1st of the month) and max values (as at end of the month) and you get the difference as the usage total.

 

That being the case why get the MAX and MIN for each hour of each day in your TOTAL_KWH_SUMMER table subquery?

There may come a time in the near future that the collection times change.  I should probably deal with this with a table but for now I can adjust them here. It may not always be cut and dry 00:00:01 through 24:00:00  .  Do you suspect issue with this line of code?

[Barand]

I have no idea what is causing the issue. All you have given us is a query that apparently doesn't produce what you expect.

 

I don't know

• your table structure,
• what the input data is to your query,
• what output the query is giving from that data
• what you expect the query to be giving.

So pretty much in the dark