Help on TSQL query from the below tables

Vivaan 61 Reputation points
2022-01-19T06:00:36.847+00:00

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 :
166210-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,573 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,782 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

1 additional answer

Sort by: Most helpful
  1. Vivaan 61 Reputation points
    2022-01-19T08:48:59.293+00:00

    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

    0 comments No comments