Jump to content

If / then question

Butterbean

By Butterbean
in PHP Coding Help

 Share
Go to solution Solved by Barand,

Recommended Posts

Butterbean Member

Butterbean

Posted
Posted (edited)

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);?>
Edited by Butterbean
Link to comment
https://forums.phpfreaks.com/topic/291987-if-then-question/
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

I think you need to first go back and simplify the queries. Just looking at the first query, it looks like it is way overcomplicated. I don't see the need for the aliased SELECT query - or maybe I am missing something. Plus, why do you calculate the dateparts for hour/minute in the aliased query and then calculate them in the outer query? Lastly, WHAT value are you wanting to compare between the two queries? You could likely have ONE query to get the single value. Else, compare the values from the two queries to determine which one you want.

 

After reviewing just the first query, I think it could be simplified to this

 

   SELECT TOP 1
        cdate, comm_id, meter_multiplier,
        max(cast(total_full_energy_b AS FLOAT)/1000) * meter_multiplier AS totalUsage,
        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 [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
        AND 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'
    GROUP BY comm_id, cdate, meter_multiplier
    ORDER BY totalUsage DESC
Link to comment
https://forums.phpfreaks.com/topic/291987-if-then-question/#findComment-1494410
Share on other sites
Butterbean Member

Butterbean

Posted
  • Author
Posted

 

I think you need to first go back and simplify the queries. Just looking at the first query, it looks like it is way overcomplicated. I don't see the need for the aliased SELECT query - or maybe I am missing something. Plus, why do you calculate the dateparts for hour/minute in the aliased query and then calculate them in the outer query? Lastly, WHAT value are you wanting to compare between the two queries? You could likely have ONE query to get the single value. Else, compare the values from the two queries to determine which one you want.

 

After reviewing just the first query, I think it could be simplified to this

   SELECT TOP 1
        cdate, comm_id, meter_multiplier,
        max(cast(total_full_energy_b AS FLOAT)/1000) * meter_multiplier AS totalUsage,
        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 [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
        AND 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'
    GROUP BY comm_id, cdate, meter_multiplier
    ORDER BY totalUsage DESC

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



			
		


		
			

				
					
						

	
	
		

			
				
				

					
				

			
			
			
		

	


					
				
				
			

		

		
	


	

	





	
Link to comment

	
		
	
	
    
         https://forums.phpfreaks.com/topic/291987-if-then-question/#findComment-1494476
    

	
	
Share on other sites

	

	


	

	

	




	



					
					
					
				

					

					
					







	

	

	

		

			

				


	
		Psycho
	

				
				
				
				
					
						
Prolific Member
					
				
			

		

		

			

				


Psycho
			

			

			    Posted 
				
			

		

	

	
	

		




	

		

			

				
    
					
					
					
					
					
				

			

			
		


		

			Posted 
			
			
				
				
			
		

	


	

    

	

		
		

			

Seriously? I spent time trying to decipher your code and provide a response all for nothing? And then you provide over 100 lines of code with no comments or explanation on what the different sections are doing expecting me to reverse engineer it.


 


This code seems to suffer from the same problems I already stated above. You are making things more complicated than they should be. Every single one uses an aliased dynamic table. Maybe those are necessary, but based on the first one I reviewed I doubt it. Here's an example of something that just doesn't make sense:


 


where datepart(weekday, trans_date) IN ('1', '2', '3', '4', '5', '6', '7') 


What is the purpose of this line? Is there a possibility that the weekday would be something other than a number between 1 and 7?


 


Sorry, but I'm not going to take more time out of my day to try and understand what you are doing and try to make sense of it. I wish you luck.



			
		


		
			

				
					
						

	
	
		

			
				
				

					
				

			
			
			
		

	


					
				
				
			

		

		
			

		
	


	

	





	
Link to comment

	
		
	
	
    
         https://forums.phpfreaks.com/topic/291987-if-then-question/#findComment-1494486
    

	
	
Share on other sites

	

	


	

	

	




	



					
					
					
				

					

					
					







	

	

	

		

			

				


	
		Butterbean
	

				
				
					
						
Member
					
				
			

		

		

			

				


Butterbean
			

			

			    Posted 
				
			

		

	

	
	

		




	

		

			

				
    
					
						
  • Author
    • 
					
					
					
					
					
				

			

			
		


		

			Posted 
			
			
				
				
			
		

	


	

    

	

		
		

			



Seriously? I spent time trying to decipher your code and provide a response all for nothing? And then you provide over 100 lines of code with no comments or explanation on what the different sections are doing expecting me to reverse engineer it.


 


This code seems to suffer from the same problems I already stated above. You are making things more complicated than they should be. Every single one uses an aliased dynamic table. Maybe those are necessary, but based on the first one I reviewed I doubt it. Here's an example of something that just doesn't make sense:


where datepart(weekday, trans_date) IN ('1', '2', '3', '4', '5', '6', '7') 


What is the purpose of this line? Is there a possibility that the weekday would be something other than a number between 1 and 7?


 


Sorry, but I'm not going to take more time out of my day to try and understand what you are doing and try to make sense of it. I wish you luck.




Ok.



			
		


		
			

				
					
						

	
	
		

			
				
				

					
				

			
			
			
		

	


					
				
				
			

		

		
	


	

	





	
Link to comment

	
		
	
	
    
         https://forums.phpfreaks.com/topic/291987-if-then-question/#findComment-1494515
    

	
	
Share on other sites

	

	


	

	

	




	



					
					
					
				

					

					
					







	

	

	

		

			

				


	
		Barand
	

				
				
				
				
					
						
Prolific Member
					
				
			

		

		

			

				


Barand
			

			

			    Posted 
				
			

		

	

	
	

		




	

		

			

				
    
					
					
					
					
					
				

			

			
		


		

			Posted 
			
			
				
				
			
		

	


	

    

	

		
		

			

There seems to a basic flaw in that subquery you use everywhere.


 


It assumes that the usage is the max reading - min reading.


 


In another post of yours you have problem where the readings trip over back to zero, so if you have readings like this for a quarter


 


2013-12-31    99999800


2014-02-28    00000010


2014-03-31    00000250


 


then if you use the max and min only you get usage of 210 when it should be 450



			
		


		
			

				
					
						

	
	
		

			
				
				

					
				

			
			
			
		

	


					
				
				
			

		

		
			

		
	


	

	





	
Link to comment

	
		
	
	
    
         https://forums.phpfreaks.com/topic/291987-if-then-question/#findComment-1494981
    

	
	
Share on other sites

	

	


	

	

	




	



					
						
    
							
							
								
  • 
									2 months later...
								
    • 
							
						

					
					
					
				

					

					
					







	

	

	

		

			

				


	
		Butterbean
	

				
				
					
						
Member
					
				
			

		

		

			

				


Butterbean
			

			

			    Posted 
				
			

		

	

	
	

		




	

		

			

				
    
					
						
  • Author
    • 
					
					
					
					
					
				

			

			
		


		

			Posted 
			
			
				
				
			
		

	


	

    

	

		
		

			

So, I'm trying to fix this issue but really don't have the knowledge to do so.  I am enrolled in a php course but for now I could really use your help.  Below is what I've attempted to try to fix the issue.  Can you please tell me what I have done wrong so far?  I know there is a lot to clean up to make this code faster to query and more resilient and I will get to that soon.  For now, I'm focusing on the issue when I reach 10M.  Please see below.


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

if ((maxUsage - minUsage)*meter_multiplier)<0:{
  ((maxUsage - minUsage)+10000000)*meter_multiplier);
}else {
((maxUsage - minUsage)*meter_multiplier)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') 
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);?> 





			
		


		
			

				
					
						

	
	
		

			
				
				

					
				

			
			
			
		

	


					
				
				
			

		

		
	


	

	





	
Link to comment

	
		
	
	
    
         https://forums.phpfreaks.com/topic/291987-if-then-question/#findComment-1503346
    

	
	
Share on other sites

	

	


	

	

	




	



					
					
					
				

					

					
					







	

	
		

			
    
				
				
					
  •  Solution
    • 
				
				
				
			

		

	

	

		

			

				


	
		Barand
	

				
				
				
				
					
						
Prolific Member
					
				
			

		

		

			

				


Barand
			

			

			    Posted 
				
			

		

	

	
	

		




	

		

			

				
    
					
					
					
						
  •  Solution
    • 
					
					
					
				

			

			
		


		

			Posted 
			
			
				
				
			
		

	


	

    

	

		
		

			

I don't have your data so I created my own.


+------------------+------------+------------+---------+
| meter_reading_id | account_id | date_read  | reading |
+------------------+------------+------------+---------+
|                1 |        121 | 2015-12-25 | 9999332 |
|                2 |        121 | 2016-01-25 | 9999727 |
|                3 |        121 | 2016-02-25 | 9999949 |
|                4 |        121 | 2016-03-25 |     242 |
|                5 |        121 | 2016-04-25 |     510 |
|                6 |        122 | 2015-12-15 | 9999214 |
|                7 |        122 | 2016-01-15 | 9999473 |
|                8 |        122 | 2016-02-15 | 9999872 |
|                9 |        122 | 2016-03-15 |     167 |
|               10 |        122 | 2016-04-15 |     473 |
|               11 |        123 | 2015-12-16 | 9999316 |
|               12 |        123 | 2016-01-16 | 9999695 |
|               13 |        123 | 2016-02-16 | 9999941 |
|               14 |        123 | 2016-03-16 |     197 |
|               15 |        123 | 2016-04-16 |     510 |
|               16 |        124 | 2015-12-20 | 9999277 |
|               17 |        124 | 2016-01-20 | 9999600 |
|               18 |        124 | 2016-02-20 | 9999700 |
|               19 |        124 | 2016-03-20 | 9999800 |
|               20 |        124 | 2016-04-20 | 9999986 |
|               21 |        125 | 2015-12-21 | 9999227 |
|               22 |        125 | 2016-01-21 | 9999512 |
|               23 |        125 | 2016-02-21 | 9999793 |
|               24 |        125 | 2016-03-21 |     132 |
|               25 |        125 | 2016-04-21 |     362 |
+------------------+------------+------------+---------+



You can apply the method to your data. The target will be to get the usage for each account for the period Jan 1st to Mar 31st. To do this you need, for each account


  • last reading prior to Jan 1st
    • 

  • last reading prior to Mar 31st
    • 

then find the difference, adjusting those that have gone over the 10M.


 


Method is to find the latest (MAX) date required then match the reading dates to find the reading on that date


 


So


SELECT r1.account_id
, r1.reading as initial
, r2.reading as final
, CASE WHEN r2.reading < r1.reading
    THEN 10000000 + r2.reading - r1.reading
    ELSE r2.reading - r1.reading
    END as acc_usage
FROM 
    (
    SELECT m1.account_id, m1.reading
    FROM meter_reading m1
        JOIN 
            (
            SELECT account_id
            , MAX(date_read) as date_read 
            FROM meter_reading
            WHERE date_read < '2015-01-01'
            GROUP BY account_id
            ) mx1 USING (account_id, date_read)
    ) r1
JOIN
    (
    SELECT m2.account_id, m2.reading
    FROM meter_reading m2
        JOIN 
            (
            SELECT account_id
            , MAX(date_read) as date_read 
            FROM meter_reading
            WHERE date_read < '2015-04-01'
            GROUP BY account_id
            ) mx2 USING (account_id, date_read)
    ) r2 ON r1.account_id = r2.account_id
ORDER BY account_id;
+------------+---------+---------+-----------+
| account_id | initial | final   | acc_usage |
+------------+---------+---------+-----------+
|        121 | 9999332 |     242 |       910 |
|        122 | 9999214 |     167 |       953 |
|        123 | 9999316 |     197 |       881 |
|        124 | 9999277 | 9999800 |       523 |
|        125 | 9999227 |     132 |       905 |
+------------+---------+---------+-----------+





			
		


		
			

				
					
						

	
	
		

			
				
				

					
				

			
			
			
		

	


					
				
				
			

		

		
			

		
	


	

	





	
Link to comment

	
		
	
	
    
         https://forums.phpfreaks.com/topic/291987-if-then-question/#findComment-1503376
    

	
	
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.
			

		

		
	

		
	

	

	

		

			×
			
		

		

	



						
					
				
					
				
					
				
			
			
    
				
					
						
					
						
					
						
							
  • 
								


    
	

    

								
							
    • 
						
					
				
				
					
  • 

	

    • 
				
			

		

	



			
		

	

	
		

			

				
					


    
          Share
    

    

				
				
                

                


				
			

		

	







	

		
			
				Go to topic listing
			
		
	

	




						


					

					


					

				

			

			
		

		
		


	

		×
		

			
				

					
				

			

			

			

			
		

	






	

		×
		

			
    
				
  • 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.