SQL Server Performance

stored procedure to read a table - create output table

Discussion in 'SQL Server 2008 General Developer Questions' started by surya42, Jul 19, 2011.

  1. surya42 New Member

    I have a table which contains many records for a RequestID ,
    Each request has a BookingID .

    Aim : to write a stored procedure and create an output table
    which contains a RequestID and Status ( If any request has a Status of ā€˜Cā€™ then Status
    overrides any other status other status is whatever is on input.

    Input Table

    Request ID Booking ID Status
    001 50 NC
    001 51 C
    001 52 NC
    001 53 C
    002 54 NC
    002 55 NC
    002 56 NC
    003 57 C
    004 58 NC
    004 59 NC
    004 60 NC

    Output Table should produce this :

    Request ID Status
    001 C
    002 NC
    003 C
    004 NC

    Please advise ā€¦
  2. satya Moderator

    Welcome to the forums.
    OK what was the problem ..did you attempt to write the TSQL in this case.
  3. FrankKalis Moderator

    I'm fairly sure some important aspects are missing in this request, but given this sample data this will give you the expected result set:

    Code:
    SELECT DISTINCT
        T.RequestID, T.Status
    FROM
        table T
    WHERE
        T.Status = 'C'
    UNION
    SELECT DISTINCT
        T1.RequestID, T1.Status
    FROM
        table T1
    WHERE
        T1.Status <> 'C' AND
        NOT EXISTS (SELECT *
                FROM
                    table T2
                WHERE
                    T1.RequestID = T2.RequestID AND
                    T2.Status = 'C')
    If we have more information, we could provide probably a "better" version. :)
  4. Madhivanan Moderator

    Another version

    Code:
    select distinct Request_ID,Status from table
    where Status='C'
    union
    select Request_ID,'NC' from table
    group by Request_ID
    having MAX(status)=MIN(status) and MIN(status)='NC'

Share This Page