Group by Max Count ? how to do query?

Can anyone tell me how to do this query please?
3 fields. provider, site, visitdate. Given a date range for
visitdate,
Return all providers that have at least one visit in that date range
and also display which site has the most visitdates for EACH provider
for all records (not date constrained).
Since a provider can have visits at multiple sites I only want to
return the site where they have the most visits.
THANKS A LOTSome DDL (i.e. create table statements) would be useful.
Thomas
<bringmewater@.gmail.com> wrote in message
news:1114630228.267194.170960@.g14g2000cwa.googlegroups.com...
> Can anyone tell me how to do this query please?
> 3 fields. provider, site, visitdate. Given a date range for
> visitdate,
> Return all providers that have at least one visit in that date range
> and also display which site has the most visitdates for EACH provider
> for all records (not date constrained).
> Since a provider can have visits at multiple sites I only want to
> return the site where they have the most visits.
> THANKS A LOT
>|||Try,
create view v1
as
select
provider,
site,
count(*) as number_of_visit
from
t1
where
visitdate >= convert(char(8), @.sd, 112) and visitdate < dateadd(day, 1,
convert(char(8), @.ed, 112))
group by
provider,
site
having
count(*) > 0
go
select
provider,
site,
number_of_visit
from
v1 as a
where
number_of_visit = (select max(b.number_of_visit) from v1 as b where
b.provider = a.provider)
go
AMB
"bringmewater@.gmail.com" wrote:

> Can anyone tell me how to do this query please?
> 3 fields. provider, site, visitdate. Given a date range for
> visitdate,
> Return all providers that have at least one visit in that date range
> and also display which site has the most visitdates for EACH provider
> for all records (not date constrained).
> Since a provider can have visits at multiple sites I only want to
> return the site where they have the most visits.
> THANKS A LOT
>