SELECT A.GRP, A.SAMPLE_CODE,A.PRODNAME, A.PACK_SIZE, A.TP, ISNULL(B.TOT_REC,0) TOT_REC, ISNULL(C.TOT_ISS,0) TOT_ISS, (ISNULL(B.TOT_REC,0) - ISNULL(C.TOT_ISS,0) ) AS BALANCE,         D.*,        0 as VAL1, 0 AS VAL2, '' AS HD1, '' AS HD2, '' AS HD3          FROM PRODUCT_SAMPLE A          LEFT JOIN (                       SELECT SAMPLE_CODE, SUM(QTY) AS TOT_REC                             FROM SMS_RECEIVE_DETAIL                             GROUP BY SAMPLE_CODE                    ) B ON A.SAMPLE_CODE = B.SAMPLE_CODE         LEFT JOIN (                      SELECT SAMPLE_CODE, SUM(QTY) AS TOT_ISS                             FROM SMS_ISSUE_DETAIL                             GROUP BY SAMPLE_CODE                    ) C ON A.SAMPLE_CODE = C.SAMPLE_CODE              LEFT JOIN (SELECT A.SAMPLE_CODE as SAM_CODE,           (B.REC_BA - C.ISS_BA) AS BA, 0 as BG, 0 as CG, 0 as CX, 0 as DH, 0 as DP, 0 as FA, 0 as JS, 0 as KG, 0 as MY, 0 as NG, 0 as FE, 0 as RJ, 0 as RN, 0 as SY, 0 as TA, 0 as CM, 0 as DS, 0 as KS, 0 as KH,              0 as VAL1, 0 AS VAL2, '' AS HD1, '' AS HD2, '' AS HD3              FROM PRODUCT_SAMPLE A              LEFT JOIN (                                      SELECT SAMPLE_CODE,                                SUM(CASE WHEN DEPOT_CODE = 'BA' THEN QTY ELSE 0 END ) AS REC_BA,    SUM(CASE WHEN DEPOT_CODE = 'BG' THEN QTY ELSE 0 END ) AS REC_BG,    SUM(CASE WHEN DEPOT_CODE = 'CW' THEN QTY ELSE 0 END ) AS REC_CW,    SUM(CASE WHEN DEPOT_CODE = 'CG' THEN QTY ELSE 0 END ) AS REC_CG,    SUM(CASE WHEN DEPOT_CODE = 'CX' THEN QTY ELSE 0 END ) AS REC_CX,    SUM(CASE WHEN DEPOT_CODE = 'DH' THEN QTY ELSE 0 END ) AS REC_DH,    SUM(CASE WHEN DEPOT_CODE = 'DP' THEN QTY ELSE 0 END ) AS REC_DP,    SUM(CASE WHEN DEPOT_CODE = 'FA' THEN QTY ELSE 0 END ) AS REC_FA,    SUM(CASE WHEN DEPOT_CODE = 'JS' THEN QTY ELSE 0 END ) AS REC_JS,    SUM(CASE WHEN DEPOT_CODE = 'KG' THEN QTY ELSE 0 END ) AS REC_KG,    SUM(CASE WHEN DEPOT_CODE = 'MY' THEN QTY ELSE 0 END ) AS REC_MY,    SUM(CASE WHEN DEPOT_CODE = 'NG' THEN QTY ELSE 0 END ) AS REC_NG,    SUM(CASE WHEN DEPOT_CODE = 'FE' THEN QTY ELSE 0 END ) AS REC_FE,    SUM(CASE WHEN DEPOT_CODE = 'RJ' THEN QTY ELSE 0 END ) AS REC_RJ,    SUM(CASE WHEN DEPOT_CODE = 'RN' THEN QTY ELSE 0 END ) AS REC_RN,    SUM(CASE WHEN DEPOT_CODE = 'SY' THEN QTY ELSE 0 END ) AS REC_SY,    SUM(CASE WHEN DEPOT_CODE = 'TA' THEN QTY ELSE 0 END ) AS REC_TA,    SUM(CASE WHEN DEPOT_CODE = 'CM' THEN QTY ELSE 0 END ) AS REC_CM,    SUM(CASE WHEN DEPOT_CODE = 'DS' THEN QTY ELSE 0 END ) AS REC_DS,    SUM(CASE WHEN DEPOT_CODE = 'KS' THEN QTY ELSE 0 END ) AS REC_KS,    SUM(CASE WHEN DEPOT_CODE = 'KH' THEN QTY ELSE 0 END ) AS REC_KH,    SUM(CASE WHEN DEPOT_CODE = 'FC' THEN QTY ELSE 0 END ) AS REC_FC,                                   TP                               FROM SMS_RECEIVE_DETAIL_DEPOT                                 GROUP BY SAMPLE_CODE,TP                        ) B ON A.SAMPLE_CODE = B.SAMPLE_CODE             LEFT JOIN (                          SELECT SAMPLE_CODE,                                SUM(CASE WHEN DEPOT_CODE = 'BA' THEN QTY ELSE 0 END ) AS ISS_BA,    SUM(CASE WHEN DEPOT_CODE = 'BG' THEN QTY ELSE 0 END ) AS ISS_BG,    SUM(CASE WHEN DEPOT_CODE = 'CW' THEN QTY ELSE 0 END ) AS ISS_CW,    SUM(CASE WHEN DEPOT_CODE = 'CG' THEN QTY ELSE 0 END ) AS ISS_CG,    SUM(CASE WHEN DEPOT_CODE = 'CX' THEN QTY ELSE 0 END ) AS ISS_CX,    SUM(CASE WHEN DEPOT_CODE = 'DH' THEN QTY ELSE 0 END ) AS ISS_DH,    SUM(CASE WHEN DEPOT_CODE = 'DP' THEN QTY ELSE 0 END ) AS ISS_DP,    SUM(CASE WHEN DEPOT_CODE = 'FA' THEN QTY ELSE 0 END ) AS ISS_FA,    SUM(CASE WHEN DEPOT_CODE = 'JS' THEN QTY ELSE 0 END ) AS ISS_JS,    SUM(CASE WHEN DEPOT_CODE = 'KG' THEN QTY ELSE 0 END ) AS ISS_KG,    SUM(CASE WHEN DEPOT_CODE = 'MY' THEN QTY ELSE 0 END ) AS ISS_MY,    SUM(CASE WHEN DEPOT_CODE = 'NG' THEN QTY ELSE 0 END ) AS ISS_NG,    SUM(CASE WHEN DEPOT_CODE = 'FE' THEN QTY ELSE 0 END ) AS ISS_FE,    SUM(CASE WHEN DEPOT_CODE = 'RJ' THEN QTY ELSE 0 END ) AS ISS_RJ,    SUM(CASE WHEN DEPOT_CODE = 'RN' THEN QTY ELSE 0 END ) AS ISS_RN,    SUM(CASE WHEN DEPOT_CODE = 'SY' THEN QTY ELSE 0 END ) AS ISS_SY,    SUM(CASE WHEN DEPOT_CODE = 'TA' THEN QTY ELSE 0 END ) AS ISS_TA,    SUM(CASE WHEN DEPOT_CODE = 'CM' THEN QTY ELSE 0 END ) AS ISS_CM,    SUM(CASE WHEN DEPOT_CODE = 'DS' THEN QTY ELSE 0 END ) AS ISS_DS,    SUM(CASE WHEN DEPOT_CODE = 'KS' THEN QTY ELSE 0 END ) AS ISS_KS,    SUM(CASE WHEN DEPOT_CODE = 'KH' THEN QTY ELSE 0 END ) AS ISS_KH,    SUM(CASE WHEN DEPOT_CODE = 'FC' THEN QTY ELSE 0 END ) AS ISS_FC,                                  TP                               FROM SMS_ISSUE_DETAIL_DEPOT                                 GROUP BY SAMPLE_CODE, TP                        ) C ON A.SAMPLE_CODE = C.SAMPLE_CODE )          D ON A.SAMPLE_CODE = D.SAM_CODE