Help on TSQL query from the below tables

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 see my answers in your last thread:
https://learn.microsoft.com/en-us/answers/questions/700644/help-on-tsql-query.html

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".

Answer 2

Here is the answer from LiHongMSFT-3908

;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