The below code is supposed to yield a calculation of kWh onto an invoice. it does produce a result and in the correct place. I am able to change parameters and watch the values change, so I know its in the right location (as the data is changing in the location when prompted). The only problem is, when comparing the expected output to the achieved output, there is a major difference. At both of those points below, 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. The value is the amount of kWh per a user specified time period (or month in this case).
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.
I kind of feel maybe the truncation is happening in SQL.
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
With these numbers, would you expect this outcome from this code?
Thanks.
[php]<?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) AS minUsage,
MAX(total_energy_a+total_energy_b+total_energy_c) 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(”
".print_r(sqlsrv_errors(), true));}while ($row = sqlsrv_fetch_array($query)){ $sumUsageKWH += $row[totalUsage];}sqlsrv_free_stmt($query);?>[/php]