"Hulicat" <dennis_A_white@yahoo.com> wrote in message
news:1168881101.922067.170520@l53g2000cwa.googlegroups.com...

Tom Moreau wrote:
> OK, I'm assuming, then, that there will be one dialling and one line
> available per trunk. Also, if the 'dialling and 'line' search words are
> at
> the beginning of the string, you don't need the leading % sign, since it
> doesn't perform well. I think your solution is:
>
> select
> Trunk
> , min (case when status like 'dialling%' then dtime) end startcall
> , min (case when status like 'line%' then dtime) end endcall
> from
> MyTable
> group by
> Trunk
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Hulicat" <dennis_A_white@yahoo.com> wrote in message
> news:1168703618.145821.231940@v45g2000cwv.googlegroups.com...
> Thanks for your reply.
>
> Sample data looks like this.
>
> Trunk dtime status
> 1 12/7/05 12:31 dilaing 2122223456
> 9 12/7/05 12:32 open
> 6 12/7/05 12:33 unavailable
> 1 12/7/05 12:34 line avaliable
>
> I would like the results to look like this:
>
> Trunk startcall endcall
> 1 12/7/05 12:31 12/7/05 12:34
>
>
>
> status '%dialing%' = startcall
> status '%line%' = endcall
>
>
> The data is coming from a flat file that I am importing into SQL. I
> have not figured out a way to parse out the status to different
> columns. Therefore, I am trying to acheive the goal via a query.
>
> If there is a better way to do this please let me know.
>
> Thanks,
> Hulicat
> Tom Moreau wrote:
> > The second SELECT doesn't refer to trunk at all. Is there a
> > relationship
> > between the dialling data and the line data per trunk? Please post your
> > DDL
> > + INSERT statements of sample data + expected results.
> >
> > --
> > Tom
> >
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > .
> > "Hulicat" <dennis_A_white@yahoo.com> wrote in message
> > news:1168646242.435874.292990@l53g2000cwa.googlegroups.com...
> > Hello,
> > I am trying to query out of the same feild for separate results.
> >
> > The query looks like this:
> > select trunk, start as StartCall from table1 where status like
> > '%dialing%'
> >
> > That works, however; I also want to get this statment into the same
> > results.
> >
> > select start as endcall from table1 where status like '%line%'
> >
> > I have not found a way to incorporate the last statement with erroring
> > or getting the desored results.
> >
> > What function should I be using?
> >
> > Results should be
> >
> > Trunk Startcall Endcall
> >
> > Any help would be greatly appreciated.
> >
> > Thanks,
> > H