Forum Moderators: coopster
I tested the code having an start table like this:
id_propiedad baja_inicio baja_fin precio_baja
villa_nad 2004-11-01 2004-12-22 45.60
I did this test code changing the variables for real dates:
SELECT sum(
IF ( (baja_inicio BETWEEN '2004-11-07' AND '2004-11-10') AND (baja_fin BETWEEN '2004-11-07' AND '2004-11-10'), precio_baja*(TO_DAYS('2004-11-10')-TO_DAYS('2004-11-07')),
IF ( (baja_inicio BETWEEN '2004-11-07' AND '2004-11-10') AND (baja_fin < '2004-11-10'), precio_baja*(TO_DAYS(baja_fin )-TO_DAYS('2004-11-07')),
IF ( (baja_inicio > '2004-11-07') AND (baja_fin BETWEEN '2004-11-07' AND '2004-11-10'), precio_baja*(TO_DAYS('2004-11-10')-TO_DAYS(baja_inicio)),0)))) as price FROM propiedad WHERE id_propiedad='villa_nad
It works, i.e donīt get any errors but donīt sum, the result is 0, the precio_column is an decimal(10.2)
And I suppose this should work, but think would be an enormous code when manage to get to work and put all the dates in,
Actually I have all these dates
2004-11-01 2004-12-22
2004-12-23 2005-01-06
2005-01-07 2005-03-19
2005-03-20 2005-04-03
2005-04-04 2005-06-30
2005-07-01 2005-08-31
2005-09-01 2005-10-31
this is for one year....
will be an awful lot of if.s
how to do this?
Below selects works perfect, i.e when I do one case I get the result and when I do the second case separately it works as well, but when putting them together I get error, I could always do several selects, but the correct would be to have all the cases in one select:
$result = mysql_query ("SELECT
(CASE WHEN ( '$llegada' and '$salida'
BETWEEN baja_inicio AND baja_fin ) THEN sum( precio_baja ) * ( TO_DAYS( '$salida' ) - TO_DAYS( '$llegada' ) )ELSE NULL
END) AS price
(CASE WHEN ( '$llegada'
>= baja_inicio and '$salida' > baja_fin) THEN sum( precio_baja ) * ( TO_DAYS( baja_fin ) - TO_DAYS( '$llegada' ) )ELSE NULL
END ) AS price1
FROM propiedad
", $dbh);
I canīt see what is wrong, tried putting () at end and beginning but the same
