locked
Indentical case when, different then RRS feed

  • Question

  • User-1826049516 posted

    Hi,

    I am repeating the exact same case when sequence but to result different values for 2 different columns. There must be a better way?

    case
    	when u.[User_ID] = @UserID or exists
    	(
    		select		1
    		from		tbl_Users ux join
    						[(local)\ISYS].Intelligent.dbo.Employees ex on ex.EMP_Number collate database_default = ux.User_Number left join
    						[(local)\ISYS].Intelligent.dbo.Ethnic etx on etx.ETH_Id = ex.EMP_ETH_Id
    		where		ux.[User_ID] = @UserID and etx.ETH_Description = 'Administrator'
    	)
    	then a.ACT_Status_Name
    	else 'Out'
    end Status_Name
    case
    	when u.[User_ID] = @UserID or exists
    	(
    		select		1
    		from		tbl_Users ux join
    						[(local)\ISYS].Intelligent.dbo.Employees ex on ex.EMP_Number collate database_default = ux.User_Number left join
    						[(local)\ISYS].Intelligent.dbo.Ethnic etx on etx.ETH_Id = ex.EMP_ETH_Id
    		where		ux.[User_ID] = @UserID and etx.ETH_Description = 'Administrator'
    	)
    	then 'Allowance: ' + cast(h.Allowance as varchar) + ' | Remaining: ' + cast(h.Remaining as varchar)
    	else 'Not available'
    end Holiday
    

    Thanks.

    Saturday, February 17, 2018 2:57 PM

Answers

  • User452040443 posted

    Hi,

    Try something like this:

    select 
        case ca.Case_Result 
            when 1 then a.ACT_Status_Name
    	when 2 else 'Out'
        end Status_Name,
        case ca.Case_Result
            when 1 then 'Allowance: ' + cast(h.Allowance as varchar) + ' | Remaining: ' + cast(h.Remaining as varchar)
            else 'Not available'
        end Holiday
    from MyTableU as u
    inner join MyTableA as a on a.IdAbc = u.IdAbc inner join MyTableH as h on h.IdDef = u.IdDef
    cross apply ( select case when u.[User_ID] = @UserID or exists ( select 1 from tbl_Users ux join [(local)\ISYS].Intelligent.dbo.Employees ex on ex.EMP_Number collate database_default = ux.User_Number left join [(local)\ISYS].Intelligent.dbo.Ethnic etx on etx.ETH_Id = ex.EMP_ETH_Id where ux.[User_ID] = @UserID and etx.ETH_Description = 'Administrator' ) then 1 else 2 end Case_Result ) as ca

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 19, 2018 1:09 PM

All replies

  • User452040443 posted

    Hi,

    Try something like this:

    select 
        case ca.Case_Result 
            when 1 then a.ACT_Status_Name
    	when 2 else 'Out'
        end Status_Name,
        case ca.Case_Result
            when 1 then 'Allowance: ' + cast(h.Allowance as varchar) + ' | Remaining: ' + cast(h.Remaining as varchar)
            else 'Not available'
        end Holiday
    from MyTableU as u
    inner join MyTableA as a on a.IdAbc = u.IdAbc inner join MyTableH as h on h.IdDef = u.IdDef
    cross apply ( select case when u.[User_ID] = @UserID or exists ( select 1 from tbl_Users ux join [(local)\ISYS].Intelligent.dbo.Employees ex on ex.EMP_Number collate database_default = ux.User_Number left join [(local)\ISYS].Intelligent.dbo.Ethnic etx on etx.ETH_Id = ex.EMP_ETH_Id where ux.[User_ID] = @UserID and etx.ETH_Description = 'Administrator' ) then 1 else 2 end Case_Result ) as ca

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 19, 2018 1:09 PM
  • User-1826049516 posted

    Thanks! I've used APPLY before.

    Wednesday, February 21, 2018 10:37 AM