November 23, 2010 at 10:44 am
Hi,
I have written the following update query that I can't get to work:
UPDATE @tblResults
SET TonnesPerWeek =
case
when SUM(Qty) <> 0 then
CASE
WHEN MIN(DateRequested) = MAX(DateRequested) THEN SUM(Qty)
ELSE SUM(Qty) / DATEDIFF(d, MIN(DateRequested), MAX(DateRequested)) * 7
END
end
FROM @tblResults GROUP BY Rep, CustAcc, ProductGroup;
I get an error 'Incorrect syntax near the keyword 'GROUP'. I do need the update to be based on these groups.
Any help please?
Thanks in advance,
November 23, 2010 at 12:26 pm
You can't use a GROUP BY within your update statement directly.
It's not really clear what you're looking for, but due to the GROUP BY aggregation I have to assume you're trying to violate normalization...
Several ways to resolve it (in my personal order of preference):
1)
If you're not using this code inside a stored procedure (assumed, since you're using a table variable), you could simply create a view and join to it, if needed.
2)
if within a sproc, use a separate (temp) table or a CTE when you need the TonnesPerWeek values (assuming you're not using any type of loop to update your data)
3)
use the OVER() clause during populating your temp table
4)
use a CTE to calculate the TonnesPerWeek and update your temp table based on that cte.
here's the CTE I would use:
;
WITH cte AS
(
SELECT
Rep, CustAcc, ProductGroup,
CASE
WHEN SUM(Qty) <> 0 THEN
CASE
WHEN MIN(DateRequested) = MAX(DateRequested) THEN SUM(Qty)
ELSE SUM(Qty) / DATEDIFF(d, MIN(DateRequested), MAX(DateRequested)) * 7
END
END AS TonnesPerWeekCalculated
FROM @tblResults t GROUP BY Rep, CustAcc, ProductGroup
)
SELECT * FROM cte
November 23, 2010 at 3:06 pm
Thanks for the help. It is used within a SProc btw.
I eventually used the following which seemed to work..
; WITH cte AS
(
SELECT
Rep, CustAcc, ProductGroup,
CASE
WHEN SUM(Qty) <> 0 THEN
CASE
WHEN MIN(DateRequested) = MAX(DateRequested) THEN SUM(Qty)
ELSE SUM(Qty) / DATEDIFF(d, MIN(DateRequested), MAX(DateRequested)) * 7
END
END AS TonnesPerWeekCalculated
FROM @tblResults t GROUP BY Rep, CustAcc, ProductGroup
)
--SELECT * FROM cte;
update @tblResults set TonnesPerWeek = TonnesPerWeekCalculated
FROM @tblResults R INNER JOIN cte C
ON R.Rep = C.Rep
AND R.CustAcc = C.CustAcc
AND R.ProductGroup = C.ProductGroup;
November 23, 2010 at 3:33 pm
Is there any specific reason for using the least preferred method?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply