Indentical case when, different then

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.

Answer 1

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

Answer 2

User-1826049516 posted

Thanks! I've used APPLY before.