OK, în cazul în care am putea considera că acc_id e cheie primară în vw_crmimportclienti, atunci putem rescrie următoarea porţiune:

    select cst.*, NULLIF(x.cstid,cst.acc_id) IDSUP from vw_crmimportclienti cst
    inner join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid
    where cst.comid = 3

    union all

    select *, null IDSUP from vw_crmimportclienti cst
    where acc_id not in (
        select acc_id from vw_crmimportclienti cst
        inner join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid
        where cst.comid = 3
    )

astfel:

    select cst.*, NULLIF(x.cstid,cst.acc_id) IDSUP from vw_crmimportclienti cst
    left join #T x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid and cst.comid = 3

Cu această ocazie, am putea să scoatem tabela temporară (acum, că e folosită doar odată), şi obţinem:

select x.*,y.code CodAgent from (
    select cst.*, NULLIF(x.cstid,cst.acc_id) IDSUP from vw_crmimportclienti cst
    left join (
        select acc_new_codfiscal as cstafm, min(acc_id) as cstid
        from vw_crmimportclienti
        where acc_new_codfiscal in (
            select acc_new_codfiscal from vw_crmimportclienti
            where acc_new_codfiscal like '%[0-9]%'
            group by acc_new_codfiscal
            having count(acc_new_codfiscal) > 1)
        group by acc_new_codfiscal
    ) x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid and cst.comid = 3
) x inner join (
    select sal.Code code,ftr.cusid cusid
    from (
        SELECT ftr.cusid, min(ftr.id) ftrid
        FROM dbo.FINTRADE ftr
        INNER JOIN dbo.ITEMTRANS itt ON ftr.ID=itt.FTRID
        WHERE ftr.SOURCE=5 AND itt.BILLEDOUTPUT=1
        GROUP BY cusid
     ) z join fintrade ftr on ftr.id=z.ftrid
     INNER JOIN Salesman sal on ftr.COLIDSALESMAN = sal.id
) y on x.acc_id=y.cusid

Apoi, am putea să înlocuim porţiunea:

    select cst.*, NULLIF(x.cstid,cst.acc_id) IDSUP from vw_crmimportclienti cst
    left join (
        select acc_new_codfiscal as cstafm, min(acc_id) as cstid
        from vw_crmimportclienti
        where acc_new_codfiscal in (
            select acc_new_codfiscal from vw_crmimportclienti
            where acc_new_codfiscal like '%[0-9]%'
            group by acc_new_codfiscal
            having count(acc_new_codfiscal) > 1)
        group by acc_new_codfiscal
    ) x on cst.acc_new_codfiscal = x.cstafm and cst.acc_id >= x.cstid and cst.comid = 3

cu:

    select a.*, (
            SELECT MIN(b.acc_id) FROM vw_crmimportclienti b
            WHERE a.acc_new_codfiscal=b.acc_new_codfiscal
            AND b.acc_new_codfiscal like '%[0-9]%'
            AND a.acc_id>b.acc_id AND a.comid=3
        ) as IDSUP
    from vw_crmimportclienti a

Obţinând în final query-ul:

select x.*,y.code CodAgent from (
    select a.*, (
            SELECT MIN(b.acc_id) FROM vw_crmimportclienti b
            WHERE a.acc_new_codfiscal=b.acc_new_codfiscal
            AND b.acc_new_codfiscal like '%[0-9]%'
            AND a.acc_id>b.acc_id AND a.comid=3
        ) as IDSUP
    from vw_crmimportclienti a
) x inner join (
    select sal.Code code,ftr.cusid cusid
    from (
        SELECT ftr.cusid, min(ftr.id) ftrid
        FROM dbo.FINTRADE ftr
        INNER JOIN dbo.ITEMTRANS itt ON ftr.ID=itt.FTRID
        WHERE ftr.SOURCE=5 AND itt.BILLEDOUTPUT=1
        GROUP BY cusid
     ) z join fintrade ftr on ftr.id=z.ftrid
     INNER JOIN Salesman sal on ftr.COLIDSALESMAN = sal.id
) y on x.acc_id=y.cusid
 
Dacă e nevoie de mai multe optimizări, cred că ar trebui să intrăm în view-ul vw_crmimportclienti şi să vedem ce e în plus pe acolo (sau ce indecşi noi am putea face pentru acest query).

Răzvan