"tarheels4025" <tarheels4***@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 confused on how I
> would
> write this query or if there is even a way.  Any help would be greatly
> appreciated.  Thank you.

>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 confused on how I would
>write this query or if there is even a way.  Any help would be greatly
>appreciated.  Thank you.

"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
> <tarheels4***@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_dates
> >occured     between a start_date and end_date.  I'm confused 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'.