Bouke Bruinsma (5/6/2014)
ChrisM@home (5/6/2014)
Without sample data to test against...Try this.
;WITH Updater AS (SELECT
FTEHrsAVG,
NewFTEHrsAVG = AVG(CASE WHEN FlexVast = 'VAST' THEN FTEHrs ELSE NULL END)
OVER(PARTITION BY MWID, [Org eenheid code], DATEPART(ISO_WEEK,A.BEGINDATUM))
FROM DRPDATA o
WHERE EXISTS (
SELECT 1 FROM DRPDATA i
WHERE i.MWID = o.MWID
AND DATEPART(ISO_WEEK,i.BEGINDATUM)= DATEPART(ISO_WEEK,o.BEGINDATUM)
AND i.[Org eenheid code] = o.[Org eenheid code]
AND i.FlexVast = 'VAST')
)
UPDATE Updater SET FTEHrsAVG = NewFTEHrsAVG
You might not need the EXISTS check, it depends on what you want to do with unaffected partitions.
If the number of affected rows is relatively high and unaffected partitions should have FTEHrsAVG set to NULL, then try without the EXISTS check - it should be very quick.
That is amazing. Would you perhaps have a reference to other examples of CTE usage like these?
Thanks. That's why I lurk here - to learn stuff like this. It's what I'd recommend to you too. When something interesting or new comes up, copy it and pull it apart locally to see how it works - and if you can improve on it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden