Unclear Error Message when polling SQL

[Butterbean]

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.    

Edited October 16, 2014 by Butterbean

[Psycho]

The string for the query starts with a semi-colon which causes it to interprets the first line as a comment. Therefore, it sees the beginning of the actual query as a left parenthesis

;WITH TOTAL_KWH_WINTER AS
(

Edited October 16, 2014 by Psycho

[maxxd]

Also looks like you're missing a comma after meter_multiplier on line 5. Any reason why you're selecting meter_multiplier twice (once on line 5, again on 6)?

[Butterbean]

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

[Psycho]

Forget what I said before, dashes are used for comments. But, the semi-colon is still probably problematic.

 

But, if I am reading that correct, you are also running two separate queries. Start with a simple query, that works, then add the additional complexity to get the specific data you need.

[Butterbean]

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