Complex reporting query suggestion needed for different approach as per latest Tsql format We have recently migrated from SQL Server 2008 R2 to SQL Server 2017 web edition. I am not a SQL developer but enthuses to learn.
Since many of our scrips are of old SQL format, I am searching for some alternative to leverage new T-SQL formats to help optimize the codes.
Below is one example which I believe can be modified using some different approach. It will be helpful for me if someone suggest me some approaches so that I can learn in detail about them.
Below query is for report it uses UNION ALL, the first part fetches data of ID for which payments are done incorrectly marked as 'recoup' and second part fetched data for the same ID it amount of payment required to be done.
SELECT TOP 200 'Recoup',ERH_DOC_DATE,ERH_DOCNO,ERH_RECV_FROM,ERD_BLH_ID,BLD_CODE,A.ERD_ALLOWED_AMT,A.ERD_PAID_INS_AMT
FROM TRN_ERA_HEAD,TRN_ERA_DET A,TRN_BILLING_DET
WHERE ERH_STATUS = 'P' AND ERH_BOOL_INACTIVE = 0 AND A.ERD_BOOL_RECALLED=1 AND
A.ERD_ALLOWED_AMT &lt;&gt; 0 AND A.ERD_PAID_INS_AMT &lt;&gt; 0 AND
ERH_ID = A.ERD_ERH_ID AND
A.ERD_BLD_ID = BLD_ID AND
NOT EXISTS (SELECT B.ERD_ID FROM TRN_ERA_DET B WHERE A.ERD_ERH_ID = B.ERD_ERH_ID AND A.ERD_ID &lt;&gt; B.ERD_ID AND B.ERD_BLD_ID = A.ERD_BLD_ID AND B.ERD_BOOL_RECALLED = 0 AND A.ERD_ALLOWED_AMT = B.ERD_ALLOWED_AMT AND A.ERD_PAID_INS_AMT = B.ERD_PAID_INS_AMT)
AND ERH_DOC_DATE&gt;='01-01-2021' AND ERH_DOC_DATE&lt;='05-31-2021' 
UNION ALL 
SELECT TOP 200 'Payment',EH1.ERH_DOC_DATE,EH1.ERH_DOCNO, EH1.ERH_RECV_FROM, ED1.ERD_BLH_ID,BD1.BLD_CODE,ED1.ERD_ALLOWED_AMT, ED1.ERD_PAID_INS_AMT FROM 
( SELECT ERD_ERH_ID ERDERH_ID, ERD_BLH_ID erdblh_id, a.erd_bld_id bld, A.ERD_ALLOWED_AMT, A.ERD_PAID_INS_AMT FROM TRN_ERA_HEAD,TRN_ERA_DET A,TRN_BILLING_DET WHERE ERH_STATUS = 'P' AND ERH_BOOL_INACTIVE = 0 AND A.ERD_BOOL_RECALLED=1 AND A.ERD_ALLOWED_AMT &lt;&gt; 0 AND A.ERD_PAID_INS_AMT &lt;&gt; 0 AND ERH_ID = A.ERD_ERH_ID AND A.ERD_BLD_ID = BLD_ID AND NOT EXISTS 
(SELECT B.ERD_ID FROM TRN_ERA_DET B WHERE A.ERD_ERH_ID = B.ERD_ERH_ID AND A.ERD_ID &lt;&gt; B.ERD_ID AND B.ERD_BLD_ID = A.ERD_BLD_ID AND B.ERD_BOOL_RECALLED = 0 AND A.ERD_ALLOWED_AMT = B.ERD_ALLOWED_AMT AND A.ERD_PAID_INS_AMT = B.ERD_PAID_INS_AMT) AND ERH_DOC_DATE&gt;='01-01-2021'
AND ERH_DOC_DATE&lt;='05-31-2021') AS RECOUPS, 
TRN_ERA_HEAD EH1,TRN_ERA_DET ED1, TRN_BILLING_DET BD1 WHERE RECOUPS.ERDERH_ID &lt; ED1.ERD_ERH_ID AND ED1.ERD_BLD_ID = RECOUPS.BLD AND EH1.ERH_ID=ED1.ERD_ERH_ID AND ED1.ERD_BLD_ID=BD1.BLD_ID AND EH1.ERH_TRNTYPE='IN' AND EH1.ERH_STATUS = 'P' AND EH1.ERH_BOOL_INACTIVE = 0 AND ED1.ERD_BOOL_RECALLED=0 AND ED1.ERD_ALLOWED_AMT &lt;&gt; 0 AND ED1.ERD_PAID_INS_AMT &lt;&gt; 0;

