Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

sql statment to show multiple phone calls at the same time.

Discussion in 'Information Technology' started by James, Jul 28, 2006.

  1. James

    James
    Expand Collapse
    Guest

    anyone know how to write a sql statment that will list all phone calls when
    other people were using the phone too? i.e. select all phone calls when
    there was more than 1 call at the same time. i have a table with phone
    records, it has start and stop times for the calls...
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Post an example of your data as I could follow what you said.

    "James" wrote:

    > anyone know how to write a sql statment that will list all phone calls when
    > other people were using the phone too? i.e. select all phone calls when
    > there was more than 1 call at the same time. i have a table with phone
    > records, it has start and stop times for the calls...
    >
    >
    >
    >
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    "James" wrote:

    > anyone know how to write a sql statment that will list all phone calls when
    > other people were using the phone too? i.e. select all phone calls when
    > there was more than 1 call at the same time. i have a table with phone
    > records, it has start and stop times for the calls...


    A "Self Join" query can do this.

    Create a query adding the calls table to the query window TWICE. Don't join
    the tables. Access will alias the second instance by adding a "1" to the name
    - Calls and Calls1 for example.

    As a criterion on Calls1.StartTime put

    >= Calls.StartTime AND <= Calls.EndTime


    Put the same criterion, down one line so it uses OR logic, on Calls1.EndTime

    On the Primary Key of Calls1 (CallID, I'll guess) put

    > Calls.CallID


    so that the call doesn't find itself, and so that you don't see the same
    pair of calls twice (with roles reversed).

    --
    John W. Vinson[MVP]
     
  5. James

    James
    Expand Collapse
    Guest

    ID calldate CallEndDate
    1 5/12/06 2:46 PM 5/12/06 2:46 PM
    2 5/12/06 2:46 PM 5/12/06 2:51 PM
    3 5/12/06 2:51 PM 5/12/06 2:51 PM
    4 5/12/06 3:06 PM 5/12/06 3:09 PM
    5 5/12/06 3:09 PM 5/12/06 3:10 PM
    6 5/12/06 3:10 PM 5/12/06 3:10 PM
    7 5/12/06 3:25 PM 5/12/06 3:30 PM
    8 5/12/06 3:31 PM 5/12/06 3:32 PM
    9 5/12/06 3:44 PM 5/12/06 3:45 PM
    10 5/12/06 3:47 PM 5/12/06 3:50 PM
    11 5/12/06 3:53 PM 5/12/06 3:54 PM
    12 5/12/06 3:58 PM 5/12/06 3:59 PM
    13 5/12/06 3:59 PM 5/12/06 3:59 PM
    14 5/12/06 4:00 PM 5/12/06 4:00 PM
    15 5/12/06 3:58 PM 5/12/06 4:01 PM
    16 5/12/06 4:06 PM 5/12/06 4:09 PM
    17 5/12/06 4:06 PM 5/12/06 4:10 PM


    "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
    news:F60821EB-A69A-4C99-A3FF-C7ED6D3CAB73@microsoft.com...
    > Post an example of your data as I could follow what you said.
    >
    > "James" wrote:
    >
    >> anyone know how to write a sql statment that will list all phone calls
    >> when
    >> other people were using the phone too? i.e. select all phone calls when
    >> there was more than 1 call at the same time. i have a table with phone
    >> records, it has start and stop times for the calls...
    >>
    >>
    >>
    >>
     
  6. James

    James
    Expand Collapse
    Guest

    heres what my query looks like, but it doesn't seem to work correclty. it
    just repeats the same records over and over. maybe using a count() somewhere
    would help?

    SELECT calls_1.calldate, calls_1.ID
    FROM calls, calls AS calls_1
    WHERE (((calls_1.calldate)>=[calls].[calldate] And
    (calls_1.calldate)<=[calls].[callenddate]) AND ((calls_1.ID)=[calls].[id]))
    OR (((calls_1.calldate)>=[calls_1].[calldate] And
    (calls_1.calldate)<=[calls_1].[callenddate]));




    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:81AB3379-D012-4ADB-B3ED-163C7F259848@microsoft.com...
    > "James" wrote:
    >
    >> anyone know how to write a sql statment that will list all phone calls
    >> when
    >> other people were using the phone too? i.e. select all phone calls when
    >> there was more than 1 call at the same time. i have a table with phone
    >> records, it has start and stop times for the calls...

    >
    > A "Self Join" query can do this.
    >
    > Create a query adding the calls table to the query window TWICE. Don't
    > join
    > the tables. Access will alias the second instance by adding a "1" to the
    > name
    > - Calls and Calls1 for example.
    >
    > As a criterion on Calls1.StartTime put
    >
    >>= Calls.StartTime AND <= Calls.EndTime

    >
    > Put the same criterion, down one line so it uses OR logic, on
    > Calls1.EndTime
    >
    > On the Primary Key of Calls1 (CallID, I'll guess) put
    >
    >> Calls.CallID

    >
    > so that the call doesn't find itself, and so that you don't see the same
    > pair of calls twice (with roles reversed).
    >
    > --
    > John W. Vinson[MVP]
    >
    >
    >
    >
     
  7. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    See whether my Febuary, 2005 "Access Answers" column in Pinnacle
    Publication's "Smart Access" is of any use to you. You can download the
    column (and sample database) for free at
    http://www.accessmvp.com/djsteele/SmartAccess.html

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "James" <IDontWantToGetSpammed@NotaRealEmail.com> wrote in message
    news:%239y9YefeGHA.1272@TK2MSFTNGP03.phx.gbl...
    > ID calldate CallEndDate
    > 1 5/12/06 2:46 PM 5/12/06 2:46 PM
    > 2 5/12/06 2:46 PM 5/12/06 2:51 PM
    > 3 5/12/06 2:51 PM 5/12/06 2:51 PM
    > 4 5/12/06 3:06 PM 5/12/06 3:09 PM
    > 5 5/12/06 3:09 PM 5/12/06 3:10 PM
    > 6 5/12/06 3:10 PM 5/12/06 3:10 PM
    > 7 5/12/06 3:25 PM 5/12/06 3:30 PM
    > 8 5/12/06 3:31 PM 5/12/06 3:32 PM
    > 9 5/12/06 3:44 PM 5/12/06 3:45 PM
    > 10 5/12/06 3:47 PM 5/12/06 3:50 PM
    > 11 5/12/06 3:53 PM 5/12/06 3:54 PM
    > 12 5/12/06 3:58 PM 5/12/06 3:59 PM
    > 13 5/12/06 3:59 PM 5/12/06 3:59 PM
    > 14 5/12/06 4:00 PM 5/12/06 4:00 PM
    > 15 5/12/06 3:58 PM 5/12/06 4:01 PM
    > 16 5/12/06 4:06 PM 5/12/06 4:09 PM
    > 17 5/12/06 4:06 PM 5/12/06 4:10 PM
    >
    >
    > "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
    > news:F60821EB-A69A-4C99-A3FF-C7ED6D3CAB73@microsoft.com...
    >> Post an example of your data as I could follow what you said.
    >>
    >> "James" wrote:
    >>
    >>> anyone know how to write a sql statment that will list all phone calls
    >>> when
    >>> other people were using the phone too? i.e. select all phone calls when
    >>> there was more than 1 call at the same time. i have a table with phone
    >>> records, it has start and stop times for the calls...
    >>>
    >>>
    >>>
    >>>

    >
    >
     
  8. James

    James
    Expand Collapse
    Guest

    Thank You!


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:OPsfngfeGHA.3468@TK2MSFTNGP03.phx.gbl...
    > See whether my Febuary, 2005 "Access Answers" column in Pinnacle
    > Publication's "Smart Access" is of any use to you. You can download the
    > column (and sample database) for free at
    > http://www.accessmvp.com/djsteele/SmartAccess.html
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "James" <IDontWantToGetSpammed@NotaRealEmail.com> wrote in message
    > news:%239y9YefeGHA.1272@TK2MSFTNGP03.phx.gbl...
    >> ID calldate CallEndDate
    >> 1 5/12/06 2:46 PM 5/12/06 2:46 PM
    >> 2 5/12/06 2:46 PM 5/12/06 2:51 PM
    >> 3 5/12/06 2:51 PM 5/12/06 2:51 PM
    >> 4 5/12/06 3:06 PM 5/12/06 3:09 PM
    >> 5 5/12/06 3:09 PM 5/12/06 3:10 PM
    >> 6 5/12/06 3:10 PM 5/12/06 3:10 PM
    >> 7 5/12/06 3:25 PM 5/12/06 3:30 PM
    >> 8 5/12/06 3:31 PM 5/12/06 3:32 PM
    >> 9 5/12/06 3:44 PM 5/12/06 3:45 PM
    >> 10 5/12/06 3:47 PM 5/12/06 3:50 PM
    >> 11 5/12/06 3:53 PM 5/12/06 3:54 PM
    >> 12 5/12/06 3:58 PM 5/12/06 3:59 PM
    >> 13 5/12/06 3:59 PM 5/12/06 3:59 PM
    >> 14 5/12/06 4:00 PM 5/12/06 4:00 PM
    >> 15 5/12/06 3:58 PM 5/12/06 4:01 PM
    >> 16 5/12/06 4:06 PM 5/12/06 4:09 PM
    >> 17 5/12/06 4:06 PM 5/12/06 4:10 PM
    >>
    >>
    >> "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message
    >> news:F60821EB-A69A-4C99-A3FF-C7ED6D3CAB73@microsoft.com...
    >>> Post an example of your data as I could follow what you said.
    >>>
    >>> "James" wrote:
    >>>
    >>>> anyone know how to write a sql statment that will list all phone calls
    >>>> when
    >>>> other people were using the phone too? i.e. select all phone calls when
    >>>> there was more than 1 call at the same time. i have a table with phone
    >>>> records, it has start and stop times for the calls...
    >>>>
    >>>>
    >>>>
    >>>>

    >>
    >>

    >
    >
     
  9. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi James,

    Here is my interpretation of John Vinson's suggestion, using table and field
    names that you indicated:

    First possibility
    SELECT Calls.ID, Calls.calldate, Calls.callenddate
    FROM Calls, Calls AS Calls_1
    WHERE (((Calls_1.calldate)>=Calls.calldate
    And (Calls_1.calldate)<=Calls.callenddate)
    And ((Calls_1.ID)>Calls.ID))
    Or (((Calls_1.callenddate)>=Calls.calldate
    And (Calls_1.callenddate)<=Calls.callenddate)
    And ((Calls_1.ID)>Calls.ID));


    Using the data you supplied, this query returns the following records:
    ID calldate callenddate
    1 5/12/2006 2:46:00 PM 5/12/2006 2:46:00 PM
    2 5/12/2006 2:46:00 PM 5/12/2006 2:51:00 PM
    4 5/12/2006 3:06:00 PM 5/12/2006 3:09:00 PM
    5 5/12/2006 3:09:00 PM 5/12/2006 3:10:00 PM
    12 5/12/2006 3:58:00 PM 5/12/2006 3:59:00 PM
    12 5/12/2006 3:58:00 PM 5/12/2006 3:59:00 PM
    16 5/12/2006 4:06:00 PM 5/12/2006 4:09:00 PM


    Second possibility
    SELECT Calls_1.ID, Calls_1.calldate, Calls_1.callenddate
    FROM Calls, Calls AS Calls_1
    WHERE (((Calls_1.ID)>[Calls].[ID])
    AND ((Calls_1.calldate)>=[Calls].[calldate]
    And (Calls_1.calldate)<=[Calls].[callenddate]))
    OR (((Calls_1.ID)>[Calls].[ID])
    AND ((Calls_1.callenddate)>=[Calls].[calldate]
    And (Calls_1.callenddate)<=[Calls].[callenddate]));

    Using the data you supplied, this query returns the following records:
    ID calldate callenddate
    2 5/12/2006 2:46:00 PM 5/12/2006 2:51:00 PM
    3 5/12/2006 2:51:00 PM 5/12/2006 2:51:00 PM
    5 5/12/2006 3:09:00 PM 5/12/2006 3:10:00 PM
    6 5/12/2006 3:10:00 PM 5/12/2006 3:10:00 PM
    13 5/12/2006 3:59:00 PM 5/12/2006 3:59:00 PM
    15 5/12/2006 3:58:00 PM 5/12/2006 4:01:00 PM
    17 5/12/2006 4:06:00 PM 5/12/2006 4:10:00 PM



    I think I like a marriage of the two possibilities, as in this union query.
    A union query produces a read only recordset, so that might not be acceptable
    to you:

    SELECT Calls.ID, Calls.calldate, Calls.callenddate
    FROM Calls, Calls AS Calls_1
    WHERE (((Calls_1.calldate)>=[Calls].[calldate]
    And (Calls_1.calldate)<=[Calls].[callenddate])
    AND ((Calls_1.ID)>[Calls].[ID]))
    OR (((Calls_1.callenddate)>=[Calls].[calldate]
    And (Calls_1.callenddate)<=[Calls].[callenddate])
    AND ((Calls_1.ID)>[Calls].[ID]))

    UNION

    SELECT Calls_1.ID, Calls_1.calldate, Calls_1.callenddate
    FROM Calls, Calls AS Calls_1
    WHERE (((Calls_1.ID)>[Calls].[ID])
    AND ((Calls_1.calldate)>=[Calls].[calldate]
    And (Calls_1.calldate)<=[Calls].[callenddate]))
    OR (((Calls_1.ID)>[Calls].[ID])
    AND ((Calls_1.callenddate)>=[Calls].[calldate]
    And (Calls_1.callenddate)<=[Calls].[callenddate]));


    Using the data you supplied, this last union query returns the following
    records:
    ID calldate callenddate
    1 5/12/2006 2:46:00 PM 5/12/2006 2:46:00 PM
    2 5/12/2006 2:46:00 PM 5/12/2006 2:51:00 PM
    3 5/12/2006 2:51:00 PM 5/12/2006 2:51:00 PM
    4 5/12/2006 3:06:00 PM 5/12/2006 3:09:00 PM
    5 5/12/2006 3:09:00 PM 5/12/2006 3:10:00 PM
    6 5/12/2006 3:10:00 PM 5/12/2006 3:10:00 PM
    12 5/12/2006 3:58:00 PM 5/12/2006 3:59:00 PM
    13 5/12/2006 3:59:00 PM 5/12/2006 3:59:00 PM
    15 5/12/2006 3:58:00 PM 5/12/2006 4:01:00 PM
    16 5/12/2006 4:06:00 PM 5/12/2006 4:09:00 PM
    17 5/12/2006 4:06:00 PM 5/12/2006 4:10:00 PM



    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "James" wrote:

    > heres what my query looks like, but it doesn't seem to work correclty. it
    > just repeats the same records over and over. maybe using a count() somewhere
    > would help?
    >
    > SELECT calls_1.calldate, calls_1.ID
    > FROM calls, calls AS calls_1
    > WHERE (((calls_1.calldate)>=[calls].[calldate] And
    > (calls_1.calldate)<=[calls].[callenddate]) AND ((calls_1.ID)=[calls].[id]))
    > OR (((calls_1.calldate)>=[calls_1].[calldate] And
    > (calls_1.calldate)<=[calls_1].[callenddate]));
    >
    >
    >
    >
    > "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    > news:81AB3379-D012-4ADB-B3ED-163C7F259848@microsoft.com...
    > > "James" wrote:
    > >
    > >> anyone know how to write a sql statment that will list all phone calls
    > >> when
    > >> other people were using the phone too? i.e. select all phone calls when
    > >> there was more than 1 call at the same time. i have a table with phone
    > >> records, it has start and stop times for the calls...

    > >
    > > A "Self Join" query can do this.
    > >
    > > Create a query adding the calls table to the query window TWICE. Don't
    > > join
    > > the tables. Access will alias the second instance by adding a "1" to the
    > > name
    > > - Calls and Calls1 for example.
    > >
    > > As a criterion on Calls1.StartTime put
    > >
    > >>= Calls.StartTime AND <= Calls.EndTime

    > >
    > > Put the same criterion, down one line so it uses OR logic, on
    > > Calls1.EndTime
    > >
    > > On the Primary Key of Calls1 (CallID, I'll guess) put
    > >
    > >> Calls.CallID

    > >
    > > so that the call doesn't find itself, and so that you don't see the same
    > > pair of calls twice (with roles reversed).
    > >
    > > --
    > > John W. Vinson[MVP]
    > >
    > >
    > >
    > >

    >
    >
    >
     

Share This Page