locked
Need help with SELECT statement. RRS feed

  • Question

  • Hi

    ,

    Give the business

    rule below, how can I return desire results below. If there is a combination Yes/No for a particular LoanId then return

    0 otherwise

    return 1. SQL 2008

    Thank you

    for your help in advance.

     

     

    IF

     

    OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL

     

    DROP TABLE #LoanInfo

    GO

    CREATE

     

    TABLE #LoanInfo

    (

    LoanId

    INT NULL,

    Response

    VARCHAR(5) NULL

    )

    GO

    INSERT

     

    #LoanInfo VALUES (123456, 'NO'), (123456, 'Yes'), (123456, 'NO'), (456123, 'NO'), (456123, 'NO');

    go

     

     

    SELECT *

     

    FROM #LoanInfo;

     

    GO

     

    -- Business rules: 1) Return 1 only all the response answer NO for a particular LoanId else return 0

    -- Desire results:

    LoanId ReturnStatus

    ------ -------------

    123456 0

    -- There is Yes

    456123 1

     

    --Testing.

     

    SELECT CASE

     

    WHEN Response = 'No' THEN 1

     

    ELSE 0

     

    END, *

     

    FROM #LoanInfo

     

    --WHERE LoanId= 123456

     

    --AND LoanModAnswerID = 2314

     

    ORDER BY 1 DESC

     

    GO

     

     

    SELECT Response

     

    FROM #LoanInfo

     

    WHERE LoanId = 123456

     

    GROUP BY Response

     

    HAVING COUNT(DISTINCT Response) >= 1

     

    GO

    Monday, November 15, 2010 7:47 PM

Answers

  • Try

    IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
     DROP TABLE #LoanInfo
    
    GO
    
    CREATE TABLE #LoanInfo (LoanId INT NULL,
    
    Response VARCHAR(5) NULL )
    
    GO
    
    INSERT
    
     
    
    #LoanInfo VALUES (123456, 'NO'), (123456, 'Yes'), (123456, 'NO'), (456123, 'NO'), (456123, 'NO');
    
    go
    
     
    
     
    
    SELECT *
    
     
    
    FROM #LoanInfo;
    
     
    
    GO
    
     
    
    -- Business rules: 1) Return 1 only all the response answer NO for a particular LoanId else return 0
    
    -- Desire results:
    
    --LoanId ReturnStatus
    
    -------- -------------
    
    --123456 0
    
    ---- There is Yes
    
    --456123 1
    
     
    
    --Testing.
    
     
    select LoanID, Case when MIN(Response) = MAX(Response) and MIN(Response) = 'NO' then 1 else 0 end as ReturnStatus
    from #LoanInfo 
    GROUP BY LoanId 
    
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by gk1393 Monday, November 15, 2010 8:11 PM
    • Marked as answer by Kalman Toth Saturday, November 20, 2010 10:49 PM
    Monday, November 15, 2010 8:06 PM
  • Hi NguyenLabckkkkk,

    Unfortunately, your code was "reformatted" beyond comprehensibility by
    the forum software. Could you please repost the code, using the Insert
    Code Block button?


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by Kalman Toth Saturday, November 20, 2010 10:49 PM
    Monday, November 15, 2010 8:09 PM
  • Why did you leave off a key from this table?

     

    CREATE TABLE Loans

    (loan_nbr INTEGER NOT NULL,

    question_nbr INTEGER NOT NULL,

    PRIMARY KEY (loan_nbr, question_nbr),

    response VARCHAR(5) NOT NULL);

     

    SELECT loan_nbr, return_status

    FROM Loans

    GROUP BY loan_nbr

    HAVING MIN(response) = MAX(response)

    AND MIN(response) = 'no';

     

     


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    • Marked as answer by Kalman Toth Saturday, November 20, 2010 10:49 PM
    Monday, November 15, 2010 8:40 PM

All replies

  • Try

    IF OBJECT_ID('Tempdb.dbo.#LoanInfo', 'u') IS NOT NULL
     DROP TABLE #LoanInfo
    
    GO
    
    CREATE TABLE #LoanInfo (LoanId INT NULL,
    
    Response VARCHAR(5) NULL )
    
    GO
    
    INSERT
    
     
    
    #LoanInfo VALUES (123456, 'NO'), (123456, 'Yes'), (123456, 'NO'), (456123, 'NO'), (456123, 'NO');
    
    go
    
     
    
     
    
    SELECT *
    
     
    
    FROM #LoanInfo;
    
     
    
    GO
    
     
    
    -- Business rules: 1) Return 1 only all the response answer NO for a particular LoanId else return 0
    
    -- Desire results:
    
    --LoanId ReturnStatus
    
    -------- -------------
    
    --123456 0
    
    ---- There is Yes
    
    --456123 1
    
     
    
    --Testing.
    
     
    select LoanID, Case when MIN(Response) = MAX(Response) and MIN(Response) = 'NO' then 1 else 0 end as ReturnStatus
    from #LoanInfo 
    GROUP BY LoanId 
    
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by gk1393 Monday, November 15, 2010 8:11 PM
    • Marked as answer by Kalman Toth Saturday, November 20, 2010 10:49 PM
    Monday, November 15, 2010 8:06 PM
  • Hi NguyenLabckkkkk,

    Unfortunately, your code was "reformatted" beyond comprehensibility by
    the forum software. Could you please repost the code, using the Insert
    Code Block button?


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by Kalman Toth Saturday, November 20, 2010 10:49 PM
    Monday, November 15, 2010 8:09 PM
  • Yes "Hugo" is correct please post the question your code using Insert Code Block for faster responses.

    Kumar


    Kaumil Gandhi
    Monday, November 15, 2010 8:12 PM
  • NAOM,

    Thank you for your help.

    Monday, November 15, 2010 8:24 PM
  • Why did you leave off a key from this table?

     

    CREATE TABLE Loans

    (loan_nbr INTEGER NOT NULL,

    question_nbr INTEGER NOT NULL,

    PRIMARY KEY (loan_nbr, question_nbr),

    response VARCHAR(5) NOT NULL);

     

    SELECT loan_nbr, return_status

    FROM Loans

    GROUP BY loan_nbr

    HAVING MIN(response) = MAX(response)

    AND MIN(response) = 'no';

     

     


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    • Marked as answer by Kalman Toth Saturday, November 20, 2010 10:49 PM
    Monday, November 15, 2010 8:40 PM