SQL - Set Datetime Column to Closest Weekday

Question

Forgive me if this sounds all over the place, I am not an expert with SQL.

So I have a few stored procedures that are either inserting into or updating a specific table.

Currently, for each stored procedure, there is logic in place for a specific datetime column (We'll call it MailingDate) to use various case statements in the select clause to set it to either a Friday or Saturday, depending on what weekend date it falls on. Just for a frame of reference, that logic looks like this:

alias.MailingDate = 
CASE DATEPART(WEEKDAY, DATEADD(dd, aliasTwo.MailingDays, aliasThree.DateScheduled))
    WHEN 7 THEN 
            CASE aliasTwo.MailingDays
                WHEN 0 THEN DATEADD(DAY, 2, DATEADD(dd, aliasTwo.MailingDays, aliasThree.DateScheduled))    -- Saturday is 7, MailingDate fell on Saturday the 1st, goto Monday.
                ELSE DATEADD(DAY, -1, DATEADD(dd, aliasTwo.MailingDays, aliasThree.DateScheduled))      -- Saturday is 7, MailingDate fell on Saturday, goto Friday.
            END
    WHEN 1 THEN DATEADD(DAY, 1, DATEADD(dd, aliasTwo.MailingDays, aliasThree.DateScheduled))                -- Sunday is 1, MailingDate needs to goto Monday.
    ELSE DATEADD(dd, aliasTwo.MailingDays, aliasThree.DateScheduled)                                        -- MailingDate fell on Weekday.
END, -- MailingDate

Essentially, I have to replace that logic to account for this:

There is a Configuration table (with no PK->FK relationship to any other table) that looks something like this (the items in the value column can be changed anytime because the goal is for it to be dynamic):

Configurations: 

|ConfigurationId|      Keyword        |      Value         |
------------------------------------------------------------
|1              |MailingDays:Sunday   |Value1              |
|2              |MailingDays:Monday   |                    |
|3              |MailingDays:Tuesday  |Value2              |
|4              |MailingDays:Wednesday|Value3|Value1       |
|5              |MailingDays:Thursday |                    |
|6              |MailingDays:Friday   |Value1|Value2|Value3|
|7              |MailingDays:Saturday |Value3|Value1       |

Each row represents a weekday, and in each weekday there is a varchar() Value column that contains a pipe separated list of varchar() values from another table. The idea is that when you are updating the Mailing Date as seen above, I need to dynamically use this table to check for each specific value (Value1, Value2, Value3), which weekday each value is attributed to, and move the Mailing Date to the closest weekday.

For example, if I am accounting for all Mailing Dates that are attributing to Value1, I would need to dynamically search the Configurations table, see that Value1 is attributed to Sunday, Wednesday, Friday, and Saturday, and move it to the closest weekday BASED ON what the weekday of aliasTwo.MailingDays is. In this case, if aliasTwo.MailingDays is a Monday, then the alias.MailingDate would have to be set as Sunday. Whereas if aliasTwo.MailingDays was a Tuesday, I would have to move alias.MailingDate to a Wednesday.

There is a special use case for if alias.MailingDays is a weekday that is equidistant between two other weekdays:

For example, if I was searching the Configurations table for Value2, I would see that Value2 is attributed to Tuesday and Friday. So, in this case, if aliasTwo.MailingDays was a Sunday, then alias.MailingDate would go to the next Weekday it was attributed to. In this case, since Sunday is equidistant from Friday and Tuesday, it would move forward to Tuesday.

That is essentially what needs to happen. SQL is definitely not my strongest language, so any beneficial help on this problem would be much appreciated.

Please let me know if you need further clarification.

Thanks!

Answer 1

I'd create a mapping table between days, and what that day moves to. Then create a separate table mapping values to that date mapping. Gets rid of your overloaded column and allows you to just maintain that date mapping table with what sounds like pretty crazy date transformation logic

if object_id('tempdb.dbo.#DateMappings') is not null drop table #DateMappings
create table #DateMappings
(
    MappingId int identity(1,1),
    ThisDOW int,
    MapsToThisDOW int

    primary key clustered (MappingId),
    unique (THisDOw, MapsToTHisDOW)
)

-- This would be replaced with what your actual logic is; i'm not going to try to figure out your rules
;with nums (num) as
(
    select 1 union all
    select 2 union all
    select 3 union all
    select 4 union all
    select 5 union all
    select 6 union all
    select 7
)
insert into #DateMappings
(
    ThisDOW,
    MapsToThisDOW
)
select 
    ThisDOW = a.num,
    MapsToThisDOW = b.num
from nums a
cross join nums b

if object_id('tempdb.dbo.#ValueDateMappings') is not null drop table #ValueDateMappings
create table #ValueDateMappings
(
    Value varchar(100),
    DateMappingId int

    primary key clustered (Value, DateMappingId)
)

-- This maps a SINGLE value to a SINGLE mapping construct. You can then use the information for that date mapping to construct or adjust the mailing date as you see fit
insert into #ValueDateMappings
values
    ('Value 1', 1),
    ('value 1', 3)
    -- ...etc