Author |
Topic |
photond
Starting Member
20 Posts |
Posted - 2013-06-14 : 00:16:25
|
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.GasStmtselect '043','0','50','36563','','83062200','OR','12/1/2011' union allselect '002','0','100','36563','','83062222','OR','12/1/2011' union allselect '002','0','-.99','36563','','-83062299','RR','12/1/2011' union allselect '002','0','-.99','36563','','-83062299','RR','2/1/2013' union allselect '002','0','-.99','36563','','-83062299','RR','4/1/2013' union allselect '002','0','-.99','36563','','83062299','OR','2/1/2011' union allselect '002','0','-.99','36563','','-86768195','RR','12/1/2011' union allselect '002','0','-.99','36563','','-86768195','RR','2/1/2013' union allselect '002','0','-.99','36563','','-86768195','RR','4/1/2013' union allselect '002','0','-.99','36563','','86768195','OR','3/1/2011' union allselect '002','0','-.99','36563','','-90467786','RR','1/1/2012' union allselect '002','0','-.99','36563','','-90467786','RR','2/1/2013' union allselect '002','0','-.99','36563','','-90467786','RR','4/1/2013' union allselect '002','0','-.99','36563','','90467786','OR','4/1/2011' union allselect '002','0','-.99','36563','','-77671301','RR','2/1/2013' union allselect '002','0','-.99','36563','','-77671301','RR','4/1/2013' union allselect '002','0','-.99','36563','','77671301','OR','1/1/2011' union allselect '002','0','-.99','36563','','-68420423','RR','2/1/2013' union allselect '002','0','-.99','36563','','68420423','OR','4/1/2013' union allselect '002','0','-.99','36563','','-188808446','RR','3/1/2013' union allselect '002','0','-.99','36563','','188808446','OR','1/1/2013' union allselect '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 RemoveData2UNION SELECT *FROM OriginalData The result I'm hoping for with the above query is PlantNumber 002 combined.I'm getting:PlantNumber | PlantStandardGrossWellheadMcf | KeepWholeResidueMMBtu002 | 0.00000 |-2.97000002 | 1205.15000 |102.97000043 |0.00000 |50.00000My intended result:PlantNumber | PlantStandardGrossWellheadMcf | KeepWholeResidueMMBtu002 | 1205.15000 |100043 |0.00000 |50.00000Is 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
52326 Posts |
Posted - 2013-06-14 : 00:41:17
|
[code]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 KeepWholeResidueMMBtuFROM(SELECT *FROM RemoveData2UNION ALLSELECT *FROM OriginalData)tGROUP BY Plant_No[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-14 : 00:42:29
|
[code];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) KeepWholeResidueMMBtuFROM (SELECT * FROM RemoveData2 UNION SELECT * FROM OriginalData )TGROUP BY PlantNumber[/code]--Chandu |
|
|
photond
Starting Member
20 Posts |
Posted - 2013-06-14 : 10:08:30
|
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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-14 : 10:40:48
|
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 thatThis is MS SQL Server forum and we dont have much expertise on Oracle------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|