Butterbean

For starters i just realized i screwballed this up. I gave you the wrong parts of the code. I will start over to clarify. Also, I will give you all the code for the invoice, so you can see what I'm trying to accomplish. Basically, we have a utility bill that the customer gets monthly. This customer is billed by two different components. One component is kWh the other is KVA. kWh The kWh portion is calculated as an ongoing value, just like electric usage in a home. There is a start date and a stop date created on the user form that queries SQL. KVA Area of interest in this code. There are three periods of KVA. BASE INTERMEDIATE PEAK The are broken down like this. BASE is anytime of the day 24/7 INTEMEDIATE are at the selected times and days during the weekdays. PEAK are also at a even more refined set of times and days during the weekdays. As a result, I have one string for PEAK, one for INTERMEDIATE but there will need to be 2 strings and some if statement to decide between the two sections of BASE period (Bweekday or Bweekend - which ever is highest.) BASE is ..... if ((Bweekday > Bweekend), Bweekday, Bweekend) .... in excel lingo. INTERMEDIATE = Intermediate PEAK = Peak The three values for KVA (Via the four segments below) are queried and found by the code are dropped in [invoice]. Infact, what you are looking at is this... $sql = kWh calcs. $sql1 = KVA BASE weekday $sql2 = KVA BASE weekends $sql3 = KVA INTERMEDIATE all week $sql4 = KVA PEAK all week and, not listed here is the rest of the code for the winter months. This is just the summertime. I accidentally gave you $sql2 and $sql3 to look at earlier. It should be $sql1 and $sql2. I'm sure there are a bunch of ways to make this code much more appealing (and quicker to execute as well). I will certainly humor that. My main pain is to only output ($sql1 or $sql2) and $sql3 and $sql4 to the invoice.php for these selected months. let me know if you need more clarification. Thank you btw. <?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, 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_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' "; $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); ?> <?php $sql1 = ";WITH TOTAL_FULL_ENERGY_SUM_BASE_WEEKDAY_SUMMER AS ( SELECT cdate, datepart(hh, cdate) as trans_hour, datepart(mi, cdate) as trans_minute, comm_id, max(cast(total_full_energy_b AS FLOAT)/1000) * meter_multiplier AS totalUsage, 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, cdate, meter_multiplier ) SELECT top 1 *, datepart(weekday, cdate) as trans_date_day, datepart(month, cdate) as trans_date_month, datepart(hour, cdate) as trans_date_hour, DATEPART(minute, cdate) as trans_date_minute FROM TOTAL_FULL_ENERGY_SUM_BASE_WEEKDAY_SUMMER where datepart(weekday, cdate) IN ('2', '3', '4', '5', '6') AND DATEPART(MONTH, cdate) IN ('5','6','7','8','9','10') and cdate between '$base_date $base_start_time' and '$base_date $base_end_time' ORDER BY totalUsage desc"; $query1 = sqlsrv_query($conn, $sql1);if ($query1 === false){ exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row1 = sqlsrv_fetch_array($query1)){ $sumUsageWKW += $row1[totalUsage];}sqlsrv_free_stmt($query1); ?> <?php $sql2 = ";WITH TOTAL_FULL_ENERGY_SUM_BASE_WEEKEND_SUMMER AS ( SELECT cdate, datepart(hh, cdate) as trans_hour, datepart(mi, cdate) as trans_minute, comm_id, max(cast(total_full_energy_b AS FLOAT)/1000) * meter_multiplier AS totalUsage, 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, cdate, meter_multiplier ) SELECT top 1 *, datepart(weekday, cdate) as trans_date_day, datepart(month, cdate) as trans_date_month, datepart(hour, cdate) as trans_date_hour, DATEPART(minute, cdate) as trans_date_minute FROM TOTAL_FULL_ENERGY_SUM_BASE_WEEKEND_SUMMER where datepart(weekday, cdate) IN ('1', '7') AND DATEPART(MONTH, cdate) IN ('5','6','7','8','9','10') and cdate between '$base_date $base_start_time' and '$base_date $base_end_time' ORDER BY totalUsage desc"; $query2 = sqlsrv_query($conn, $sql2);if ($query2 === false){ exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row2 = sqlsrv_fetch_array($query2)){ $sumUsageWEW += $row2[totalUsage];}sqlsrv_free_stmt($query2); ?> <?php $sql3 = ";WITH TOTAL_FULL_ENERGY_SUM_INTERMEDIATE_WEEKDAY_SUMMER AS ( SELECT cdate, datepart(hh, cdate) as trans_hour, datepart(mi, cdate) as trans_minute, comm_id, max(cast(total_full_energy_b AS FLOAT)/1000) * meter_multiplier AS totalUsage, 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, cdate, meter_multiplier ) SELECT top 1 *, datepart(weekday, cdate) as trans_date_day, datepart(month, cdate) as trans_date_month, datepart(hour, cdate) as trans_date_hour, DATEPART(minute, cdate) as trans_date_minute FROM TOTAL_FULL_ENERGY_SUM_INTERMEDIATE_WEEKDAY_SUMMER where datepart(weekday, cdate) IN ('2', '3','4', '5', '6') AND DATEPART(MONTH, cdate) IN ('5','6','7','8','9','10') and trans_hour in ('10','11','12','13','14','15','16','17','18','19','20','21','22') and cdate between '$int_date $int_start_time' and '$int_date $int_end_time' ORDER BY totalUsage desc"; $query3 = sqlsrv_query($conn, $sql3);if ($query3 === false){ exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row3 = sqlsrv_fetch_array($query3)){ $sumUsageWKWi += $row3[totalUsage];}sqlsrv_free_stmt($query3); ?> <?php $sql4 = ";WITH TOTAL_FULL_ENERGY_SUM_PEAK_WEEKDAY_SUMMER AS ( SELECT cdate, datepart(hh, cdate) as trans_hour, datepart(mi, cdate) as trans_minute, comm_id, max(cast(total_full_energy_b AS FLOAT)/1000) * meter_multiplier AS totalUsage, 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, cdate, meter_multiplier ) SELECT top 1 *, datepart(weekday, cdate) as trans_date_day, datepart(month, cdate) as trans_date_month, datepart(hour, cdate) as trans_date_hour, DATEPART(minute, cdate) as trans_date_minute FROM TOTAL_FULL_ENERGY_SUM_PEAK_WEEKDAY_SUMMER where datepart(weekday, cdate) IN ('2', '3', '4', '5', '6') AND DATEPART(MONTH, cdate) IN ('5','6','7','8','9','10') and trans_hour in ('13','14','15','16','17','18','19') and cdate between '$peak_date $peak_start_time' and '$peak_date $peak_end_time' ORDER BY totalUsage desc";$query4 = sqlsrv_query($conn, $sql4);if ($query4 === false){ exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row4 = sqlsrv_fetch_array($query4)){ $sumUsageWKWp += $row4[totalUsage]; }sqlsrv_free_stmt($query4); ?>

