Difference Between 2 Dates 1

[likelylad]

I would like to run the following query.
It will sum all dates that are between 4 and 9 days old

This is what I have so far

Select Sum(if((TO_DAYS(NOW()) - TO_DAYS(Date_Entered))(>4 and <9),1,0)) As 4to9 from table Group By Whatever

The problem is with the (>4 and <9) part, because if I just put >4 the query will work correctly.

Thanking in advance for any help received

 

[r937]

what do you mean, &quot;sum all dates&quot;??

if you want to sum something over a range of dates, try this --

[tt]select foo, sum(bar) as 4to9
from yourtable
where TO_DAYS(NOW()) - TO_DAYS(Date_Entered))
between 5 and 8
group
by foo[/tt]

BETWEEN includes the end points of the range given, so since you had >4 and <9 instead of >=4 and <=9, therefore 4 and 9 should not be included

rudy

http://r937.com/

 

[likelylad]

What worked for me was the following

Select Sum(if((TO_DAYS(NOW()) - TO_DAYS(Date_Entered))Between 4 and 9,1,0)) As 4to9 from table Group By Whatever

 

[r937]

well, i figured you really did mean to include the endpoints, but i wasn't sure

also, if you had said count the days instead of sum the days...



[tt]select count(*)
from yourtable
where TO_DAYS(NOW()) - TO_DAYS(Date_Entered))
between 4 and 9[/tt]

rudy

 

[likelylad]

The reason why I had to have it in this format was that the query would include counts upto 4 days and a count after 9 days.
In effect I would have a crosstab looking query results

Thanks for your help.