asked
grouping and adding into new table
i have a table that has multiple lines for one number. so i need to combine the amounts if a - b = 0...and few other criteria i need to insert one line into another table. Table 1 claimnum appramt paidamt 11 20. 20.0 11 10.0 10.0 11 2.0 2.0 table 2 how is best way to group all claimnum's together Right now i have Insert into table2 ([control num], [Org control num], [rec num], [property], [value], operator, dtmadded ) Select [clm spec 1],[clm spec 1],[clm spec 1],'Status','21','Sync',getdate() from table1 group by [clm spec 1],[Amt Paid], [Amt Appr], [Check Date], Status having sum([Amt Paid]) - SUM([amt appr]) = '0' and [Check Date] is null and Status = '2' im getting values returned but it isn't grouping them.
It is grouping them but probably not the way you want it. You are grouping by columns that are not part of an aggregate but they are part of an aggregate in your having. The most likely "fix" is to remove [Amt Paid] and [Amt Appr] from your group by. FWIW, I would highly recommend you not put spaces in your column names. It causes nothing but headaches. Also, it is better to give your column names a meaningful name. [Amt Appr] is not clear. Is that AmountApproved, AmortizationApplied, AmountAppropriated. If you just give the column a name it is instantly abundantly clear.
you have no idea how upset it makes me these programmers put spaces. Yes i agree with both of your comments. Now, if i remove from group by i get the following error is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Not sure how to rewrite the code.
Select [clm spec 1],[clm spec 1],[clm spec 1],'Status','21','Sync',getdate() from tmpclaims_preload group by [clm spec 1], [Check Date], Status having sum([Amt Paid]) - SUM([amt appr]) = '0' and [Check Date] is null and Status = '2' didn't throw the error.
TABLE [dbo].[table1]( [Dlr Num] [nvarchar](30) NOT NULL, [Promo] [nvarchar](5) NOT NULL, [Trans Code] [nvarchar](1) NOT NULL, [Proc Date] [datetime] NOT NULL, [Control Num] [nvarchar](11) NOT NULL, [Invoice Num] [nvarchar](24) NULL, [Clm Date] [datetime] NULL, [Amt Claimed] [money] NOT NULL, [Amt Appr] [money] NOT NULL, [Amt Paid] [money] NULL, [Ad From] [datetime] NOT NULL, [Ad To] [datetime] NULL, [Media] [nvarchar](3) NOT NULL, [Media Desc] [nvarchar](25) NOT NULL, [Audit 1] [nvarchar](2) NOT NULL, [Audit 2] [nvarchar](2) NULL, [Audit 3] [nvarchar](2) NULL, [Audit 4] [nvarchar](2) NULL, [Audit 5] [nvarchar](2) NULL, [Audit 6] [nvarchar](2) NULL, [Prod Code 1] [nvarchar](10) NOT NULL, [Prod Code 1 %] [int] NOT NULL, [Prod Code 1 Amt] [money] NULL, [Prod Code 1 Adv Amt] [money] NULL, [Prod Code 2] [nvarchar](10) NULL, [Prod Code 2 %] [int] NULL, [Prod Code 2 Amt] [money] NULL, [Prod Code 2 Adv Amt] [money] NULL, [Prod Code 3] [nvarchar](10) NULL, [Prod Code 3 %] [int] NULL, [Prod Code 3 Amt] [money] NULL, [Prod Code 3 Adv Amt] [money] NULL, [Prod Code 4] [nvarchar](10) NULL, [Prod Code 4 %] [int] NULL, [Prod Code 4 Amt] [money] NULL, [Prod Code 4 Adv Amt] [money] NULL, [Prod Code 5] [nvarchar](10) NULL, [Prod Code 5 %] [int] NULL, [Prod Code 5 Amt] [money] NULL, [Prod Code 5 Adv Amt] [money] NULL, [Prod Code 6] [nvarchar](10) NULL, [Prod Code 6 %] [int] NULL, [Prod Code 6 Amt] [money] NULL, [Prod Code 6 Adv Amt] [money] NULL, [Prod Code 7] [nvarchar](10) NULL, [Prod Code 7 %] [int] NULL, [Prod Code 7 Amt] [money] NULL, [Prod Code 7 Adv Amt] [money] NULL, [Prod Code 8] [nvarchar](10) NULL, [Prod Code 8 %] [int] NULL, [Prod Code 8 Amt] [money] NULL, [Prod Code 8 Adv Amt] [money] NULL, [Prod Code 9] [nvarchar](10) NULL, [Prod Code 9 %] [int] NULL, [Prod Code 9 Amt] [money] NULL, [Prod Code 9 Adv Amt] [money] NULL, [Prod Code 10] [nvarchar](10) NULL, [Prod Code 10 %] [int] NULL, [Prod Code 10 Amt] [money] NULL, [Prod Code 10 Adv Amt] [money] NULL, [Comment] [nvarchar](140) NULL, [Received Date] [datetime] NOT NULL, [Last Modified] [datetime] NULL, [Prior Appr Num] [nvarchar](15) NULL, [AltPayee Flag] [tinyint] NULL, [AltPayee Number] [float] NULL, [Ratio] [smallint] NULL, [Number Of Ads] [float] NULL, [Amt Applied] [money] NULL, [UM] [nvarchar](2) NOT NULL, [Audited Amt] [money] NULL, [Rated Ads] [float] NULL, [Branch Number] [nvarchar](15) NULL, [Operator] [nvarchar](10) NULL, [Bal Pending] [money] NULL, [Paid From Pending] [bit] NOT NULL, [Store Num] [nvarchar](10) NULL, [Clm Spec 1] [nvarchar](10) NULL, [Clm Spec 2] [nvarchar](25) NULL, [Dept Num] [nvarchar](30) NULL, [Doc Type] [nvarchar](1) NOT NULL, [Det Spec 1] [nvarchar](10) NULL, [Det Spec 2] [nvarchar](20) NULL, [Det Spec 3] [nvarchar](30) NULL, [Det Spec 4] [float] NULL, [Size Claimed] [float] NULL, [Size Appr] [float] NULL, [Media Zip] [nvarchar](5) NULL, [Color] [nvarchar](2) NULL, [Check Num] [float] NULL, [Check Date] [datetime] NULL, [Rate Appr] [money] NULL, [Void Date] [datetime] NULL, [Void Comment] [nvarchar](30) NULL, [Print Date] [datetime] NULL, [Status] [smallint] NULL, [Org Control Num] [nvarchar](11) NULL, [Locked] [bit] NOT NULL, [Audited] [bit] NULL, [Rec Num] [int] NOT NULL ) ON [PRIMARY]
That is super awesome. Thank you. Yes i do understand that. However, this structure has been around for so many years. This company has been around since early 1900's. Whoever first created there structure did not know what they should do. That being said. i am only inserting a few things into a table much smaller.
Here is your table in a legible format. CREATE TABLE [dbo].[table1] ( [Dlr Num] [NVARCHAR](30) NOT NULL ,[Promo] [NVARCHAR](5) NOT NULL ,[Trans Code] [NVARCHAR](1) NOT NULL ,[Proc Date] [DATETIME] NOT NULL ,[Control Num] [NVARCHAR](11) NOT NULL ,[Invoice Num] [NVARCHAR](24) NULL ,[Clm Date] [DATETIME] NULL ,[Amt Claimed] [MONEY] NOT NULL ,[Amt Appr] [MONEY] NOT NULL ,[Amt Paid] [MONEY] NULL ,[Ad From] [DATETIME] NOT NULL ,[Ad To] [DATETIME] NULL ,[Media] [NVARCHAR](3) NOT NULL ,[Media Desc] [NVARCHAR](25) NOT NULL ,[Audit 1] [NVARCHAR](2) NOT NULL ,[Audit 2] [NVARCHAR](2) NULL ,[Audit 3] [NVARCHAR](2) NULL ,[Audit 4] [NVARCHAR](2) NULL ,[Audit 5] [NVARCHAR](2) NULL ,[Audit 6] [NVARCHAR](2) NULL ,[Prod Code 1] [NVARCHAR](10) NOT NULL ,[Prod Code 1 %] [INT] NOT NULL ,[Prod Code 1 Amt] [MONEY] NULL ,[Prod Code 1 Adv Amt] [MONEY] NULL ,[Prod Code 2] [NVARCHAR](10) NULL ,[Prod Code 2 %] [INT] NULL ,[Prod Code 2 Amt] [MONEY] NULL ,[Prod Code 2 Adv Amt] [MONEY] NULL ,[Prod Code 3] [NVARCHAR](10) NULL ,[Prod Code 3 %] [INT] NULL ,[Prod Code 3 Amt] [MONEY] NULL ,[Prod Code 3 Adv Amt] [MONEY] NULL ,[Prod Code 4] [NVARCHAR](10) NULL ,[Prod Code 4 %] [INT] NULL ,[Prod Code 4 Amt] [MONEY] NULL ,[Prod Code 4 Adv Amt] [MONEY] NULL ,[Prod Code 5] [NVARCHAR](10) NULL ,[Prod Code 5 %] [INT] NULL ,[Prod Code 5 Amt] [MONEY] NULL ,[Prod Code 5 Adv Amt] [MONEY] NULL ,[Prod Code 6] [NVARCHAR](10) NULL ,[Prod Code 6 %] [INT] NULL ,[Prod Code 6 Amt] [MONEY] NULL ,[Prod Code 6 Adv Amt] [MONEY] NULL ,[Prod Code 7] [NVARCHAR](10) NULL ,[Prod Code 7 %] [INT] NULL ,[Prod Code 7 Amt] [MONEY] NULL ,[Prod Code 7 Adv Amt] [MONEY] NULL ,[Prod Code 8] [NVARCHAR](10) NULL ,[Prod Code 8 %] [INT] NULL ,[Prod Code 8 Amt] [MONEY] NULL ,[Prod Code 8 Adv Amt] [MONEY] NULL ,[Prod Code 9] [NVARCHAR](10) NULL ,[Prod Code 9 %] [INT] NULL ,[Prod Code 9 Amt] [MONEY] NULL ,[Prod Code 9 Adv Amt] [MONEY] NULL ,[Prod Code 10] [NVARCHAR](10) NULL ,[Prod Code 10 %] [INT] NULL ,[Prod Code 10 Amt] [MONEY] NULL ,[Prod Code 10 Adv Amt] [MONEY] NULL ,[Comment] [NVARCHAR](140) NULL ,[Received Date] [DATETIME] NOT NULL ,[Last Modified] [DATETIME] NULL ,[Prior Appr Num] [NVARCHAR](15) NULL ,[AltPayee Flag] [TINYINT] NULL ,[AltPayee Number] [FLOAT] NULL ,[Ratio] [SMALLINT] NULL ,[Number Of Ads] [FLOAT] NULL ,[Amt Applied] [MONEY] NULL ,[UM] [NVARCHAR](2) NOT NULL ,[Audited Amt] [MONEY] NULL ,[Rated Ads] [FLOAT] NULL ,[Branch Number] [NVARCHAR](15) NULL ,[Operator] [NVARCHAR](10) NULL ,[Bal Pending] [MONEY] NULL ,[Paid From Pending] [BIT] NOT NULL ,[Store Num] [NVARCHAR](10) NULL ,[Clm Spec 1] [NVARCHAR](10) NULL ,[Clm Spec 2] [NVARCHAR](25) NULL ,[Dept Num] [NVARCHAR](30) NULL ,[Doc Type] [NVARCHAR](1) NOT NULL ,[Det Spec 1] [NVARCHAR](10) NULL ,[Det Spec 2] [NVARCHAR](20) NULL ,[Det Spec 3] [NVARCHAR](30) NULL ,[Det Spec 4] [FLOAT] NULL ,[Size Claimed] [FLOAT] NULL ,[Size Appr] [FLOAT] NULL ,[Media Zip] [NVARCHAR](5) NULL ,[Color] [NVARCHAR](2) NULL ,[Check Num] [FLOAT] NULL ,[Check Date] [DATETIME] NULL ,[Rate Appr] [MONEY] NULL ,[Void Date] [DATETIME] NULL ,[Void Comment] [NVARCHAR](30) NULL ,[Print Date] [DATETIME] NULL ,[Status] [SMALLINT] NULL ,[Org Control Num] [NVARCHAR](11) NULL ,[Locked] [BIT] NOT NULL ,[Audited] [BIT] NULL ,[Rec Num] [INT] NOT NULL ) ON [PRIMARY] This table is a nightmare. Aside from the absolutely hideous names of columns (and the table itself), it is in dire need of normalization. You have multiple sections of repeating groups. This is just awful. Let me take a look at this and see what I can do.
CREATE TABLE [dbo].[table2]( [Control Num] [varchar](50) NOT NULL, [Org Control Num] [varchar](50) NOT NULL, [Rec Num] [int] NOT NULL, [Property] [varchar](100) NOT NULL, [Value] [varchar](8000) NOT NULL, [Operator] [varchar](100) NOT NULL, [dtmAdded] [datetime] NOT NULL, [comments] [varchar](8000) NULL, [ID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_tmpClaimsProperties] PRIMARY KEY CLUSTERED
Ironically the main thing that is important is the three num fields(which are all the same) and value in table2.
16 People are following this question.
