Counting Date Query

I have a table called sv_call_log
The table contains columns row_id, card_num, start_date, end_date.
This table logs calls.
The start_date is when the call begins and end_date is when the call ends.
They are both fromatted as follows EX. 2004-09-20 15:55:29.247
We currently have 4 lines avliable for people to call in out.
Does anyone know how to write a query to show when more than 3 lines are
used at the same time? The query would have to see if 3 or more start_dates
occured between a start_date and end_date. I'm on how I would
write this query or if there is even a way. Any help would be greatly
appreciated. Thank you.Having the entire table DDL would be very useful for us to provide
meaningful help. Otherwise we are just guessing...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:2EBC3CD5-95D8-49CF-B59B-49EBB78E508C@.microsoft.com...
>I have a table called sv_call_log
> The table contains columns row_id, card_num, start_date, end_date.
> This table logs calls.
> The start_date is when the call begins and end_date is when the call ends.
> They are both fromatted as follows EX. 2004-09-20 15:55:29.247
> We currently have 4 lines avliable for people to call in out.
> Does anyone know how to write a query to show when more than 3 lines are
> used at the same time? The query would have to see if 3 or more
> start_dates
> occured between a start_date and end_date. I'm on how I
> would
> write this query or if there is even a way. Any help would be greatly
> appreciated. Thank you.|||Hmm. Interesting.
Hopefully start_date and end_date are actually of datetime datatype,
and the formatting you describe is simply how they are displayed.
I think this will show you any call that starts while at least 2 other
calls are already in progress. If a fourth call starts there will be
rows for both the third and fourth.
--Each call matches itself, possibly others
SELECT X.row_id,
count(Y.row_id) as OverlapLevel,
start_date as start_overlap,
min(end_date) as end_overlap
FROM CallLog as X
JOIN CallLog as Y
ON X.start_date BETWEEN Y.start_date AND Y.end_date
GROUP BY X.row_id
HAVING count(Y.row_id) > 3
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 12:03:02 -0700, tarheels4025
<tarheels4025@.discussions.microsoft.com> wrote:

>I have a table called sv_call_log
>The table contains columns row_id, card_num, start_date, end_date.
>This table logs calls.
>The start_date is when the call begins and end_date is when the call ends.
>They are both fromatted as follows EX. 2004-09-20 15:55:29.247
>We currently have 4 lines avliable for people to call in out.
>Does anyone know how to write a query to show when more than 3 lines are
>used at the same time? The query would have to see if 3 or more start_date
s
>occured between a start_date and end_date. I'm on how I would
>write this query or if there is even a way. Any help would be greatly
>appreciated. Thank you.|||Ron,
When I run the query it throws back
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Start_Date'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'End_Date'.
"Roy Harvey" wrote:

> Hmm. Interesting.
> Hopefully start_date and end_date are actually of datetime datatype,
> and the formatting you describe is simply how they are displayed.
> I think this will show you any call that starts while at least 2 other
> calls are already in progress. If a fourth call starts there will be
> rows for both the third and fourth.
> --Each call matches itself, possibly others
> SELECT X.row_id,
> count(Y.row_id) as OverlapLevel,
> start_date as start_overlap,
> min(end_date) as end_overlap
> FROM CallLog as X
> JOIN CallLog as Y
> ON X.start_date BETWEEN Y.start_date AND Y.end_date
> GROUP BY X.row_id
> HAVING count(Y.row_id) > 3
> Roy Harvey
> Beacon Falls, CT
> On Thu, 22 Jun 2006 12:03:02 -0700, tarheels4025
> <tarheels4025@.discussions.microsoft.com> wrote:
>
>|||Sorry about that.
SELECT X.row_id,
count(Y.row_id) as OverlapLevel,
X.start_date as start_overlap,
min(Y.end_date) as end_overlap
FROM CallLog as X
JOIN CallLog as Y
ON X.start_date BETWEEN Y.start_date AND Y.end_date
GROUP BY X.row_id
HAVING count(Y.row_id) > 3
Roy
On Thu, 22 Jun 2006 12:55:02 -0700, tarheels4025
<tarheels4025@.discussions.microsoft.com> wrote:

>Ron,
>When I run the query it throws back
>Server: Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'Start_Date'.
>Server: Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'End_Date'.