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.