[SOLVED] Update a date field in selected records with a calculated, relative date

Update a date field in selected records with a calculated, relative date

The end result I am looking for is to have a query that will find specific records in Table1, use a field in joined Table2 to retrieve a day of the week, use a formula to determine the next occurring calendar date of that day of week and update date fields in Talbe1 with that calendar date.

Taken individually, the pieces are not that complicated. However putting the pieces together is proving to be a challenge.

If someone can give me the structure of the query I would greatly appreciate it. By "structure" I mean the order of DECLARE, SELECT, FROM, UPDATE, etc. statements.

Here are the pieces that I have:

DECLARE Var1 INT -- This is the day of week from Field5 in Table2
    Var2 DATE  -- This is the next calendar date to use in the update

SET Var2 = [calculation for next calendar date]

UPDATE Table1.
 SET Field6 = Var2
     ,Field7 = Var2
     ,Field8 = Var2

FROM Table1
   INNER JOIN Table2 on Table1.Field1 = Table2.Field1 and
          Table1.Field2 = Table2.Field2

WHERE Table1.Field3 IS NULL and Table2.Field4 is 'VALUE'

Solution

Following might help:

-- Declare variables
DECLARE @Var1 INT; 
DECLARE @Var2 DATE;

-- Set the day of the week from Table2
SELECT @Var1 = t2.Field5
FROM Table1 t1
INNER JOIN Table2 t2 
    ON t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2
WHERE t1.Field3 IS NULL AND t2.Field4 = 'VALUE';

-- Calculate the next occurring calendar date for the given day of the week
SET @Var2 = DATEADD(DAY, (7 - DATEPART(WEEKDAY, GETDATE()) + @Var1) % 7, GETDATE());

-- Update Table1 with the calculated date
UPDATE t1
SET Field6 = @Var2,
    Field7 = @Var2,
    Field8 = @Var2
FROM Table1 t1
INNER JOIN Table2 t2 
    ON t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2
WHERE t1.Field3 IS NULL AND t2.Field4 = 'VALUE';