sql - 调整日期以匹配保存的星期几

Question

我有一个存储 StartDate 的表和开始日期所在的星期几的名称。我不知道为什么，这是糟糕的设计，但我没有创造它，也无法改变它。所以当然，现在我们有一些与星期几不匹配的日期。更糟糕的是，星期几是正确的，而开始日期是不正确的。所以我需要做的是调整日期，使每一行的 StartDate 落在该行的 DayOfWeek 上。我们可以假设 StartDate 始终是最小值，因此目标日期将是当前设置的 StartDate 之后的第一个 [DayOfWeek]。

例如，我的行看起来像这样(8/23/10 是星期一，8/29/10 是太阳):

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

在第 2 行中，您可以看到日期应该是星期二，但实际上是星期一。我需要结束这个:

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

我在处理日期时总是很挣扎，但 SQL 也不是我最擅长的技能。谢谢。

Answer 1

将有一个聪明的方法来做到这一点，并且是“让我们只处理一些数据”的方式。这是后者:

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