sqlsql-serverdatesql-updatecalculated-field

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';