Answered by:
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.
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 caHope 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 caHope 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