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 …
    surya42, Jul 19, 2011
    #1

  2. satya Moderator

    Welcome to the forums.
    OK what was the problem ..did you attempt to write the TSQL in this case.
    satya, Aug 3, 2011
    #2

  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. :)
    FrankKalis, Aug 3, 2011
    #3

  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'
    Madhivanan, Aug 9, 2011
    #4

Share This Page