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 DECIMAL(8,2),
    WEEK_2 DECIMAL(8,2),
    WEEK_3 DECIMAL(8,2),
    WEEK_4 DECIMAL(8,2),
    WEEK_5 DECIMAL(8,2),
    WEEK_6 DECIMAL(8,2)
)

INSERT INTO @ROOMS_BY_DEPA_KEY1(OPRO_KEY)
VALUES (160000014),(160000015),(160000016),(160000017),(160000018),(160000019),(160000020)

So after filling some data table looks like this:

I must update each rows columns WEEK_1, WEEK_2, WEEK_3, WEEK_4, WEEK_5, WEEK_6 with results from this table, which is being generated from OPRO_KEY and KAPACITETA:

DECLARE @WEEKS_MONTH AS DBO.WEEKS_MONTH_Type
(
    WEEKOFMONTH INT,
    WEEKSTART DATE,
    WEEKEND DATE, 
    NO_DAYS INT, 
    AVERAGE_AVAI DECIMAL(8,2)
)

(it's table type because it's being used as functions parameter). Results from that table looks like this:

To get this second table I have a UDF:

dbo.f_SCAVAI_GetWeekAverage(@iOPRO_KEY, @WEEKS_MONTH, '1', @iKAPACITETA)

Table @WEEKS_MONTH is filled with some values (weekofmonth, datestart, dateend, no_days) and it stays the same for each call:

My problem is how to call this function for each OPRO_KEY and how to update week columns from returning table? Columns are updated according to WEEKOFMONTH. Example: WEEK_1 update where @WEEK_MONTHS.WEEK_OF_MONTH = 1, WEEK_2 where @WEEK_MONTHS = 2

回答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

回答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