Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: SQL Server 2008. sort and insert custom rows in result

    I have table for comunication between clients and operaters, which contains phone, id (1=client, 2=operater) date, message... and some other data.

    I need to make stored procedure which will sort table like this

    number1 operaters message
    number1 clients message
    number1 clients message
    number1 operaters message
    number1 *no message*
    .
    .
    .

    So, bassically, I need to find first message sent by operater and then client messages before next operaters message. If after one operaters message there is no clients answer, I need to insert row like "no clients anwer" or something like that.

    And that is large table, so will be good if it is less steps.

    I know how to order by multiple columns, I know how to find first operaters message (select min time where id...) but I don't know how to insert those rows where needed.

    Thanks in advance for help.

    And yeah, it's MSSQL.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You will have to give more information. Can you show us the code you have so far? Give some example data for the involved tables, and for the example data the required result.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2011
    Posts
    71
    That's right we can't understand well what do you intend to do exactly.
    But i guess that you need to use a Switch case/or IF Conditions clause with where statement ,and Select Top* Clause
    Thanks

  4. #4
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Can you please provide your code for more clarity. May be case or if else will serve the purpose.

  5. #5
    Join Date
    Oct 2011
    Posts
    3
    Hi, sorry for being vague, now I'll start from beginning and try to be more detailed. Here is the deal:

    I have table for comunication between clients and operators, which contains phone, id (1=client, 2=operater) date, request, response... and some other (non important) rows.

    It looks like this:

    Code:
    PhoneNumber     Date       Request    Response             ID
    0123456          1.1.2011  some_txt    some_other_txt    1
    .
    .
    .
    .
    
    and so on....

    I need to get this:

    PhoneNumber Request Date Response Date

    where last two rows are from clients, and two before them are operators.

    So, I need to make it so I have first operators message with request for some number (by date) and response in one row (response is first message from client after that date).

    I aimed to get that by left joining table to itself on number, but so if there is no response within wanted criteria, last two rows will be NULL (which I want), but I cant figure out how to join first value by date for every phone number with first response after that date from "right" table.

    I tried to select distinct rows where only PhoneNumber is distinct, but that wasn't so successful. And if I somehow manage to do that, how to join it with first row from users messages...

    Of course, even if I manage this, probably it isn't the best way.

    So, every idea is welcome.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •