Can anyone help me with code optimization on my T-SQL 2K16 query?

See more:

SQL

T-SQL

CTE

Hi.

I have used CTE extensively in my scripts and just lately I found that a new script is really slow to return a result set. I have been really dumbfounded on how to further optimize as I have found that CTE is the best method in querying multiple tables with related data. Because it kinda gets really complicated and fuzzy when using join types.

Here is my sample script:

ALTER PROCEDURE [dbo].[xxx]
@DTB AS DATETIME,
@DTE AS DATETIME
AS
BEGIN
    WITH CTE AS 
    (
        SELECT H.DOCID, C.NAME, NSDOCNO, EMDOCNO, H.DOCDT, LINEID, D.MODELID, M.MODEL, D.QTY, D.ID
        FROM _NSPOHDR H
        JOIN COSTCTR C ON H.SHPCOSTID = C.COSTID    
        JOIN _NSPODTL D ON H.DOCID = D.DOCID
        JOIN MODEL M ON D.MODELID = M.MODELID
        WHERE  DOCDT BETWEEN @DTB AND @DTE
    ),
    CTE2 AS
    (
        SELECT C.*, 
        (
            SELECT COUNT(RID) 
            FROM TRANSFERHDR H JOIN TRANSFERDTL D ON H.DOCID = D.DOCID 
            WHERE H.APPTYPE = 0 
            AND REPLACE(REPLACE(PO,' ',''),'-','') LIKE REPLACE(REPLACE(C.EMDOCNO,' ',''),'-','') 
            AND MODELID = C.MODELID 
        ) AS SERVED,
        (
            SELECT COUNT(DTLID) 
            FROM INVDTL D JOIN INVHDR H ON D.DOCID = H.DOCID 
            WHERE H.APPTYPE = 1 
            AND REFDOCID IN 
            (
                SELECT DOCID 
                FROM TRANSFERHDR H 
                WHERE REPLACE(REPLACE(PO,' ',''),'-','') LIKE REPLACE(REPLACE(C.EMDOCNO,' ',''),'-','')  
                AND MODELID = C.MODELID
            ) 
            AND MODELID = C.MODELID 
        ) AS RCVD
        FROM CTE C
    )
    SELECT C.*, (QTY - SERVED) AS BALANCE, (SERVED - RCVD) AS TRANSIT  
    FROM CTE2 C
END

Hope you could help me out. Thanks a lot in advance :)

What I have tried:

Tried using joins and always get mixed results that doesn't satisfy the output needed for my project.

Cursor is is also not viable.