get balance past month mysql

[helenp]

Hi again,
I have another problem very similar to this thread that I finally fixed and this one can be fixed the same way:
[webmasterworld.com...]
but the problem will be when february has 29 days instead of 28, at least I think will be a problem.

As i said in the other thread, my host updated mysql to version 5 and there are some problems in my scripts.
I have this query:
$date = $year."-".$month."-".$day;
SELECT DATE_FORMAT(fecha, '%d-%m-%y') as ffecha, importe, pagado, servicios, DATE_FORMAT(llegada, '%e/%c') as fllegada, DATE_FORMAT(salida, '%e/%c') salida, TO_DAYS(salida) - TO_DAYS(llegada) as dias, propiedades.id_propiedad FROM propiedades LEFT JOIN bookings ON propiedades.id_reservas = bookings.id AND propiedades.id_propiedad = bookings.propiedad WHERE propiedades.id_propiedad = '$propiedad' and month(fecha) = '$month' and year(fecha) = '$year' order by fecha", $dbh);
This query gives me the movements and balance for the month you choose in a form.
this works perfect,
the problem is when you are supposed to get previous months balance, sometimes it works and sometimes failes, this is the code for that:
<input type="hidden" name="day" size="1" value="31">
select
sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos
from propiedades where fecha <= DATE_ADD('$date', INTERVAL -1 MONTH) and propiedades.id_propiedad = '$propiedad'"

So I will fix this the same as I did with the other one,
I take away the hidden day field with value 31, and add the day to the month like this 01-31, 02-28, 03-31 etc.
and change $date to $date = $year."-".$month;
seems to work, but there is a but, I have a booking date 31-08-2007 and If I choose movements for september 2007 the previous balance for august, does not include the 31st august booking....So something is wrong, and what will happen when february has 29 days?

[deMorte]

I think the problem is caused by the fact that September ends on 30th day, where August ends on 31st. I think that when you go a month back (INTERVAL -1 MONTH) you actually only end up on August 30th. Same problem will probably whenever you are going back from a month that has less days than the previous month.

Relating to February 29th, you could try testing the year if it is a leap year and adjusting the last day of February accordingly.

if(date("L",$year)) { 
 //leap year, February ends on 29th 
} else { 
 // February ends on 28th 
} 

[helenp]

Thanks deMorte,
Actually I donīt understand it though in the manual of mysql they say this:
if you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:

mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
-> '1998-02-28'

I guess I should be the same if you rest a month?

Is there no way to do that select without using the day in the date? sort of:
from propiedades where month(fecha) <= DATE_ADD('$date', INTERVAL -1 MONTH) and propiedades.id_propiedad = '$propiedad but doing that I would have to say the year, and whatout when it is january/december year before?

[helenp]

I keep trying,
A way could be to do this:
SELECT sum( importe ) - sum( servicios ) - sum( pagado ) AS total_duenos
FROM propiedades
WHERE month( fecha ) <= DATE_SUB( '2007-09-30', INTERVAL 1
MONTH )
AND propiedades.id_propiedad = 'Casa_Blanca_5'
but that does not give me the correct balance for month of august either.
Please help, must be a way

[deMorte]

One way I think you could go back to the last day of the previous month is to change the date from which you are going back as the first day of the previous month, then going back 1 day.
You could use the year and month you get earlier and make a new date for substracting date.

$date = $year."-".$month."-".$day;  
$subdate = $year."-".$month."-01"; 

Then just use this new $subdate to count to the last day

DATE_SUB('$subdate', INTERVAL 1 DAY) 

This is how I'd go on solving the problem. There might be a smoother way but I'm not that good at SQL.

Hope this helps.

[helenp]

Yes but, the script should work for all months on the year. I do not have to add a day always. The user choose a month and year they want to view the movements and balance and on that page the previous month balance shows.

Another way could be to use php, I could just change the month for $date, itīs easy to do if I use the same year, but it should be changing only the month and not include the year, and I do not know that much php, and also will be a problem when there is december-january, in that case would have to change the year in the date as well.

