February 23, 2011 at 10:52 am
I wrote some code but it does not parse, my guess is I can't use UPDATE and GROUP like I tried. Below is a description of the rules I was trying to implement, a CREATE TABLE, an INSERT and what the final data should look like based on the sample set.
Everything I start to think of ends up with UPDATE and GROUP again! I would appreciate it if someone could set me straight on how to do it correctly. Thanks very much.
/*
Rules:
I want to accumulate into work1 the sum of hours per employee found in Hours_Reg, Hours_Ot and (case when (PayCode=5 or PayCode=10) then 0.0 else OtherHours end)
I want to accumulate into work2 the sum of hours per employee found in (case when (PayCode=5 or PayCode=10) then OtherHours else 0.0 end)
Since the PayCodes might not always be 5 and 10 I need to use a select statement like (SELECT DISTINCT PayCode FROM PayCodeTable WHERE PayType='V')
*/
CREATE TABLE MyTable (
Company int
, Loc_No int
, Emp_No int
, Hours_Reg decimal(18,2)
, Hours_Ot decimal(18,2)
, PayCode int
, OtherHours decimal(18,2)
, work1 decimal(18,2)
, work2 decimal(18,2)
, Source int
)
CREATE TABLE PayCodeTable (Code int, PayType char(1))
INSERT INTO PayCodeTable (1,'R')
INSERT INTO PayCodeTable (2,'O')
INSERT INTO PayCodeTable (3,'R')
INSERT INTO PayCodeTable (4,'H')
INSERT INTO PayCodeTable (5,'V')
INSERT INTO PayCodeTable (10,'V')
--sample set
INSERT INTO MyTable (30, 3456, 7, 20.0, 0.0, 0, 0.0, null, null, 34560003)
INSERT INTO MyTable (30, 3456, 7, 20.0, 0.0, 5, 8.0, null, null, 34560003)
INSERT INTO MyTable (30, 3456, 4, 40.0, 4.5, 3, 4.0, null, null, 34560003)
INSERT INTO MyTable (30, 3456, 2, 20.0, 0.0, 1, 6.0, null, null, 34560003)
INSERT INTO MyTable (30, 3456, 2, 10.0, 0.0, 5, 8.0, null, null, 34560003)
INSERT INTO MyTable (30, 3456, 2, 10.0, 0.0, 0, 0.0, null, null, 34560003)
INSERT INTO MyTable (30, 3456, 1, 0.0, 0.0, 1, 40.0, null, null, 34560003)
INSERT INTO MyTable (30, 3456, 1, 0.0, 0.0, 10, 40.0, null, null, 34560003)
--final output should look like this
(30, 3456, 7, 20.0, 0.0, 0, 0.0, 40.0, 8.0, 34560003)
(30, 3456, 7, 20.0, 0.0, 5, 8.0, 40.0, 8.0, 34560003)
(30, 3456, 4, 40.0, 4.5, 3, 4.0, 48.5, 0.0, 34560003)
(30, 3456, 2, 20.0, 0.0, 1, 6.0, 46.0, 8.0, 34560003)
(30, 3456, 2, 10.0, 0.0, 5, 8.0, 46.0, 8.0, 34560003)
(30, 3456, 2, 10.0, 0.0, 0, 0.0, 46.0, 8.0, 34560003)
(30, 3456, 1, 0.0, 0.0, 1, 40.0, 40.0, 40.0, 34560003)
(30, 3456, 1, 0.0, 0.0, 10, 40.0, 40.0, 40.0, 34560003)
This is the stuff that doesn't work, the error is <Incorrect syntax near the keyword 'GROUP'>
DECLARE @Source int
SET @Source=34560003
UPDATE MyTable SET
work1=SUM(Hours_Reg+Hours_Ot)
WHERE Source = @Source
GROUP BY Company, Loc_No, Emp_No
UPDATE MyTable SET
work2=SUM(OtherHours)
WHERE Source = @Source
AND PayCode NOT IN (SELECT DISTINCT Code FROM PayCodeTable WHERE PayType='V')
GROUP BY Company, Loc_No, Emp_No
UPDATE MyTable SET
work1=work1+work2
, work2=0
WHERE Source = @Source
UPDATE MyTable SET
work2=SUM(OtherHours)
WHERE Source = @Source
AND PayCode IN (SELECT DISTINCT Code FROM PayCodeTable WHERE PayType='V')
GROUP BY Company, Loc_No, Emp_No
February 23, 2011 at 10:56 am
You can't use group by in an update.
You have to precompile the data in a derived table and then join back to the base table for the update.
February 23, 2011 at 10:59 am
Hi
You have bring the data to update in the temp table first and thenuse the update statement.
you can not use group by with update.
:w00t:
February 23, 2011 at 3:25 pm
There may be a more elegant way to handle this, but unless you are talking massive amounts of data (and then I'm not sure about performance) I like subqueries instead of temp tables.
-- Set initial values for work1 and work2
Update MyTable SET Work1 = 0, Work2 = 0
DECLARE @Source int
SET @Source=34560003
UPDATE MyTable SET
work1=TotalHours
FROM (SELECT Company, Loc_No, Emp_No,
SUM(Hours_Reg+Hours_Ot) TotalHours
FROM MyTable
WHERE Source = @Source
GROUP BY Company, Loc_No, Emp_No
) SubQ
WHERE MyTable.Company = SubQ.Company
AND MyTable.Loc_No = SubQ.Loc_no
AND MyTable.Emp_No = SubQ.Emp_No
AND MyTable.Source = @Source
UPDATE MyTable SET
work2=TotalHours
FROM (SELECT Company, Loc_No, Emp_No,
SUM(OtherHours) TotalHours
FROM MyTable
WHERE Source = @Source
AND PayCode NOT IN (SELECT DISTINCT Code FROM PayCodeTable WHERE PayType='V')
GROUP BY Company, Loc_No, Emp_No
) SubQ
WHERE MyTable.Company = SubQ.Company
AND MyTable.Loc_No = SubQ.Loc_no
AND MyTable.Emp_No = SubQ.Emp_No
AND MyTable.Source = @Source
UPDATE MyTable SET
work1=work1+work2
, work2=0
WHERE Source = @Source
UPDATE MyTable SET
work2=TotalHours
FROM (SELECT Company, Loc_No, Emp_No,
SUM(OtherHours) TotalHours
FROM MyTable
WHERE Source = @Source
AND PayCode IN (SELECT DISTINCT Code FROM PayCodeTable WHERE PayType='V')
GROUP BY Company, Loc_No, Emp_No
) SubQ
WHERE MyTable.Company = SubQ.Company
AND MyTable.Loc_No = SubQ.Loc_no
AND MyTable.Emp_No = SubQ.Emp_No
AND MyTable.Source = @Source
And may I say fantastic job of setting up the problem with sample code and data. It made it very easy to work with.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply