Help on TSQL query

Question

Hi,

From below tables I would to like to write TSQL query to get the below results, please help on it.

IF OBJECT_ID('tempdb..#NetWorthAnticipated') IS NOT NULL
DROP TABLE #NetWorthAnticipated;
SELECT *
INTO #NetWorthAnticipated
FROM
(
VALUES
(0, 25000),
(25001, 50000),
(50001, 200000),
(200001, 500000),
(500001, 1000000),
(1000001, 3000000),
(3000001, 100000000)
) AS a (MinRange, MaxRange);

IF OBJECT_ID('tempdb..#Networth') IS NOT NULL
DROP TABLE #Networth;
SELECT *
INTO #Networth
FROM
(VALUES
( '$25,000 and under' ),
( '$1,000,001 - $3,000,000' ),
( '$200,001 - $500,000' ),
( '$25000 - $50000' ),
( '$500,001 - $1,000,000' ),
( '$50,001 - $200,000' ),
( '$50,001-200,000' )
) AS b (NetWorth);

SELECT * FROM #Networth
SELECT * FROM #NetWorthAnticipated

From the above tables need the below Results :

Answer 1

Hi,@Vivaan
Please also check:

;WITH CTE1 AS  
(  
 SELECT NetWorth,  
        CASE WHEN CHARINDEX ('and under', NetWorth) = 0  
             THEN REPLACE (NetWorth,' ', '')   
  ELSE '$0'+'-'+STUFF(NetWorth,CHARINDEX ('and under', NetWorth),9,'')   
  END AS ModifiedNetWorth  
 FROM #Networth   
),CTE2 AS  
(  
 SELECT '$'+CAST(MinRange AS VARCHAR)+' - '+'$'+CAST(MaxRange AS VARCHAR) AS AnticipatedNetWorth,  
         CAST(MinRange AS VARCHAR)+' - '+ CAST(MaxRange AS VARCHAR) AS RangeNetWorth  
 FROM #NetWorthAnticipated  
)  
SELECT NetWorth,AnticipatedNetWorth  
FROM CTE1 A LEFT JOIN CTE2 B   
   ON ROUND(REPLACE(SUBSTRING(ModifiedNetWorth,2,CHARINDEX ('-', ModifiedNetWorth)-2),',','')/100,0)*100 =ROUND(LEFT(RangeNetWorth,CHARINDEX ('-', RangeNetWorth)-2 )/100,0)*100  
     

Best regards,
LiHong

Answer 2

Hi,@Vivaan
Please check this:

;WITH CTE1 AS  
(  
 SELECT NetWorth,  
        CASE WHEN CHARINDEX ('and under', NetWorth) = 0  
             THEN REPLACE (NetWorth,',', '')   
			 ELSE '$0-$25000'  
			 END AS ModifiedNetWorth  
 FROM #Networth   
),  
CTE2 AS(SELECT NetWorth, REPLACE (ModifiedNetWorth,' ','') ModifiedNetWorth FROM CTE1  ),  
CTE3 AS(SELECT NetWorth, REPLACE (ModifiedNetWorth,'$','') ModifiedNetWorth FROM CTE2  ),  
CTE4 AS  
(  
 SELECT '$'+CAST(MinRange AS VARCHAR)+' - '+'$'+CAST(MaxRange AS VARCHAR) AS AnticipatedNetWorth  
         ,CAST(MinRange AS VARCHAR)+' - '+ CAST(MaxRange AS VARCHAR) AS RangeNetWorth  
 FROM #NetWorthAnticipated  
)  
SELECT NetWorth,AnticipatedNetWorth   
FROM CTE3 A LEFT JOIN CTE4 B   
ON LEFT(ModifiedNetWorth,CHARINDEX ('-', ModifiedNetWorth)-1) =LEFT(RangeNetWorth,CHARINDEX ('-', RangeNetWorth)-2 )  
OR RIGHT(ModifiedNetWorth,CHARINDEX ('-', ModifiedNetWorth)-1 ) =RIGHT(RangeNetWorth,CHARINDEX ('-', RangeNetWorth)-2 )  

Output:

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.

Answer 3

Thank you so much that would very helpful :)