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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Union of CTEs to add rows together
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

photond
Starting Member

20 Posts

Posted - 06/14/2013 :  00:16:25  Show Profile  Reply with Quote
I've asked this on another forum, but thought I'd give it a shot here too.
I've been trying to get this to work for days. I have two queries with the results I want. I thought I could UNION the data and combine the two but I'm struggling. Here's code for essentially what I'm doing.
CREATE TABLE STG.GasStmt
(PLANT_NO varchar(100),
ALLOC_WHDV_VOL numeric(29, 5),
KW_CTR_REDELIVERED_HV numeric(29, 5),
MTR_NO varchar(100),
MTR_SFX varchar(100),
TRNX_ID bigint,
REC_STATUS_CD varchar(100),
ACCT_DT DateTime)


insert into STG.GasStmt
select '043','0','50','36563','','83062200','OR','12/1/2011' union all
select '002','0','100','36563','','83062222','OR','12/1/2011' union all
select '002','0','-.99','36563','','-83062299','RR','12/1/2011' union all
select '002','0','-.99','36563','','-83062299','RR','2/1/2013' union all
select '002','0','-.99','36563','','-83062299','RR','4/1/2013' union all
select '002','0','-.99','36563','','83062299','OR','2/1/2011' union all
select '002','0','-.99','36563','','-86768195','RR','12/1/2011' union all
select '002','0','-.99','36563','','-86768195','RR','2/1/2013' union all
select '002','0','-.99','36563','','-86768195','RR','4/1/2013' union all
select '002','0','-.99','36563','','86768195','OR','3/1/2011' union all
select '002','0','-.99','36563','','-90467786','RR','1/1/2012' union all
select '002','0','-.99','36563','','-90467786','RR','2/1/2013' union all
select '002','0','-.99','36563','','-90467786','RR','4/1/2013' union all
select '002','0','-.99','36563','','90467786','OR','4/1/2011' union all
select '002','0','-.99','36563','','-77671301','RR','2/1/2013' union all
select '002','0','-.99','36563','','-77671301','RR','4/1/2013' union all
select '002','0','-.99','36563','','77671301','OR','1/1/2011' union all
select '002','0','-.99','36563','','-68420423','RR','2/1/2013' union all
select '002','0','-.99','36563','','68420423','OR','4/1/2013' union all
select '002','0','-.99','36563','','-188808446','RR','3/1/2013' union all
select '002','0','-.99','36563','','188808446','OR','1/1/2013' union all
select '002','1205.15','0','36563','A','138365544','OR','2/1/2012'


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 STG.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 STG.GasStmt a
                LEFT OUTER JOIN (SELECT MTR_NO, MTR_SFX, TRNX_ID, REC_STATUS_CD, MAX(ACCT_DT) ACCT_DT
                FROM STG.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
                )             
SELECT *
FROM RemoveData2
UNION 
SELECT *
FROM OriginalData


The result I'm hoping for with the above query is PlantNumber 002 combined.
I'm getting:
PlantNumber | PlantStandardGrossWellheadMcf | KeepWholeResidueMMBtu
002 | 0.00000 |-2.97000
002 | 1205.15000 |102.97000
043 |0.00000 |50.00000

My intended result:
PlantNumber | PlantStandardGrossWellheadMcf | KeepWholeResidueMMBtu
002 | 1205.15000 |100
043 |0.00000 |50.00000

Is my result possible? Am I missing something obvious? I would think I'd be able to combine the two to get the sum for PlantNumber 002 but can't figure it out.
Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/14/2013 :  00:41:17  Show Profile  Reply with Quote

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 STG.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 STG.GasStmt a
                LEFT OUTER JOIN (SELECT MTR_NO, MTR_SFX, TRNX_ID, REC_STATUS_CD, MAX(ACCT_DT) ACCT_DT
                FROM STG.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
                )             
SELECT Plant_NO,
SUM(PlantStandardGrossWellheadMcf) AS PlantStandardGrossWellheadMcf,
SUM(KeepWholeResidueMMBtu) AS KeepWholeResidueMMBtu
FROM
(
SELECT *
FROM RemoveData2
UNION ALL
SELECT *
FROM OriginalData
)t
GROUP BY Plant_No



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/14/2013 :  00:42:29  Show Profile  Reply with Quote
;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
                )             
SELECT PlantNumber
	,SUM(PlantStandardGrossWellheadMcf) PlantStandardGrossWellheadMcf
	,SUM(KeepWholeResidueMMBtu) KeepWholeResidueMMBtu
FROM (SELECT * FROM RemoveData2 
		UNION 
	  SELECT * FROM OriginalData
	 )T
GROUP BY PlantNumber


--
Chandu
Go to Top of Page

photond
Starting Member

20 Posts

Posted - 06/14/2013 :  10:08:30  Show Profile  Reply with Quote
Great, thank you both! Just have to use PlantNumber like bandi's response and it works perfectly in SQL Server.

I've also tried it in PL/SQL but can't get it to reference the alias. Neither PlantNumber nor Plant_No work. Anyone happen to now how it would apply with Oracle?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/14/2013 :  10:40:48  Show Profile  Reply with Quote
quote:
Originally posted by photond

Great, thank you both! Just have to use PlantNumber like bandi's response and it works perfectly in SQL Server.

I've also tried it in PL/SQL but can't get it to reference the alias. Neither PlantNumber nor Plant_No work. Anyone happen to now how it would apply with Oracle?


You may be better off posting in some Oracle forums for that

This is MS SQL Server forum and we dont have much expertise on Oracle

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.09 seconds. Powered By: Snitz Forums 2000