Thursday, June 13, 2013

[SQL Server] UNION with multiple CTEs and summing data

[SQL Server] UNION with multiple CTEs and summing data

UNION with multiple CTEs and summing data

Posted: 13 Jun 2013 12:48 PM PDT

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.[code="sql"]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)[/code][code="sql"]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' [/code][code="sql"]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[/code]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 | 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 this possible? Can I combine and add the rows by plantnumber? Sorry for the very long post, I'm desperate!

Database Diagrams Help

Posted: 13 Jun 2013 09:29 AM PDT

Had a question about database diagrams. We want to set up a diagram to mainly get the benefit of visualizing our data, and establishing the relationships. The tables we have are old and do not always have a simple key. The diagram seems like a good fit, but does anyone know if we can set it up so that the relationships set up in the diagram do not enforce any rules or effect the tables the reference in any way? We don't want to effect the tables, only get a layout of the data we have. Is there a better tool to use outside of Management Studio 2008 R2 to set up these relationships for visualization only?Any help would be appreciated!

Search for relationship

Posted: 13 Jun 2013 06:05 AM PDT

I have a table having company names and another table having Revenueid and amount of Revenue but dont seem t get the relationship where it says which company pays what amount oof revenue.How can I see that. No common columns in these two tables.There are lot of other tables in the DB but I dont seem to find one which can show the relationship and get me the data.Pls suugest.

inserting a zip file into a varbinary column in a database

Posted: 13 Jun 2013 04:46 AM PDT

anyone know how to write an insert statement that will allow you to write a zip file into a table column designated as type varbinary? I would like to write and insert state and a retrieve statement.Here is my attempt at writing a zip file:Table: mps _datacolumns: mps_id longmps_session_id varchar(128)mps_request varbinarylocal variablesll_mps_id long value 1ls_session string value 'userid' + datetimels_zipfile string location of zipfile (ie c:\temp\zipfile.zip)insert into mps_data (mps_id, mps_session_id, mps_request) values ( :ll_mps_id, :ls_session, CONVERT(varbinary(max), :ls_zipfile));writes a binary value that is illegible. When I try to retrieve it creates a blank zip file that cannot be opened. Has anyone else been able to write a simple insert and retrieval process for zip files?

convert into second

Posted: 13 Jun 2013 02:58 AM PDT

Can you please help me converting datediff into seconds. I'm using SQL 2008 , the following command error out with pass valid argument. round((ENDDTTM - BEGINDTTM) * 24 * 60 * 60) || ' seconds' as PROCSTIME . Thank you

Move Primary Key Nonclustered Constraint and Clustered Index to New Filegroup?

Posted: 13 Jun 2013 01:05 AM PDT

New to SQL and want to try my hand at moving tables/indexes to different filegroups. Can someone tell me the correct process for migrating the nonclustered primary key index and clustered index? I included the table script below. CREATE TABLE [dbo].[system_xwalk_user_roles]( [RecID] [int] IDENTITY(1,1) NOT NULL, [UserRecID] [int] NOT NULL, [RoleRecID] [int] NOT NULL, [IsSuspended] [bit] NOT NULL, CONSTRAINT [PK_system_xwalk_user_roles] PRIMARY KEY NONCLUSTERED ( [RecID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GOCREATE CLUSTERED INDEX [IX_system_xwalk_user_roles] ON [dbo].[system_xwalk_user_roles] ( [UserRecID] ASC, [RoleRecID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]GO/****** Object: Default [DF_system_xwalk_user_roles_IsSuspended] Script Date: 06/13/2013 09:32:06 ******/ALTER TABLE [dbo].[system_xwalk_user_roles] ADD CONSTRAINT [DF_system_xwalk_user_roles_IsSuspended] DEFAULT (0) FOR [IsSuspended]GO
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog