Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using records in a SELECT to remove records

Author  Topic 

photond
Starting Member

20 Posts

Posted - 2013-05-03 : 16:28:10
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 ALL
SELECT '8301', 'A', '365', '002', 'RR', 2/1/2013, 2/1/2011, .99 UNION ALL
SELECT '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 ALL
SELECT '8301', 'A', '365', '002', 'RR', 3/1/2013, 3/1/2011, .99 UNION ALL
SELECT '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 a
GROUP BY a.PLANT_NO
ORDER 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_Dt
FROM GAS_STMT
WHERE rec_status_cd = 'RR'
GROUP BY mtr_no, mtr_sfx, trnx_id, rec_status_cd
HAVING COUNT(trnx_id) > 1) b
WHERE a.mtr_no = b.mtr_no and a.mtr_sfx = b.mtr_sfx
and a.trnx_id = b.trnx_id and a.rec_status_cd = b.rec_status_cd
and 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!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-03 : 18:27:51
The fist query is aginst the GAS_STMT as well as the second, so you can't use an NOT EXISTS, since, by definition, it has to exist. So, you'll need to refine your logic some more.

Here are some links that explain how to ask your question in more detail so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

photond
Starting Member

20 Posts

Posted - 2013-05-05 : 21:46:15
Thanks, Lamprey. I've edited the post above to reflect the changes. Please let me know if further information is needed. Essentially, there are two queries I have. One that sums the data at a plant level and another that removes duplicate 'RR' records based on the most recent accounting date. My main question is if those can be combined to sum the data while excluding those duplicate records.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-06 : 00:26:24
-- this?
SELECT a.PLANT_NO "PlantNumber"
,SUM(CASE WHEN [REC_STATUS_CD] = 'RR' THEN a.KW_CTR_REDELIVERED_HV END) "KeepWholeResidueMMBtu"
FROM GAS_STMT a
GROUP BY a.PLANT_NO
ORDER BY a.PLANT_NO

--
Chandu
Go to Top of Page

photond
Starting Member

20 Posts

Posted - 2013-05-06 : 08:29:22
Thank you for your response, bandi. That won't work for me since that still includes the duplicate 'RR' records. Your query is what I currently have, but I need to somehow remove the duplicate 'RR' records from the sum to only have 3.96 in the above example data.
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-06 : 09:43:11
Not sure why only you are wanting to exclude ONLY the record of 'RR' having max Acct_dt ... however, based on the sample data.. May be this is something that would work for you


SELECT * FROM (
select *,ROW_NUMBER () over (partition by Rec_Status_Cd order by Acct_dt desc) RID
from @GAS_STMT)A WHERE (RID<>1 and REC_STATUS_CD='rr') OR REC_STATUS_CD<>'rr'

Cheers
MIK
Go to Top of Page
   

- Advertisement -