Adjust Date to match saved day of week

I have a table that stores a StartDate and the name of the day of week that start date falls on. I don't know why, it is bad design but I didn't create it and can't change it. So of course, now we have some dates that don't match the day of week. To make it worse, the day of week is correct and the start date is incorrect. So what I need to do is adjust the dates so that each row's StartDate falls on that row's DayOfWeek. We can assume StartDate is always the minimum value so the target date will be the first [DayOfWeek] after the currently set StartDate.

So for example I have rows that look like this (8/23/10 was a Mon, 8/29/10 was a Sun):

StartDate    DayOfWeek
-----------------------
2010-08-23   Monday
2010-08-23   Tuesday
2010-08-29   Thursday

In row 2 you can see the date 开发者_如何学Gois supposed to be a Tuesday but it's actually a Monday. I need to end up with this:

StartDate    DayOfWeek
-----------------------
2010-08-23   Monday
2010-08-24   Tuesday
2010-09-02   Thursday

I always struggle when working with dates, but SQL is also not my strongest skill either. Thanks.

Stealing geofftnz's setup, and hoping this is the "clever" method he was thinking of:

declare @baddata table(StartDate datetime, [DayOfWeek] varchar(20))
insert into @baddata values('2010-08-23','Monday')
insert into @baddata values('2010-08-23','Tuesday')
insert into @baddata values('2010-08-29','Thursday')

select * from @baddata

;with Nums as (
    select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6
)
update b
set StartDate = DATEADD(day,Nums.n,StartDate)
from
    @baddata b
        inner join
    Nums
        on
            DATENAME(weekday,DATEADD(day,Nums.n,StartDate)) = [DayOfWeek]

select * from @baddata

For the third row, I'm getting a date in September, not August, but I think that the result is right and your sample result is incorrect?

There will be a clever way of doing this and a "let's just bash some data out" way. Here is the latter:

-- here's our bad data we want to fix:
declare @baddata table(StartDate datetime, [DayOfWeek] varchar(20))
insert into @baddata values('2010-08-23','Monday')
insert into @baddata values('2010-08-23','Tuesday')
insert into @baddata values('2010-08-29','Thursday')

-- we need to create a table containing valid date+day pairs for the 
-- range of our bad data

-- find max and min dates from our bad data
declare @MinDate datetime
declare @MaxDate datetime

select @MinDate = min(StartDate), @MaxDate = max(StartDate) from @baddata

-- offset max date by 7 days (which is the most we'll need to correct the date by)
set @MaxDate = dateadd(day,7,@MaxDate)

-- create a table matching dates to days
declare @dates table([Date] Datetime, [DayOfWeek] varchar(20))

declare @i int

-- populate the table with enough days to cover the range of your bad data
set @i = 0
while @i <= datediff(day,@MinDate, @MaxDate)
begin
    insert into @dates
    select dateadd(day, @i, @MinDate), datename(dw,dateadd(day, @i, @MinDate))

    set @i = @i + 1
end

-- show us our table
select * from @dates


-- update the ones with incorrect days
update bd
set
    bd.StartDate =  ( -- find the next date with a matching day
        select top 1
            d.[Date]
        from
            @dates d
        where
            d.[DayOfWeek] = bd.[DayOfWeek] and
            d.[Date] >= bd.StartDate
        order by
            d.[Date]
    )
from
    @baddata bd
        inner join @dates d on 
            d.[Date] = bd.StartDate
where
    bd.[DayOfWeek] != d.[DayOfWeek] -- date names don't match

select * from @baddata