SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UNION with multiple CTEs and summing data


UNION with multiple CTEs and summing data

Author
Message
hogpen
hogpen
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 323
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. It's actually in Oracle but I tested the code below in SQL Server and get the same result.

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



Sorry, I went overboard with the inserts. I wanted to make sure it was like my data. 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 this possible? Can I combine and add the rows by plantnumber? Sorry for the very long post, I'm desperate!
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3578 Visits: 3149
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


hogpen
hogpen
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 323
Thank you Nevyn! That worked perfectly.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search