Looks to me like you are one step away. Make the last query (the union) into another CTE, and then select from it grouping by plant number and summing the other two columns.

I'll take a closer look and see if there's a more efficient way of getting there, but that should get the result you want.

The extra CTE solution

WITH RemoveData AS

(

SELECT a.PLANT_NO,a.ALLOC_WHDV_VOL,a.KW_CTR_REDELIVERED_HV, a.MTR_NO, a.MTR_SFX, a.TRNX_ID, a.REC_STATUS_CD,

MAX(a.ACCT_DT) ACCT_DT

FROM GasStmt a

WHERE a.REC_STATUS_CD = 'RR'

GROUP BY a.PLANT_NO,a.ALLOC_WHDV_VOL,a.KW_CTR_REDELIVERED_HV, a.MTR_NO, a.MTR_SFX, a.TRNX_ID, a.REC_STATUS_CD

HAVING COUNT(a.REC_STATUS_CD) > 2

),

RemoveData2 AS

(

SELECT plant_no "PlantNumber"

,SUM(-a.ALLOC_WHDV_VOL) "PlantStandardGrossWellheadMcf"

,SUM(KW_CTR_REDELIVERED_HV) "KeepWholeResidueMMBtu"

FROM RemoveData a

GROUP BY plant_no

),

OriginalData AS

(

SELECT a.PLANT_NO "PlantNumber"

,SUM(a.ALLOC_WHDV_VOL) "PlantStandardGrossWellheadMcf"

,SUM(CASE WHEN a.REC_STATUS_CD = 'RR' THEN -a.KW_CTR_REDELIVERED_HV ELSE a.KW_CTR_REDELIVERED_HV END) "KeepWholeResidueMMBtu"

FROM GasStmt a

LEFT OUTER JOIN (SELECT MTR_NO, MTR_SFX, TRNX_ID, REC_STATUS_CD, MAX(ACCT_DT) ACCT_DT

FROM GasStmt

WHERE REC_STATUS_CD = 'RR'

GROUP BY MTR_NO, MTR_SFX, TRNX_ID, REC_STATUS_CD

HAVING COUNT(TRNX_ID) > 1) b

ON a.MTR_NO = b.MTR_NO

AND a.TRNX_ID = b.TRNX_ID

AND a.Rec_Status_Cd = b.REC_STATUS_CD

AND a.Acct_Dt = b.ACCT_DT

WHERE a.ACCT_DT > '1/1/2010'

AND b.MTR_NO IS NULL

GROUP BY a.PLANT_NO

),

UnionCTE AS (

SELECT *

FROM RemoveData2

UNION

SELECT *

FROM OriginalData

)

SELECT PlantNumber, SUM(PlantStandardGrossWellheadMcf) AS PlantStandardGrossWellheadMcf,SUM(KeepWholeResidueMMBtu) AS KeepWholeResidueMMBtu

FROM UnionCTE

GROUP BY PlantNumber