I could get this months movements and balance the same as before with mysql though that works perfect, then in if statements I say if the month choosen is 09 it should be 08
like this:
If $month = 09{
$newmonth = 08}
Then I get the balance for month 08 the same way as got month 09.

[deMorte]

I'm not sure if I understand your problem, but if your users can select only the year and month maybe you can use something like the following.

$year = //USER INPUT 
$month = //USER INPUT 
$day = 01; 
$unixtime = strtotime($day.".".$month."."$year); 
$days_in_month = date("t",$unixtime); 
$date_last = $year."-".$month."-".$day; 
$date_first = $year."-".$month."-01"; 
// SQL-query to get the balance for the selected month here 
// using $date_last as the last day to get the balance 
// SQL-query to get the balance from the last day of previous month 
SELECT sum( importe ) - sum( servicios ) - sum( pagado ) AS total_duenos 
FROM propiedades 
WHERE month( fecha ) <= DATE_SUB( '$date_first', INTERVAL 1 
DAY ) 
AND propiedades.id_propiedad = 'Casa_Blanca_5'  

This should solve your problems with the last day of the month as well as with the previous year.
If user selects to view the balance from January 2007, you will get the balance and movements until 31.1.2007 and when you go a day backwards from 1.1.2007, you'll end up in 31.12.2006.

[edited by: deMorte at 11:55 am (utc) on May 30, 2008]

[helenp]

Thanks a lot,
but it do not give me the correct balance for previous month.

I have this:
$day = 01;
$unixtime = strtotime($day."-".$month."-".$year);
$days_in_month = date("t",$unixtime);

$date_last = $year."-".$month."-".$day;
$date_first = $year."-".$month."-".-01;
(there was a small error in your $date_last = $year."-".$month."-".$day;
$date_first = $year."-".$month."-01"; which I fixed I think, I donīt understand what $unixtime and $days_in_month is for, as it is no used anywhere in the script)

"SELECT sum( importe ) - sum( servicios ) - sum( pagado ) AS total_duenos
FROM propiedades
WHERE month( fecha ) <= DATE_SUB( '$date_first', INTERVAL 1
DAY )
AND propiedades.id_propiedad = '$propiedad' ",

But I see your idea, is a good one.

[whoisgregg]

I think that mktime and strtotime are your friends for any date manipulation. Here's a test page to show how to use those to generate the first and last date for any given month:

<?php
$year = isset($_POST['year'])? intval($_POST['year']) : date("Y");
$month = isset($_POST['month'])? intval($_POST['month']) : date("m");
?>
<form action="" method="post">
<p>Year <input type="text" name="year" value="<?php echo $year; ?>"></p>
<p>Month <input type="text" name="month" value="<?php echo $month; ?>"></p>
<p><input type="submit" /></p>
</form>
<?php
$current_month_first = mktime(0,0,0, $month, 1, $year);
$current_month_last = strtotime("-1 day", strtotime("+1 month", $current_month_first));
$date_first = date("Y-m-d", $current_month_first);
$date_last = date("Y-m-d", $current_month_last);
echo '<p>$date_first: '.$date_first.'</p>';
echo '<p>$date_last: '.$date_last.'</p>';
?>

I don't 100% understand what you want to accomplish with your SQL query, but if it's grabbing all the records for the given month, this might do the trick:

$sql = "SELECT sum( importe ) - sum( servicios ) - sum( pagado ) AS total_duenos 
FROM propiedades 
WHERE fecha >= '".$date_first."' 
AND fecha <= '".$date_last."' 
AND propiedades.id_propiedad = '".$propiedad."' ";

That should work if the field `fecha` is a DATE field, but if it's a DATETIME or TIMESTAMP field then you'll also need to provide the hours portion for each date:

$sql = "SELECT sum( importe ) - sum( servicios ) - sum( pagado ) AS total_duenos 
FROM propiedades 
WHERE fecha >= '".$date_first." 00:00:00' 
AND fecha <= '".$date_last." 23:59:59' 
AND propiedades.id_propiedad = '".$propiedad."' ";

