Update Table From UDF Results

I have a table @ROOMS_BY_DEPA_KEY1 like this: DECLARE @ROOMS_BY_DEPA_KEY1 TABLE ( OPRO_KEY INT, KAPACITETA INT, WEEK_1 DECIMA

Solution 1:

If you will always have exactly 6 weeks, the simplest way to update the @ROOMS_BY_DEPA_KEY1 table would be using a pivot of the @Weeks_Month table:

UPDATE @ROOMS_BY_DEPA_KEY1
SET Week_1 = w.Week_1
    ,Week_2 = w.Week_2
    ,Week_3 = w.Week_3
    ,Week_4 = w.Week_4
    ,Week_5 = w.Week_5
    ,Week_6 = w.Week_6
FROM 
   (SELECT *
    FROM (SELECT 'Week_' + CAST(WeekOfMonth AS VARCHAR) AS WeekName
            ,Average_Avai
        FROM @WEEKS_MONTH) wm
    PIVOT (MAX(Average_Avai) 
           FOR WeekName IN (Week_1, Week_2, Week_3, Week_4, Week_5, Week_6)
          )p
    ) w

Solution 2:

I've managed to solve the problem with:

UPDATE RM
                SET RM.WEEK_1 = (
                    SELECT P.AVERAGE_AVAI FROM dbo.f_SCAVAI_GetWeekAverage(RM.DEPA_KEY, RM.OPRO_KEY, @WEEKS_MONTH, @cWorkDaysOnly, RM.KAPACITETA) P WHERE P.WEEKOFMONTH = 1
                ),
                RM.WEEK_2 = (
                    SELECT P.AVERAGE_AVAI FROM dbo.f_SCAVAI_GetWeekAverage(RM.DEPA_KEY,RM.OPRO_KEY, @WEEKS_MONTH, @cWorkDaysOnly, RM.KAPACITETA) P WHERE P.WEEKOFMONTH = 2
                ),
                RM.WEEK_3 = (
                    SELECT P.AVERAGE_AVAI FROM dbo.f_SCAVAI_GetWeekAverage(RM.DEPA_KEY,RM.OPRO_KEY, @WEEKS_MONTH, @cWorkDaysOnly, RM.KAPACITETA) P WHERE P.WEEKOFMONTH = 3
                ),
                RM.WEEK_4 = (
                    SELECT P.AVERAGE_AVAI FROM dbo.f_SCAVAI_GetWeekAverage(RM.DEPA_KEY,RM.OPRO_KEY, @WEEKS_MONTH, @cWorkDaysOnly, RM.KAPACITETA) P WHERE P.WEEKOFMONTH = 4
                ),
                RM.WEEK_5 = (
                    SELECT P.AVERAGE_AVAI FROM dbo.f_SCAVAI_GetWeekAverage(RM.DEPA_KEY,RM.OPRO_KEY, @WEEKS_MONTH, @cWorkDaysOnly, RM.KAPACITETA) P WHERE P.WEEKOFMONTH = 5
                ),
                RM.WEEK_6 = (
                    SELECT P.AVERAGE_AVAI FROM dbo.f_SCAVAI_GetWeekAverage(RM.DEPA_KEY,RM.OPRO_KEY, @WEEKS_MONTH, @cWorkDaysOnly, RM.KAPACITETA) P WHERE P.WEEKOFMONTH = 6
                )
                FROM @ROOMS_BY_DEPA_KEY1 RM