SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using records in a SELECT to remove records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

photond
Starting Member

20 Posts

Posted - 05/03/2013 :  16:28:10  Show Profile  Reply with Quote
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!

Edited by - photond on 05/05/2013 21:43:03

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/03/2013 :  18:27:51  Show Profile  Reply with Quote
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 - 05/05/2013 :  21:46:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/06/2013 :  00:26:24  Show Profile  Reply with Quote
-- 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 - 05/06/2013 :  08:29:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 05/06/2013 :  09:43:11  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000