[helenp]

Now that point works, like this:
SELECT sum( importe ) - sum( servicios ) - sum( pagado ) AS total_duenos
FROM propiedades
WHERE fecha <= DATE_SUB( '$date_first', INTERVAL 1
DAY )
AND propiedades.id_propiedad = '$propiedad
Also to get the selected months movements works.

But there is an aditional problem:
I do need the last day of the month to be choosen,
though to get the selected (not the previous) month balance, I need to do this select:
select sum(
case when month(fecha) = '$month' and year(fecha) = '$year' and propiedades.id_propiedad = '$propiedad'
then importe
else null end
) as sum_importe_this_month
, sum(
case when month(fecha) = '$month' and year(fecha) = '$year' and propiedades.id_propiedad = '$propiedad'
then servicios
else null end
) as sum_servicios_this_month
, sum(
case when month(fecha) = '$month' and year(fecha) = '$year' and propiedades.id_propiedad = '$propiedad'
then pagado
else null end
) as sum_pagado_this_month
, sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos
from propiedades where fecha <= '$date' and propiedades.id_propiedad = '$propiedad

[helenp]

Thanks a lot to you both, I have it working now.

[helenp]

Hi again,
I have a doubt, In the control panel, the client can choose to view balance and movements per month or per quarter.
I am thinking now, if the problem with the date can give me the same problem with the quarters?
The script is the same as before, If the person choose to view quarter July-september 2007 the date is choosen in the form as 2007-09-30 and then the select is similar as the one with months, this is the select:
select
sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos
from propiedades WHERE propiedades.id_propiedad = '$propiedad' and fecha <= DATE_ADD('$date', INTERVAL -1 QUARTER)
As far as I can see all the balances seems ok, but could be there arenīt any on the last day or first day of that quarter for the property I view.
What do you think?
As there was a error when I did INTERVAL -1 MONTH should there not be the same with QUARTER?

[helenp]

To make sure the quarter works perfect, I am trying to do it the same way as whoisgregg did, getting the dates with php. I need as date 2007-09-30 and 2007-06-30 i.e the last day of quarter choosen and last day of quarter before. In the form I choose 2007-09 but not the day.

So far I have this:
$year = isset($_POST['year'])? intval($_POST['year']) : date("Y");
$month = isset($_POST['month'])? intval($_POST['month']) : date("m");
$current_month_first = mktime(0,0,0, $month, $day, $year);
$current_month_last = strtotime("-1 day", strtotime("-2 month", $current_month_first));
$date_first = date("Y-m-d", $current_month_first);
$date_last = date("Y-m-d", $current_month_last);
echo '<p>$date_first: '.$date_first.'</p>';
echo '<p>$date_last: '.$date_last.'</p>';

And the result I get is near, but I need to add a month to $date_first.
This is my result:
$date_first: 2007-08-31

$date_last: 2007-06-30
Been trying to change the script to get the correct dates, but donīt get it.

[helenp]

I managed to get the dates, I am stupid tried to change yours instead of just adding another variable.

$date_first: 2008-09-01

$date_first2: 2008-06-30

$date_last: 2008-09-30

$year = isset($_POST['year'])? intval($_POST['year']) : date("Y");
$month = isset($_POST['month'])? intval($_POST['month']) : date("m");
$date = $_REQUEST['date'];
$current_month_first = mktime(0,0,0, $month, 1, $year);
$current_month_first2 = strtotime("-1 day", strtotime("-2 month", $current_month_first));
$current_month_last = strtotime("-1 day", strtotime("+1 month", $current_month_first));
$date_first = date("Y-m-d", $current_month_first);
$date_first2 = date("Y-m-d", $current_month_first2);
$date_last = date("Y-m-d", $current_month_last);
echo '<p>$date_first: '.$date_first.'</p>';
echo '<p>$date_first2: '.$date_first2.'</p>';
echo '<p>$date_last: '.$date_last.'</p>';

I do think this is safer than doing -1 QUARTER, I am not sure but just in case.