tags:

views:

290

answers:

2
+1  A: 

Assuming your table looks like this

CREATE TABLE invoices (
    -- ...some stuff ...
    client_id ...,
    due_date date ,
    amount_due ...,
    -- ... some more stuff ...
)

Try this:

SELECT client_id,
    DATEDIFF(CURDATE(), due_date) AS days_past_due,
    SUM(IF(days_past_due = 0, amount_due, 0)),
    SUM(IF(days_past_due BETWEEN 1 AND 30, amount_due, 0)),
    SUM(IF(days_past_due BETWEEN 31 AND 60, amount_due, 0)),
    SUM(IF(days_past_due BETWEEN 61 AND 90, amount_due, 0)),
    SUM(IF(days_past_due > 90, amount_due, 0))
FROM invoices
GROUP BY client_id
John Douthat
thanks for your quick reply john...i will try it out...
Gnaniyar Zubair
Hi john,i used the above query. CurrentDate is 08-05-2009DueDate is 2009-06-08But date difference is displays as 100. i have to change the format of date?pls clarify?- Gnaniyar Zubair
Gnaniyar Zubair
OOPS! Indeed, the subtraction doesn't work past month boundaries. I will fix the query in the answer.
John Douthat
Hi john, i have changed format of currentDate as yyyy-MM-dd. now it is working fine. Thanks a lot.
Gnaniyar Zubair
A: 

I get an unknown column for days_past_due? I can get around it by copying the equation, but I am doing some more complicated math and it would be nice if you could use the alias in the select statement the way your answer does.

For example:

SELECT 10 AS my_num, my_num*5 AS another_number
FROM table

This results in unknown column "my_num"

Tom Rossi