mysql select quarter

[helenp]

Hi,
I have this select:
select
sum(comision)
-sum(pagado) as total
from pagados
WHERE MONTH(llegada) <= MONTH(DATE_ADD(NOW(), INTERVAL -1 MONTH))
to see the total for the previous month,

But I like more viewing quarters.

I tried to just swop month for quarter but donīt work, how to do it?
this is what I would like:
where quarter (llegada) <= quarter(DATE_ADD(NOW(), INTERVAL -1 quarter))",$dbh);

What I want is the total for the previous quarter counting from now.

[coopster]

The values QUARTER (and WEEK) are only allowed as of MySQL >= 5.0.0 in a DATE_ADD [dev.mysql.com] function. You can use the QUARTER function, but not the QUARTER value in an INTERVAL.

Knowing that, and knowing that a quarter is merely three (3) months, you just modify your query accordingly. Don't forget, though, that the quarter before quarter 1 is quarter 4, which is NOT less than qtr 1 -- you'll have to add that logic to your statement. I didn't test this, but I think I have it...

SELECT 
 ... 
 FROM table 
 WHERE QUARTER(mydate) = QUARTER(DATE_ADD(NOW(), INTERVAL -3 MONTH)) 
 AND YEAR(mydate) = YEAR(NOW()) 
 OR QUARTER(DATE_ADD(NOW(), INTERVAL -3 MONTH)) = 4 
 AND YEAR(mydate) = YEAR(NOW()) - 1 
;

[helenp]

<<<<Don't forget, though, that the quarter before quarter 1 is quarter 4, which is NOT less than qtr 1 >>>

this is only for quarter?
because the same code for month seems to work perfect, at least on my tests.

tried the code and error....
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in

thanks anyway

[coopster]

because the same code for month seems to work perfect, at least on my tests.

That's because you are using NOW() for the date and the month is going to be 10. Replace the NOW() in your month query with '2004-01-01' to simulate the first month of the year.

[helenp]

That's because you are using NOW() for the date and the month is going to be 10. Replace the NOW() in your month query with '2004-01-01' to simulate the first month of the year.

I donīt get what you say,
do you mean my month select is not ok, to get the previous months totals?
I better do more tests.

About the quarter select I got an very complicated solution from an expert, will post in when I tested it.

[helenp]

Just tried your code again:
SELECT ... FROM table WHERE QUARTER(mydate) = QUARTER(DATE_ADD(NOW(), INTERVAL -3 MONTH)) AND YEAR(mydate) = YEAR(NOW()) OR QUARTER(DATE_ADD(NOW(), INTERVAL -3 MONTH)) = 4 AND YEAR(mydate) = YEAR(NOW()) - 1 ;
And it donīt give errors....donīt know what I did yesterday :)

But the total it gives up is not correct, the total should be from day 1 to last day of the three previous month (previous quarter) excluding the current month.

[helenp]

what I said before is not correct.
the total should be as I put it even though donīt work:
<= quarter(DATE_ADD(NOW(), INTERVAL -1 quarter))",

i.e everything until last day in previous quarter

[helenp]

doing this I am a lot nearer,
even though the only entry for december 2003 is missing....
exactly what you said about 4 is less than 1

from propiedades where propiedades.id_propiedad = '$propiedad' and quarter(fecha) < quarter(current_date) OR QUARTER(DATE_ADD(NOW(), INTERVAL -3 MONTH)) = 4 AND YEAR(fecha) = YEAR(NOW()) - 1

This only gives the same result as above:
where propiedades.id_propiedad = '$propiedad' and quarter(fecha) < quarter(current_date)

this gives me result 980 euros which is not correct, should be 1.080 with entry dec. 2003 that is missing.

[helenp]

I got it...........:)

from propiedades where propiedades.id_propiedad = '$propiedad' and quarter(fecha) < quarter(current_date) OR YEAR(fecha) < YEAR(NOW())",$dbh);

[coopster]

Ah! You wanted all previous quarters, I thought you wanted only the previous quarter. You got it, congratulations!

[helenp]

<<<You got it, congratulations! >>>

thanks to you,
saw in your code that OR can be used, didnīt have a clue that could be used that way.