Count number of visitor

I have a database of visitors as follows:
Name; Begdate, Enddate
I would like to count the number of visitors every day over a six month
period.
Is there an easy way to do this using SQL?See if this helps:
-- count number of orders every 6 months
use northwind
go
select
colA,
count(*)
from
(
select
convert(char(4), orderdate, 112) + ' - ' + ltrim((month(orderdate) % 2) + 1)
from
orders
) as t(colA)
group by
colA
order by
colA
go
AMB
"Mustapha Amrani" wrote:

> I have a database of visitors as follows:
> Name; Begdate, Enddate
> I would like to count the number of visitors every day over a six month
> period.
> Is there an easy way to do this using SQL?
>
>|||Thanks for the reply, I am afraid that is not what I need. I have a
programme over 6 month period, I have visitors that come to this programme
for varying periods. I would like to give the administrator a table of
number of visitors for each day of the programme so that she can do the room
booking and monitor the period were the programme is over booked.
At the moment I am itirating over the period and counting the number of
visitors for each day, I was wondering if there is a better way to do this
in SQL.
Jan1; 23
Jan2; 36
jan 3; 31
jan 4; 27
etc...
Thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:909107DE-5362-4A90-A40E-31E0CF8F9C77@.microsoft.com...
> See if this helps:
> -- count number of orders every 6 months
> use northwind
> go
> select
> colA,
> count(*)
> from
> (
> select
> convert(char(4), orderdate, 112) + ' - ' + ltrim((month(orderdate) % 2) +
> 1)
> from
> orders
> ) as t(colA)
> group by
> colA
> order by
> colA
> go
>
> AMB
> "Mustapha Amrani" wrote:
>|||On Wed, 2 Feb 2005 17:20:36 -0000, Mustapha Amrani wrote:

>I have a database of visitors as follows:
>Name; Begdate, Enddate
>I would like to count the number of visitors every day over a six month
>period.
>Is there an easy way to do this using SQL?
>
Hi Mustapha,
First, create a calendar table. See www.aspfaq.com/2519.
When your calendar table is ready, try if this query works for you:
SELECT c.dt, COUNT(*)
FROM Calendar AS c
LEFT JOIN Visitors AS v
ON v.Begdate <= c.dt
AND v.Enddate >= c.dt
GROUP BY c.dt
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Woops, I forgot to add the WHERE clause.
The query should have been:
SELECT c.dt, COUNT(*)
FROM Calendar AS c
LEFT JOIN Visitors AS v
ON v.Begdate <= c.dt
AND v.Enddate >= c.dt
WHERE c.dt >= '20050101' -- If the period you need to report
AND c.dt < '20050701' -- is the first 6 months of 2005
GROUP BY c.dt
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Then Hugo's post should do it.
AMB
"Mustapha Amrani" wrote:

> Thanks for the reply, I am afraid that is not what I need. I have a
> programme over 6 month period, I have visitors that come to this programme
> for varying periods. I would like to give the administrator a table of
> number of visitors for each day of the programme so that she can do the ro
om
> booking and monitor the period were the programme is over booked.
> At the moment I am itirating over the period and counting the number of
> visitors for each day, I was wondering if there is a better way to do this
> in SQL.
> Jan1; 23
> Jan2; 36
> jan 3; 31
> jan 4; 27
> etc...
> Thanks
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:909107DE-5362-4A90-A40E-31E0CF8F9C77@.microsoft.com...
>
>sql