garethtnash
asked on
SQL Server - Sum Decimal Columns
Hello Experts,
I have a table that contains multiple DECIMAL columns - (Hours worked) I'm trying to combine these into just two columns - (Normal Hours worked, Overtime Hours worked) using an update statement, with SUM
It's not working? What am I missing here ? is it not possible to SUM DECIMAL values?
Here's my code -
Here's what happens when I execute -
Any suggestions?
Thank you
I have a table that contains multiple DECIMAL columns - (Hours worked) I'm trying to combine these into just two columns - (Normal Hours worked, Overtime Hours worked) using an update statement, with SUM
It's not working? What am I missing here ? is it not possible to SUM DECIMAL values?
Here's my code -
CREATE PROCEDURE [dbo].[Test Decimal Proc]
AS
SET NOCOUNT ON;
BEGIN
BEGIN
CREATE TABLE #Ratecard(
[ID] [int] IDENTITY(1,1) NOT NULL,
[DSHours] [decimal](6, 2) NULL,
[BSHours] [decimal](6, 2) NULL,
[NSHours] [decimal](6, 2) NULL,
[WDOTHors] [decimal](6, 2) NULL,
[WEOTHours] [decimal](6, 2) NULL
) ON [PRIMARY]
END
BEGIN
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 5.00, 0.00, 0.00, 2.00)
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 0.00, 10.00, 8.00, 2.00)
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 5.00, 3.00, 5.00, 2.00)
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (10.00, 5.00, 8.00, 15.00, 2.00)
END
BEGIN
ALTER TABLE #Ratecard
ADD [Conditional Hrs] DECIMAL (5,2) NULL, [OT Hrs] DECIMAL (5,2) NULL
END
BEGIN
Update #Ratecard set [Conditional Hrs] = SUM(DSHours + BSHours + NSHours)
Update #Ratecard set [OT Hrs] = SUM(WDOTHors + WEOTHours)
END
BEGIN
Select * from #Ratecard
END
END
Here's what happens when I execute -
Msg 157, Level 15, State 1, Procedure Test Decimal Proc, Line 27
An aggregate may not appear in the set list of an UPDATE statement.
Any suggestions?
Thank you
Please try this ..
Update #Ratecard set [Conditional Hrs] = SUM(DSHours + BSHours + NSHours) OVER()
Update #Ratecard set [OT Hrs] = SUM(WDOTHors + WEOTHours) OVER()
ASKER
Hi Both,
So -
Vitor -
Thanks, but, at a later stage i need to delete the DSHours + BSHours + NSHours columns - would I loose the data then?
Pawan - when i try your solution, I get -
So -
Vitor -
Thanks, but, at a later stage i need to delete the DSHours + BSHours + NSHours columns - would I loose the data then?
Pawan - when i try your solution, I get -
Msg 4108, Level 15, State 1, Procedure Test Decimal Proc, Line 28
Windowed functions can only appear in the SELECT or ORDER BY clauses.
ASKER
When I try this -
I get -
ALTER PROCEDURE [dbo].[Test Decimal Proc]
AS
SET NOCOUNT ON;
BEGIN
BEGIN
CREATE TABLE #Ratecard(
[ID] [int] IDENTITY(1,1) NOT NULL,
[DSHours] [decimal](6, 2) NULL,
[BSHours] [decimal](6, 2) NULL,
[NSHours] [decimal](6, 2) NULL,
[WDOTHors] [decimal](6, 2) NULL,
[WEOTHours] [decimal](6, 2) NULL
) ON [PRIMARY]
END
BEGIN
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 5.00, 0.00, 0.00, 2.00)
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 0.00, 10.00, 8.00, 2.00)
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 5.00, 3.00, 5.00, 2.00)
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (10.00, 5.00, 8.00, 15.00, 2.00)
END
BEGIN
ALTER TABLE #Ratecard
ADD [Conditional Hrs] AS DSHours + BSHours + NSHours, [OT Hrs] AS WDOTHors + WEOTHours
END
/*BEGIN
Update #Ratecard set [Conditional Hrs] = SUM(DSHours + BSHours + NSHours) OVER()
Update #Ratecard set [OT Hrs] = SUM(WDOTHors + WEOTHours) OVER()
END*/
BEGIN
Select * from #Ratecard
END
BEGIN
Alter TABLE #Ratecard
DROP COLUMN DSHours, BSHours, NSHours, WDOTHors, WEOTHours
Select * from #Ratecard
END
END
GO
I get -
Msg 5074, Level 16, State 1, Procedure Test Decimal Proc, Line 35
The column 'Conditional Hrs' is dependent on column 'DSHours'.
Msg 4922, Level 16, State 9, Procedure Test Decimal Proc, Line 35
ALTER TABLE DROP COLUMN DSHours failed because one or more objects access this column.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Update #Ratecard set [Conditional Hrs] = (select SUM(DSHours + BSHours + NSHours) OVER() from #ratecard)
Update #Ratecard set [OT Hrs] = (select SUM(WDOTHors + WEOTHours) OVER() from #ratecard)
Update #Ratecard set [OT Hrs] = (select SUM(WDOTHors + WEOTHours) OVER() from #ratecard)
ASKER
HI Guys,
If i use computed columns, can i copy the data from the computed column to a new column as decimal i.e. not copy the formula that computes the value??
Thanks
If i use computed columns, can i copy the data from the computed column to a new column as decimal i.e. not copy the formula that computes the value??
Thanks
If i use computed columns, can i copy the data from the computed column to a new column as decimal i.e. not copy the formula that computes the value??Yes, you can copy the data. The compute is only to say to the engine that column doesn't need to be stored by the user but to use values from other columns to store the value.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent - thank you
ASKER
Excellent thank you so much...
You also don't need the BEGIN..END blocks:
Open in new window