none
case statement

    Question

  • not getting the result as expected 

    when Qualified = 2 it should 1 for Level1BonusQualified, and Level12BonusQualified, in case it should 1 be for all levels 

    SELECT 
    NAME,
               
    WHEN GR8_Qualified IS NULL   THEN   '0' ELSE  GR8_FrontCount END AS 'noorder'
    CASE WHEN Qualified = 1  THEN   '1'  END AS Level1BonusQualified,
    CASE WHEN Qualified = 2  THEN '1'  END AS  Level1BonusQualified,
    CASE WHEN Qualified = 2  THEN '1'  END AS  Level2BonusQualified,
    CASE WHEN Qualified = 3  THEN '1'  END AS  Level1BonusQualified,
    CASE WHEN Qualified = 3  THEN '1'  END AS  Level2BonusQualified,
    CASE WHEN Qualified = 3  THEN '1'  END AS  Level3BonusQualified,
    (
    SELECT 
    name,

    Qualified,

    ''AS Level1BonusQualified,
    ''AS Level2BonusQualified,
    ''AS Level3BonusQualified
     FROM dbo.test123 MP
    )D

    Monday, April 22, 2019 11:28 PM

All replies

  • Your code is a bit confuse.

    Do an line for each column alias like:

    CASE WHEN Qualified = 1  THEN '1' WHEN Qualified = 2 THEN '2' ELSE '3' END AS Level1BonusQualified,
    CASE WHEN Qualified = 2  THEN '1' WHEN Qualified = 1 THEN '2' ELSE '3' END AS  Level2BonusQualified,


    Tuesday, April 23, 2019 1:29 AM
  • Hi  Vanchan007,

    I don't know your expecting output, I assume that you might want to get below result, if this is not what you want, please correct me and inform me more detailed information(your sample and expecting output)

    create table #test123(name varchar(20), Qualified int) insert into #test123 values ('a', 1), ('a', 2), ('b',1), ('c',1), ('c',2), ('c',3) select NAME1, [1] AS Level1BonusQualified , [2] AS Level2BonusQualified , [3] AS Level3BonusQualified from (SELECT NAME, NAME AS NAME1 , CASE WHEN Qualified = 1 THEN '1' WHEN Qualified = 2 THEN '2' ELSE '3' END AS Level1BonusQualified from ( SELECT name, Qualified FROM #test123 MP )D ) P PIVOT ( COUNT(NAME) FOR Level1BonusQualified IN ([1], [2], [3] ) ) AS PivotTable

    /* 
    NAME1                Level1BonusQualified Level2BonusQualified Level3BonusQualified
    -------------------- -------------------- -------------------- --------------------
    a                    1                    1                    0
    b                    1                    0                    0
    c                    1                    1                    1
    (3 rows affected)
    */

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, April 23, 2019 3:29 AM
  • Above one is the out output we are looking

    Tuesday, April 23, 2019 8:07 PM
  • Hi  Vanchan007

    You could try below query to see whether it works or not

    create  table #test123(nou int,name varchar(20), Qualified int)
    insert into #test123 values (1,'a', 0), (2,'s', 1), (3,'d',2), (4,'f',3), (5,'g',4)
    
    
    SELECT 
    nou,NAME,Qualified,
     CASE WHEN Qualified = 1  THEN '1' WHEN Qualified = 2 THEN '1'  WHEN Qualified = 3 THEN '1'  WHEN Qualified = 4 THEN '1' ELSE '0' END AS Level1BonusQualified  ,        
    CASE WHEN  Qualified = 2 THEN '1' WHEN Qualified = 3 THEN '1'  WHEN Qualified = 4 THEN '1' ELSE '0'  END AS Level2BonusQualified ,
    CASE WHEN  Qualified = 3 THEN '1'  WHEN Qualified = 4 THEN '1' ELSE '0' END AS Level3BonusQualified   ,
       case WHEN Qualified = 4 THEN '1' ELSE '0' end AS Level4BonusQualified
    
    from 
    
    (
    SELECT 
    nou,NAME,Qualified,
    
    ''AS Level1BonusQualified,
    ''AS Level2BonusQualified,
    ''AS Level3BonusQualified
     FROM #test123 
    )D

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 24, 2019 7:33 AM
  • Got it thank you sir.
    Thursday, April 25, 2019 1:55 AM
  • Hi Vanchan007

    Could you please tell us if your question has been resolved. If so, in order to close the thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 25, 2019 3:25 AM