So I'm pretty new to T-SQL but I have a question about using a subquery to remove records from a SELECT statement I have.CREATE TABLE GAS_STMT( [TRNX_ID] [bigint] NULL, [MTR_SFX] [varchar](100) NULL, [MTR_NO] [varchar](100) NULL, [PLANT_NO] [varchar](100) NULL, [REC_STATUS_CD] [varchar](100) NULL, [ACCT_DT] [datetime2](7) NULL, [PROD_DT] [datetime2](7) NULL, [KW_CTR_REDELIVERED_HV] [numeric](29, 5) NULL)
INSERT INTO GAS_STMT(SELECT '8301', 'A', '365', '002', 'RR', 8/1/2011, 2/1/2011, .99 UNION ALLSELECT '8301', 'A', '365', '002', 'RR', 2/1/2013, 2/1/2011, .99 UNION ALLSELECT '8301', 'A', '365', '002', 'OR', 8/1/2011, 2/1/2011, .99 UNION ALL SELECT '8301', 'A', '365', '002', 'RR', 3/1/2011, 9/1/2011, .99 UNION ALLSELECT '8301', 'A', '365', '002', 'RR', 3/1/2013, 3/1/2011, .99 UNION ALLSELECT '8301', 'A', '365', '002', 'OR', 3/1/2011, 3/1/2011, .99 UNION ALL)
Here's a shortened list of my current query:SELECT a.PLANT_NO "PlantNumber",SUM(CASE WHEN a.REC_STATUS_CD = 'RR' THEN -a.KW_CTR_REDELIVERED_HV ELSE a.KW_CTR_REDELIVERED_HV END) "KeepWholeResidueMMBtu"FROM GAS_STMT aGROUP BY a.PLANT_NOORDER BY a.PLANT_NO
I want to exclude the records that are part of this query:select a.*FROM GAS_STMT a,(SELECT mtr_no, mtr_sfx, trnx_id, rec_status_cd, MAX(acct_dt) Acct_DtFROM GAS_STMTWHERE rec_status_cd = 'RR'GROUP BY mtr_no, mtr_sfx, trnx_id, rec_status_cdHAVING COUNT(trnx_id) > 1) bWHERE a.mtr_no = b.mtr_no and a.mtr_sfx = b.mtr_sfxand a.trnx_id = b.trnx_id and a.rec_status_cd = b.rec_status_cdand a.acct_dt = b.acct_dt
So the first query should sum for plant 002 to be 5.94. With the query that I want to exclude, it should remove two .99 records, making the total sum to 3.96. I'm not sure if that's possible? I've tried using where NOT EXISTS and inserting the second query but that returns nothing. Is this something that can be achieved? Thanks in advance!