Use Code from another part of union

Question

So my data looks like this.

I get it from 2 unions kind of like this and then select from both and do my calulations from the main query x.

Select PatientName, DateSaved, DatedRan, Code, CodeName, Rank
from
( Select Sam, 01-01-2020, 01-01-2020, C2020, 3 )

Union All

( Select
Sam, 01-01-2020, 01-01-2020, D2020, 1
Sam, 01-01-2020, 01-02-2020, D2020, 2
Sam, 01-01-2020, 01-03-2020, D2020, 2
)

) x

So what I want to achieve is something like this:

If the Date ran <> Date saved then show the Code like 'C2020'. But I am not able to achieve this.

Answer 1

Try this:

DECLARE @T1 TABLE (  
	PatientName varchar(20),   
	DateSaved date,   
	DatedRan date,   
	Code varchar(20),   
	CodeName varchar(20),   
	[Rank] int  
);  
  
INSERT INTO @T1  
Select 'Sam', '01-01-2020', '01-01-2020', 'C2020', 'Casual', 3;  
  
DECLARE @T2 TABLE (  
	PatientName varchar(20),   
	DateSaved date,   
	DatedRan date,   
	Code varchar(20),   
	CodeName varchar(20),   
	[Rank] int  
);  
  
INSERT INTO @T2 VALUES  
('Sam', '01-01-2020', '01-01-2020', 'D2020', 'Serious', 1),  
('Sam', '01-01-2020', '01-02-2020', 'D2020', 'Serious', 2),  
('Sam', '01-01-2020', '01-03-2020', 'D2020', 'Serious', 2);  
  
Select PatientName, DateSaved, DatedRan, Code, CodeName, [Rank]  
from @T1  
Union All  
Select t2.PatientName,   
	   t2.DateSaved,   
	   t2.DatedRan,   
	   CASE WHEN t2.DateSaved <> t2.DatedRan THEN t1.Code ELSE t2.Code END AS Code,   
	   CASE WHEN t2.DateSaved <> t2.DatedRan THEN t1.CodeName ELSE t2.CodeName END AS CodeName,   
	   t2.[Rank]  
from @T2 AS t2  
INNER JOIN @T1 AS t1 ON t2.PatientName = t1.PatientName;  

Output:

Answer 2

Hi @summoningknoweldge
According to your requirement description, I think you can have a try on CASE WHEN statement.
Check this query:

;WITH CTE AS  
(  
 --Put your code here   
 SELECT PatientName, DateSaved, DatedRan, Code, CodeName, [Rank]   
 FROM ...  
 UNION  
 SELECT  
 FROM ...  
)  
SELECT PatientName, DateSaved, DatedRan,  
       CASE WHEN DateSaved<>DatedRan THEN 'C2020' ELSE Code END AS Code,  
	   CASE WHEN DateSaved<>DatedRan THEN 'Casual' ELSE CodeName END AS CodeName,  
       [Rank]  
FROM CTE  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.