Use Code from another part of union

summoningknoweldge 1 Reputation point
2022-03-10T15:00:09.797+00:00

So my data looks like this.

181937-image.png

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:

181966-image.png

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2022-03-10T15:32:08.243+00:00

    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:

    181958-image.png

    0 comments No comments

  2. LiHong-MSFT 10,046 Reputation points
    2022-03-11T01:56:29.587+00:00

    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.

    0 comments No comments