Please suggest me some different approaches. Thank you in advance.
To start, here is a potential formatting that makes the query less of a tragedy to read:
SELECT TOP 200
    'Recoup', ERH_DOC_DATE, ERH_DOCNO, ERH_RECV_FROM, ERD_BLH_ID,
    BLD_CODE, A.ERD_ALLOWED_AMT, A.ERD_PAID_INS_AMT
FROM TRN_ERA_HEAD, TRN_ERA_DET A, TRN_BILLING_DET
WHERE ERH_STATUS = 'P' 
    AND ERH_BOOL_INACTIVE = 0 
    AND A.ERD_BOOL_RECALLED = 1 
    AND A.ERD_ALLOWED_AMT &lt;&gt; 0 
    AND A.ERD_PAID_INS_AMT &lt;&gt; 0 
    AND ERH_ID = A.ERD_ERH_ID 
    AND A.ERD_BLD_ID = BLD_ID 
    AND NOT EXISTS (
        SELECT B.ERD_ID 
        FROM TRN_ERA_DET B
        WHERE A.ERD_ERH_ID = B.ERD_ERH_ID 
            AND A.ERD_ID &lt;&gt; B.ERD_ID
            AND B.ERD_BLD_ID = A.ERD_BLD_ID
            AND B.ERD_BOOL_RECALLED = 0
            AND A.ERD_ALLOWED_AMT = B.ERD_ALLOWED_AMT
            AND A.ERD_PAID_INS_AMT = B.ERD_PAID_INS_AMT
    )
    AND ERH_DOC_DATE &gt;= '01-01-2021'
    AND ERH_DOC_DATE &lt;= '05-31-2021' 
UNION ALL 
SELECT TOP 200 
    'Payment', EH1.ERH_DOC_DATE, EH1.ERH_DOCNO, EH1.ERH_RECV_FROM,
    ED1.ERD_BLH_ID, BD1.BLD_CODE, ED1.ERD_ALLOWED_AMT, ED1.ERD_PAID_INS_AMT
FROM (
    SELECT
        ERD_ERH_ID ERDERH_ID, ERD_BLH_ID erdblh_id, a.erd_bld_id bld,
        A.ERD_ALLOWED_AMT, A.ERD_PAID_INS_AMT
    FROM TRN_ERA_HEAD, TRN_ERA_DET A,TRN_BILLING_DET
    WHERE ERH_STATUS = 'P'
        AND ERH_BOOL_INACTIVE = 0
        AND A.ERD_BOOL_RECALLED = 1
        AND A.ERD_ALLOWED_AMT &lt;&gt; 0 
        AND A.ERD_PAID_INS_AMT &lt;&gt; 0
        AND ERH_ID = A.ERD_ERH_ID
        AND A.ERD_BLD_ID = BLD_ID
        AND NOT EXISTS (
            SELECT B.ERD_ID
            FROM TRN_ERA_DET B
            WHERE A.ERD_ERH_ID = B.ERD_ERH_ID
                AND A.ERD_ID &lt;&gt; B.ERD_ID
                AND B.ERD_BLD_ID = A.ERD_BLD_ID
                AND B.ERD_BOOL_RECALLED = 0
                AND A.ERD_ALLOWED_AMT = B.ERD_ALLOWED_AMT
                AND A.ERD_PAID_INS_AMT = B.ERD_PAID_INS_AMT
        )
        AND ERH_DOC_DATE &gt;= '01-01-2021'
        AND ERH_DOC_DATE &lt;= '05-31-2021'
    ) AS RECOUPS, 
    TRN_ERA_HEAD EH1,
    TRN_ERA_DET ED1, 
    TRN_BILLING_DET BD1 
WHERE RECOUPS.ERDERH_ID &lt; ED1.ERD_ERH_ID
    AND ED1.ERD_BLD_ID = RECOUPS.BLD
    AND EH1.ERH_ID = ED1.ERD_ERH_ID
    AND ED1.ERD_BLD_ID = BD1.BLD_ID
    AND EH1.ERH_TRNTYPE = 'IN'
    AND EH1.ERH_STATUS = 'P'
    AND EH1.ERH_BOOL_INACTIVE = 0
    AND ED1.ERD_BOOL_RECALLED = 0
    AND ED1.ERD_ALLOWED_AMT &lt;&gt; 0
    AND ED1.ERD_PAID_INS_AMT &lt;&gt; 0;

Aside from the valid points that @Squirrel has already raised:

You'll want to change your date literal format to something that is non-locale-dependent; read https://blog.greglow.com/2020/04/27/t-sql-101-67-literal-date-and-time-values-in-sql-server-t-sql/ for instance
Choose a consistent case; right now yours isn't. My personal preference is lowercase-everything (keywords and symbols) to avoid shouting, but even if you upper-case everything it will be more consistent than what you have now.