Posted by DickChristoph on 03/24/06 01:10

Hi Jerball

Your only real problem is that "dateadd(d,-3,getdate())" run right now
3/23/3006 at 5:05pm returns
3/20/3006 at 5:05pm so those dated "3/20/2006" are excluded.

"cast(convert(varchar(8), getdate() -3, 112) as datetime)" will return
"3/20/2006" if run today.

I also added a order by to your query.

So the query is:

select search_string, CONVERT(CHAR(11),search_date,106) as search_date,
count(search_string) as numOccurances
from searches
where search_date >= cast(convert(varchar(8), getdate() -3, 112) as
datetime)
group by search_date, search_string
order by search_date, search_string

and I get
search_string search_date numOccurances
------------------- ----------- -------------
dogs 20 Mar 2006 4
pigs 20 Mar 2006 2
dogs 21 Mar 2006 8
pigeons 22 Mar 2006 5
pigs 22 Mar 2006 3

Which I believe is what you want.
--
-Dick Christoph
"jerball" <jerball@gmail.com> wrote in message
news:1143153833.707877.117960@u72g2000cwu.googlegroups.com...
>A client wants to keep track of the number of searches for keywords in
> a date range. So, I'm storing each occurance of a search in a table.
>
> The columns are:
>
> PK: id
> search_string
> search_date
>
> I'm trying to wrap my head around how I would select the number of
> occurances for each string, divided by days. The desired result would
> look something like:
>
> search_date search_string numOccurances
> ----------------------------------------------------------
> March 20 dogs 4
> March 20 pigs 2
> March 21 dogs 8
> March 22 pigs 3
> March 22 pigeons 5
>
> I've tried a query like:
>
>
> select search_string, CONVERT(CHAR(11),search_date,106) as search_date,
> count(search_string) as numOccurances
> from searches
> where search_date >= dateadd(d,-3,getdate())
> group by search_date, search_string
>
>
> but it doesn't give me the desired results. I'm sure I'm just looking
> at it the wrong way.
>
> Suggestions?
>
> Thanks!
>

[Back to original message]