Sorry. That is what I am trying to accomplish. If anyone could offer me some advice on how to start this, I would appreciate it. Thanks.

If the colde for $sql2 fetch's a value that is larger than what $sql3 fetch's, output the value of $query2, otherwise output the value of $query3. <?php $sql2 = ";WITH TOTAL_FULL_ENERGY_SUM_BASE_WEEKEND_SUMMER AS ( SELECT cdate, datepart(hh, cdate) as trans_hour, datepart(mi, cdate) as trans_minute, comm_id, max(cast(total_full_energy_b AS FLOAT)/1000) * meter_multiplier AS totalUsage, 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, cdate, meter_multiplier ) SELECT top 1 *, datepart(weekday, cdate) as trans_date_day, datepart(month, cdate) as trans_date_month, datepart(hour, cdate) as trans_date_hour, DATEPART(minute, cdate) as trans_date_minute FROM TOTAL_FULL_ENERGY_SUM_BASE_WEEKEND_SUMMER where datepart(weekday, cdate) IN ('1', '7') AND DATEPART(MONTH, cdate) IN ('5','6','7','8','9','10') and cdate between '$base_date $base_start_time' and '$base_date $base_end_time' ORDER BY totalUsage desc"; $query2 = sqlsrv_query($conn, $sql2);if ($query2 === false){ exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row2 = sqlsrv_fetch_array($query2)){ $sumUsageWEW += $row2[totalUsage];}sqlsrv_free_stmt($query2);?> <?php $sql3 = ";WITH TOTAL_FULL_ENERGY_SUM_INTERMEDIATE_WEEKDAY_SUMMER AS ( SELECT cdate, datepart(hh, cdate) as trans_hour, datepart(mi, cdate) as trans_minute, comm_id, max(cast(total_full_energy_b AS FLOAT)/1000) * meter_multiplier AS totalUsage, 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, cdate, meter_multiplier ) SELECT top 1 *, datepart(weekday, cdate) as trans_date_day, datepart(month, cdate) as trans_date_month, datepart(hour, cdate) as trans_date_hour, DATEPART(minute, cdate) as trans_date_minute FROM TOTAL_FULL_ENERGY_SUM_INTERMEDIATE_WEEKDAY_SUMMER where datepart(weekday, cdate) IN ('2', '3','4', '5', '6') AND DATEPART(MONTH, cdate) IN ('5','6','7','8','9','10') and trans_hour in ('10','11','12','13','14','15','16','17','18','19','20','21','22') and cdate between '$int_date $int_start_time' and '$int_date $int_end_time' ORDER BY totalUsage desc"; $query3 = sqlsrv_query($conn, $sql3);if ($query3 === false){ exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row3 = sqlsrv_fetch_array($query3)){ $sumUsageWKWi += $row3[totalUsage];}sqlsrv_free_stmt($query3);?>

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?

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

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.

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

