Jay,
>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?
How about PREPARE?
PB
-----
Jay Blanchard wrote:
> 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!
>
>
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.3/358 - Release Date: 6/7/2006
