List:General Discussion« Previous MessageNext Message »
From:Jay Blanchard Date:June 7 2006 6:52pm
Subject:functions in AS
View as plain text  
select psDealerID, 
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)), 
sum(if(substring(updated, 1, 10) = date_sub(curdate(), interval 1 day),
1, 0)) 
from provision 
group by psDealerID

I love crosstab queries, but one thing really eats at me. I'd like to be
able to add significance to the AS with a function. So that the return
would look something like;

+------------+------------+------------+
| psDealerID | 2006-06-07 | 2006-06-06 |
+------------+------------+------------+
|            | 0          | 4          |
| 301AA      | 0          | 0          |
| 301AB      | 2          | 0          |
| 302AA      | 0          | 0          |
| 303AA      | 0          | 1          |

We all know that you cannot do something like this;

sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate()

nor can you use user variables even though they get set properly

set @d1 = curdate();

sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1

So, does anyone know of a work around? I have RTFM and STFW...but to no
avail.

Thanks!

Thread
functions in ASJay Blanchard7 Jun
  • Re: functions in ASPeter Brawley7 Jun
RE: functions in ASJay Blanchard7 Jun
  • Re: functions in ASPeter Brawley8 Jun
RE: functions in ASJay Blanchard8 Jun