Ok, so I got it to work and spit out a value in the right location with no errors. Now, the value spit out is wrong however. I suspect it has something to do with the way I subtracted the min and the max. I need to have the multiplier added to the total amount. Basically. (Maxusage-Minusage)* meter multiplier. Then that value should be sent on. it appears that I only have it applying to the max. Maybe its just my lack of knowledge on structure. <?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)/1)*meter_multiplier AS minUsage, MAX((total_energy_a+total_energy_b+total_energy_c)/1)*meter_multiplier AS maxUsage, meter_multiplier, repeated 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, 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);?>

What Im trying to accomplish and am getting nowhere fast is..... Based on user input, the code will query the system for data, based on an inputted start date. (Ex. On July 1, 2014 the customers kWh was 3410). Based on another user input of the stop date, we generate another number for (Ex. July 31st, 2014 the customers kWh was 4210) The total kWh for the month is 4210-3410 = 800kWh. The trick here is that the data that is queried has to be multiplied by a number that is stored in column called meter_multiplier in a table called invoice before its sent to invoice. Assuming the multiplier is 2.1, the output would be 800 *2.1 = 1680 kWh

Given the following code, <?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)* meter_multiplier AS minUsage, meter_multiplier MAX((total_energy_a+total_energy_b+total_energy_c)/100)* meter_multiplier AS maxUsage, meter_multiplier, repeated FROM [radiogates].[dbo].[purge_data] 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, 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_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','5') 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);?> I keep getting an error of, Array ( [0] => Array ( [0] = > 42000 [SQLSTATE] => 42000 [1] => 102 [code] => 102 [2] => [Microsoft][SQL Server Natvie Client 11.0][SQL Server]Incorrect syntax near '('. [message] => [Microsoft][SQL Server Native Cleint 11.0][SQL Server]Incorrect syntax near '('. instead of polling the DB. I don't know why. I assume there is some sort of error with a parenthesis but it seems as if I have the executable data in the parenthesis correctly.

Thank you.

Well, I will try to fix my mistakes in posting on the next thread. For now, I have max(cast(total_full_energy_b AS FLOAT)/1000) * meter_multiplier AS totalUsage, meter_multiplier My problem is that I don't know how to manipulate FLOAT so that it gives me 2 decimals.

Is it because I didn't include this below, before my code? error_reporting(E_ALL | E_NOTICE); ini_set(display_errors', "1");

Sorry, I see now what I am supposed to do. Thank you.

Below is some PHP code that looks at a DB and outputs a value within the user declared time and date period, via user recall using a web app attached to it. The code is rounding the numbers but I want it to include 2 of the decimal places. The original numbers in the DB have up to 5 decimal digits. This code is making them whole numbers. i would like to include the first 2 digits. I suspect the float should somehow be float(2) . Not sure how to do that. thanks in advance <?php $sql = ";WITH TOTAL_FULL_ENERGY_SUM_BASE_WEEKDAY_SUMMER AS ( SELECT cdate, datepart(hh, cdate) as trans_hour, datepart(mi, cdate) as trans_minute, comm_id, max(cast(total_full_energy_b AS FLOAT)/1000) * meter_multiplier AS totalUsage, 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, cdate, meter_multiplier ) SELECT top 1 *, datepart(weekday, cdate) as trans_date_day, datepart(month, cdate) as trans_date_month, datepart(hour, cdate) as trans_date_hour, DATEPART(minute, cdate) as trans_date_minute FROM TOTAL_FULL_ENERGY_SUM_BASE_WEEKDAY_SUMMER where datepart(weekday, cdate) IN ('2', '3', '4', '5', '6') AND DATEPART(MONTH, cdate) IN ('5','6','7','8','9','10') and cdate between '$base_date $base_start_time' and '$base_date $base_end_time' ORDER BY totalUsage desc"; $query = sqlsrv_query($conn, $sql);if ($query === false){ exit("<pre>".print_r(sqlsrv_errors(), true));}while ($row = sqlsrv_fetch_array($query)){ $sumUsageWKW += $row[totalUsage];}sqlsrv_free_stmt($query);?>

My apologies for overreacting. Thanks for your help.

Any particular reason you were an a#% to me just now? I was kidding that you can make the system good but someone like me will come along and complicate it. Not sure why you felt inclined to act the way you did, but anyway.

Thank you. That was it. So, there is a master sheet somewhere I suppose.

I am trying to learn PHP on my own through examining existing code that I have. It's code written for a database. The web app allows the user to login and interrogate the system for and extract information onto a utility bill. I noticed this nice bit of informational code at the top.... if( $conn ) { echo "Connection established.<br />";}else{ echo "Connection could not be established.<br />"; die( print_r( sqlsrv_errors(), true));} I would expect the output would be "Connection established". or "Connection could not be established". but it reads this. "Connection established. You are currently logged in as" 123456 No where in the code is there a statement that includes "You are currently logged in as". Maybe I'm overlooking something. thanks for any help. I can not send the code because I do not have permission. Any help would be greatly appreciated.