Jump to content

Unexpected Math Result


Butterbean

Recommended Posts

 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);?> 

Link to comment
Share on other sites

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 by Butterbean
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.