no explanation to this sum mysql

[helenp]

Hi
this is just incredible,
canīt find any reason to it.
I have an test database, where I insert bookings for diferents properties, and sum the comisions etc.

As well I sum the previous month and quarters balance just to show looks better that way.
I did an test with one property, put in an booking for every month for year 2003, 2004 and 2005, works just perfect, for the first booking in january 2003 there is no previous balance shown up, as should be, there is nothing before january 2003.

Now I insert test bookings for other properties, but comencing 1 november 2004,
and cant believe it, an previous balance is shown up for all properties, same amount 703 euros for all new properties, as if there was anything previous, but there arenīt anything.
Only the previous is wrong.....how to explain this?

I have several diferents codes for viewing previous balance and the same balance shows up for all of them.
This is some of them:
$result=mysql_query("select
sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos
from propiedades where propiedades.id_propiedad = '$propiedad' and month(fecha) < month(NOW()) AND YEAR(fecha) <= YEAR(NOW()) OR YEAR(fecha) < YEAR(NOW())",$dbh);

$result=mysql_query("select
sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos
from propiedades where propiedades.id_propiedad = '$propiedad' and quarter(fecha) < quarter(current_date) and YEAR(fecha) <= YEAR(NOW()) or year(fecha) < YEAR(NOW())",$dbh);

[coopster]

Can you show us a sample of the data in the file and then what you expect to see come out after your query runs?

[helenp]

I think instead of an sample I put the hole, hope itīs not to long, itīs just an test.
this is the table exported:

CREATE TABLE `propiedades` (
`id` int(11) NOT NULL auto_increment,
`fecha` date NOT NULL default '0000-00-00',
`id_propiedad` char(30) NOT NULL default '',
`id_reservas` int(10) default NULL,
`id_limpieza` char(10) default NULL,
`packid` char(10) default NULL,
`limpieza` date default NULL,
`pack` char(10) default NULL,
`importe` decimal(10,2) default NULL,
`servicios` decimal(10,2) default NULL,
`pagado` decimal(10,2) default NULL,
`comentarios` char(20) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=63 ;

--
-- Dumping data for table `propiedades`
--

INSERT INTO `propiedades` VALUES (20, '2004-06-06', 'Banana_Beach_2', 20, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (22, '2005-02-28', 'Banana_Beach_2', 22, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (25, '2003-01-01', 'Banana_Beach_2', 25, NULL, 'pack', NULL, NULL, 100.00, 0.00, 100.00, '');
INSERT INTO `propiedades` VALUES (19, '2004-01-01', 'Banana_Beach_2', 19, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (27, '2004-04-15', 'Banana_Beach_2', 26, NULL, 'pack', NULL, NULL, 0.00, 30.00, 0.00, '');
INSERT INTO `propiedades` VALUES (23, '2004-11-01', 'Banana_Beach_2', 23, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (18, '2003-11-01', 'Banana_Beach_2', 18, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (17, '2003-05-05', 'Banana_Beach_2', 17, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (26, '2004-01-15', 'Banana_Beach_2', 25, '6', NULL, '2004-01-15', NULL, 0.00, 40.00, 0.00, '');
INSERT INTO `propiedades` VALUES (21, '2004-12-12', 'Banana_Beach_2', 21, NULL, NULL, NULL, NULL, 100.00, 0.00, 500.00, '');
INSERT INTO `propiedades` VALUES (16, '2003-01-01', 'Banana_Beach_2', 16, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (24, '2005-10-01', 'Banana_Beach_2', 24, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (28, '2005-10-08', 'Banana_Beach_2', 27, '7', NULL, '2005-10-08', NULL, 0.00, 40.00, 0.00, '');
INSERT INTO `propiedades` VALUES (29, '2003-01-11', 'Banana_Beach_2', NULL, NULL, NULL, NULL, NULL, 0.00, 20.00, 0.00, 'bombilla');
INSERT INTO `propiedades` VALUES (30, '2004-05-05', 'Banana_Beach_2', NULL, NULL, NULL, NULL, NULL, 0.00, 20.00, 0.00, 'bombilla');
INSERT INTO `propiedades` VALUES (31, '2004-10-10', 'Banana_Beach_2', 28, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (32, '2003-02-02', 'Banana_Beach_2', 29, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (33, '2003-03-03', 'Banana_Beach_2', 30, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (34, '2003-04-04', 'Banana_Beach_2', 31, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (35, '2003-06-06', 'Banana_Beach_2', 32, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (36, '2003-07-07', 'Banana_Beach_2', 33, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (37, '2003-08-08', 'Banana_Beach_2', 34, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (38, '2003-09-09', 'Banana_Beach_2', 35, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (39, '2003-10-10', 'Banana_Beach_2', 36, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (40, '2003-12-12', 'Banana_Beach_2', 37, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (41, '2004-02-02', 'Banana_Beach_2', 38, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (42, '2004-03-04', 'Banana_Beach_2', 39, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (43, '2004-07-04', 'Banana_Beach_2', 40, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (44, '2004-08-08', 'Banana_Beach_2', 41, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (45, '2004-09-09', 'Banana_Beach_2', 42, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (46, '2005-01-01', 'Banana_Beach_2', 43, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (47, '2005-03-05', 'Banana_Beach_2', 44, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (48, '2005-04-05', 'Banana_Beach_2', 45, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (49, '2005-05-05', 'Banana_Beach_2', 46, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (50, '2005-06-06', 'Banana_Beach_2', 47, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (51, '2005-07-07', 'Banana_Beach_2', 48, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (52, '2005-08-08', 'Banana_Beach_2', 49, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (53, '2005-09-09', 'Banana_Beach_2', 50, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (54, '2005-11-01', 'Banana_Beach_2', 51, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (55, '2005-12-01', 'Banana_Beach_2', 52, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (56, '2006-01-01', 'Banana_Beach_2', 53, NULL, NULL, NULL, NULL, 50.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (57, '2004-11-01', 'Casa_Blanca_5', NULL, NULL, NULL, NULL, NULL, 0.00, 35.00, 0.00, 'bombillas');
INSERT INTO `propiedades` VALUES (58, '2004-11-02', 'Casa_Blanca_5', NULL, NULL, NULL, NULL, NULL, 0.00, 20.00, 0.00, 'otra bombilla');
INSERT INTO `propiedades` VALUES (59, '2004-11-01', 'Casa_Blanca_5', NULL, NULL, NULL, NULL, NULL, 0.00, 25.00, 0.00, 'otra bombilla');
INSERT INTO `propiedades` VALUES (60, '2004-11-01', 'Villa_Nadia', NULL, NULL, NULL, NULL, NULL, 100.00, 25.00, 0.00, 'bombilla');
INSERT INTO `propiedades` VALUES (61, '2004-11-01', 'Villa_Palmera', 54, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');
INSERT INTO `propiedades` VALUES (62, '2004-12-01', 'Villa_Palmera', 55, NULL, NULL, NULL, NULL, 100.00, 0.00, 0.00, '');

As you can see, at least one entry every month for 'Banana_Beach_2'during 2003, 2004 and 2005,

Below selects gives me the previous balance just perfect for 'Banana_Beach_2', I even checked it month by month and quarter by quarter,,,,,,, itīs just perfect.
in one page I autmatically view current month or current quarters comisions, payments etc, and with another select I select the past month or past quarters balance,,, including everything before past month or past quarter.

And if I prefer to choose month or quarter to view, it is displayed with choosen month or quarters movement and as well showing previous month or quarters balance.

The current balances are just fine, only that with properties 'Casa_Blanca_5', 'Villa_Nadia' and 'Villa_Palmera' I get an wrongly previous balance of 730 euros, same for all of them, but with 'Banana_Beach_2' the previous balance is always perfect,
as if it is something in memory, as you can see on the table there is nothing for these properties before 1 november 2004, but for 'Banana_Beach_2' there is entrys from january 2003.

These are all the select for viewing previous balances,
all gives the same result...correct for 'Banana_Beach_2' and wrong for the rest.

to get currents months previous balance:
$result=mysql_query("select
sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos
from propiedades where propiedades.id_propiedad = '$propiedad' and month(fecha) < month(NOW()) AND YEAR(fecha) <= YEAR(NOW()) OR YEAR(fecha) < YEAR(NOW())",$dbh);

to get selected dates (31-month-year) previous balance;
$result=mysql_query("select
sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos
from propiedades where propiedades.id_propiedad = '$propiedad' AND month(fecha) < '$month' AND YEAR(fecha) <= '$year' OR YEAR(fecha) < '$year' ",$dbh);

no need to put the quarters select as well, the error is just the same....

[helenp]

Figured out it is because of the selects...
cant do it that way,

these 2 selects seems to work perfect,
I donīt know if that is correct but the variable $date is defined by $date = $year."-".$month."-".$day;
in an scrolldown menu, one choose month and year, for the day I put an hidden value saying 31 days, donīt know if can give me troubbles?

$result=mysql_query("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'",$dbh);

$result=mysql_query("select
sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos
from propiedades WHERE propiedades.id_propiedad = '$propiedad' and fecha <= DATE_ADD('$date', INTERVAL -3 MONTH)",$dbh);

But I cant figure out how to do the current month and current quarters previous balance.... in this case their is no menu to choose from

I want to do something like:
$result=mysql_query("select
sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos
from propiedades where propiedades.id_propiedad = '$propiedad' and MONTH(fecha) = MONTH(CURRENT_DATE)(DATE_ADD(NOW(), INTERVAL -1 MONTH)) and YEAR(fecha) = YEAR (CURRENT_DATE) or YEAR(fecha) < YEAR (CURRENT_DATE)",$dbh);

and similiar for the quarter only difference -3 month

[helenp]

gosh,
If i understand english correct OR is only available in mysql 5.0?
[dev.mysql.com...]

[helenp]

reading that page, I see I canīt use OR.

using union it works,
but now I get 2 balances, how does I put them into one?
any ideas?

$result=mysql_query("
SELECT sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos
from propiedades where propiedades.id_propiedad = '$propiedad' and MONTH(fecha) < MONTH (CURRENT_DATE) AND YEAR(fecha) = YEAR (CURRENT_DATE)
UNION
SELECT sum(importe)
- sum(servicios)
-sum(pagado) as total_duenos1
from propiedades where propiedades.id_propiedad = '$propiedad' and YEAR(fecha) < YEAR (CURRENT_DATE)",$dbh);

[helenp]

finally I think I got it right,
cross my fingers:

was question of parentesis:
from propiedades WHERE ( propiedades.id_propiedad = '$propiedad' ) AND ( ( quarter(fecha) < quarter(current_date) AND YEAR(fecha) = YEAR (CURRENT_DATE) ) OR ( YEAR(fecha) < YEAR(CURRENT_DATE) ) )",$dbh);