We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SELECT  t1.GA_Drg_NO ,
        SUM(t1.wr_scope) AS wr_scope ,
        SUM(t1.fitup) AS fitup ,
        SUM(t1.weld) AS weld ,
        t2.Fitup_SFSNO ,
        t1.JOBCODE ,
        t1.COMPLEXCODE ,
        t1.UNITCODE
FROM    WR_SCOPE_FITUP AS t1
        LEFT JOIN WR_SCOPE_FITUP AS t2 ON t1.GA_Drg_NO = t2.GA_Drg_NO
                                          AND t2.Fitup_SFSNO IS NOT NULL
GROUP BY t1.JOBCODE ,
        t1.COMPLEXCODE ,
        t1.UNITCODE ,
        t1.GA_Drg_NO ,
        t2.Fitup_SFSNO
GO


SELECT  GA_Drg_NO ,
        JOBCODE ,
        COMPLEXCODE ,
        UNITCODE ,
        Fitup_SFSNO ,
        WR_Scope ,
        fitup ,
        weld ,
        NDT ,
        intermediatecoat ,
        finalcoat ,
        dispatch
FROM    ( SELECT DISTINCT
                  t1.GA_Drg_NO ,
                  t3.JOBCODE ,
                  t3.COMPLEXCODE ,
                  t3.UNITCODE ,
                  t1.Fitup_SFSNO ,
                  t2.item_wt AS WR_Scope ,
                  ( SELECT  fitup
                    FROM    dbo.Fitup_Qty_SFSNO AS tf
                    WHERE   ( Fitup_SFSNO = t1.Fitup_SFSNO )
                            AND ( GA_Drg_NO = t1.GA_Drg_NO )
                  ) AS fitup ,
                  ( SELECT  weld
                    FROM    dbo.weld_Qty_SFSNO AS tw
                    WHERE   ( Fitup_SFSNO = t1.Fitup_SFSNO )
                            AND ( GA_Drg_NO = t1.GA_Drg_NO )
                  ) AS weld ,
                  ( SELECT  ISNULL(NDE, 0) AS Expr1
                    FROM    dbo.NDE_Qty_SFSNO AS tut
                    WHERE   ( Fitup_SFSNO = t1.Fitup_SFSNO )
                            AND ( GA_Drg_NO = t1.GA_Drg_NO )
                  ) AS NDT ,
                  ( SELECT  Intermediatecoat
                    FROM    dbo.Intermediate_Qty_SFSNO AS ic
                    WHERE   ( Fitup_SFSNO = t1.Fitup_SFSNO )
                            AND ( GA_Drg_NO = t1.GA_Drg_NO )
                  ) AS intermediatecoat ,
                  ( SELECT  finalcoat
                    FROM    dbo.Finalcoat_Qty_SFSNO AS fc
                    WHERE   ( Fitup_SFSNO = t1.Fitup_SFSNO )
                            AND ( GA_Drg_NO = t1.GA_Drg_NO )
                  ) AS finalcoat ,
                  ( SELECT  dispatch
                    FROM    dbo.Dispatch_Qty_SFSNO AS dis
                    WHERE   ( Fitup_SFSNO = t1.Fitup_SFSNO )
                            AND ( GA_Drg_NO = t1.GA_Drg_NO )
                  ) AS dispatch
          FROM    dbo.Drawing_Inspection_Detail AS t1
                  INNER JOIN dbo.WR_SCope AS t2 ON t1.GA_Drg_NO = t2.GA_Drg_NO
                  INNER JOIN dbo.Transmittal_GAD_JOBComplexUnitWise AS t3 ON t3.Drawing_No = t1.GA_Drg_NO
          UNION
          SELECT  GA_Drg_NO ,
                  JobCode ,
                  ComplexCode ,
                  UnitCode ,
                  NULL AS Fitup_SFSNO ,
                  SUM(itmwt) AS WR_Scope ,
                  NULL AS fitup ,
                  NULL AS weld ,
                  NULL AS NDT ,
                  NULL AS intermediatecoat ,
                  NULL AS finalcoat ,
                  NULL AS dispatch
          FROM    ( SELECT DISTINCT
                            a.GA_Drg_NO ,
                            a.Rev_NO ,
                            a.JobCode ,
                            a.ComplexCode ,
                            a.UnitCode ,
                            a.Mark_Rev_No_fab ,
                            ISNULL(SUM(CAST(ISNULL(a.Itme_Wt, 0) AS DECIMAL(18, 3))), 0) AS itmwt
                    FROM    dbo.CSV_Details AS a
                            INNER JOIN dbo.UnAssigned_Markno ON a.GA_Drg_NO = dbo.UnAssigned_Markno.GA_Drg_NO
                                                                AND a.Mark_Rev_No_fab = dbo.UnAssigned_Markno.Mark_Rev_No_fab
                    WHERE   ( a.Rev_NO = ( SELECT MAX(CAST(Rev_NO AS INT)) AS rev_no
                                           FROM   dbo.CSV_Details AS c
                                           WHERE  ( GA_Drg_NO = dbo.UnAssigned_Markno.GA_Drg_NO )
                                         ) )
                    GROUP BY a.GA_Drg_NO ,
                            a.Rev_NO ,
                            a.Mark_Rev_No_fab ,
                            a.JobCode ,
                            a.ComplexCode ,
                            a.UnitCode
                  ) AS tab1
          GROUP BY GA_Drg_NO ,
                  JobCode ,
                  ComplexCode ,
                  UnitCode
        ) AS tab1

We walk in the dark places no others will enter
We stand on the bridge and no one may pass