T-SQL代码将日期添加7天

The below statement adds 7 days to the shipment date if we miss the ship date. For example, something that was supposed to ship on 07/24/2014 did not get shipped for some reason. The following query adds 7 days (resulting in 07/31/2014) to the ship date.

如果我们错过发货日期,以下声明会在发货日期前加7天。例如,应该在2014年7月24日发货的东西由于某种原因没有发货。以下查询将7天(结果于2014年7月31日)添加到发货日期。

The only problem is the 'Test Ship' column adds 7 days to the shipment date only on Sundays if we miss the shipment date. What I mean is that for the jobs that had a scheduled ship date of 07/24/2014, and we missed shipping the jobs on that day, the below column 'Test Ship' updates the next scheduled ship day to 07/31/2014 only on SUNDAY.

唯一的问题是,如果我们错过了发货日期,“测试船”栏只会在星期日的7天内发货。我的意思是,对于预定发货日期为2014年7月24日的工作,我们错过了当天的工作运输,下面的列“测试船”将下一个预定的发货日更新为2014年7月31日只在星期日。

For example, say job J012345 did not get shipped on 07/24/2014. Then the 'Test Ship' column will update the date to tomorrow i.e 07/27/2014 (Sunday) instead of updating it on FRIDAY 07/25/2014. Am I missing anything here? How can I get 'Test Ship' to update the date on FRIDAY instead of SUNDAY?

例如,假设工作J012345未在2014年7月24日发货。然后,“测试船”列将更新日期到明天,即2014年7月27日(星期日),而不是在2014年7月25日星期五更新。我在这里遗漏了什么?我怎样才能让'Test Ship'更新星期五而不是SUNDAY?

'Test Ship' =
CASE
 WHEN j.JobStatus <> 'S'
 AND CAST(x.ExpectedDate AS date)
    < DATEADD(wk, DATEDIFF(wk, 3, CAST(GETDATE() AS date)), 3)
 THEN DATEADD(DAY, 4, CAST(DATEADD(week, DATEDIFF(week, 0, GETDATE()), -1) AS date)) 

 WHEN j.JobStatus ='S'
 THEN CAST(j.LastShippedDate AS date)

 ELSE CAST(x.ExpectedDate AS date)
END

1 个解决方案

#1

See the solution posted at SSC, which can be summarised as:

查看SSC发布的解决方案,可以概括为:

Make the code easier to read by separating the CASE conditions.

通过分离CASE条件使代码更易于阅读。

Replace the non-deterministic function (getdate()) with a deterministic solution.

用确定性解决方案替换非确定性函数(getdate())。

#1