根据结束日期和持续时间

根据结束日期和持续时间 - T-SQL查找开始日期(Find start date based upon End Date and Duration - T-SQL)

我在SQL Server中有一个表具有以下属性。

ProjectID || Start_Date || End Date || Duration(Days) 1 10-Jan-2013 5 2 02-FEB 2013 16 3 26-Mar-2013 50 . . .

我想根据合格的日期（周一至周五）找到开始日期。 例如，结束日期：1月10日开始日期为1月4日，1月5日和6月6日为周六和周日。

我想知道在T-SQL（函数，自定义T-SQL块）中如何实现这一点。 任何指导和帮助都非常感谢。

I have a table in SQL Server which have following attributes.

ProjectID || Start_Date || End Date || Duration(Days) 1 10-Jan-2013 5 2 02-FEB 2013 16 3 26-Mar-2013 50 . . .

I want to find start dates based upon the qualified days (Monday-Friday). For example for End Date: 10 January start date will be 04 January as 5 and 6 January are Saturday and Sunday.

I want to know how this could be possible in T-SQL (Function,Custom T-SQL Block). Any guidance and help is highly appreciated.

最满意答案

这应该做到这一点：

WITH tblProjects2 AS ( SELECT ProjectId, DATEADD(DAY, -Duration, EndDate) AS StartDate FROM tblProjects ) SELECT ProjectId, CASE WHEN DATENAME(DW, StartDate) = 'Sunday' THEN DATEADD(day, -2, StartDate) WHEN DATENAME(DW, StartDate) = 'Saturday' THEN DATEADD(day, -1, StartDate) ELSE StartDate END AS ProperStartDate FROM tblProjects2

这种方法相当简单 - 当你的新约会在一个周末时，减去1或2天，取决于它是分别是周六还是周日。

tblProjects测试用例结构：

CREATE TABLE [dbo].[tblProjects]( [ProjectId] [int] NULL, [StartDate] [date] NULL, [EndDate] [date] NULL, [Duration] [int] NULL )

测试用例数据相同：

INSERT INTO tblProjects VALUES (1, NULL, '10-Jan-2013', 5); INSERT INTO tblProjects VALUES (2, NULL, '02-FEB-2013', 16); INSERT INTO tblProjects VALUES (3, NULL, '26-Mar-2013', 50);

编辑 - 功能相同，使用功能：

CREATE FUNCTION dbo.getStartDate(@EndDate Date, @Duration int) RETURNS DATE AS BEGIN DECLARE @newDate DATE; SET @newDate = DATEADD(day, -@Duration, @EndDate); RETURN (CASE WHEN DATENAME(DW, @newDate) = 'Sunday' THEN DATEADD(day, -2, @newDate) WHEN DATENAME(DW, @newDate) = 'Saturday' THEN DATEADD(day, -1, @newDate) ELSE @newDate END) END;

然后你可以像这样重写上面的查询：

SELECT ProjectId, dbo.getStartDate(EndDate, Duration) AS StartDate FROM tblProjects

This should do it:

WITH tblProjects2 AS ( SELECT ProjectId, DATEADD(DAY, -Duration, EndDate) AS StartDate FROM tblProjects ) SELECT ProjectId, CASE WHEN DATENAME(DW, StartDate) = 'Sunday' THEN DATEADD(day, -2, StartDate) WHEN DATENAME(DW, StartDate) = 'Saturday' THEN DATEADD(day, -1, StartDate) ELSE StartDate END AS ProperStartDate FROM tblProjects2

The approach is rather simple - when your new date falls on a weekend, subtract 1 or 2 days, depending on whether it's Saturday or Sunday respectively.

Test case structure for tblProjects:

CREATE TABLE [dbo].[tblProjects]( [ProjectId] [int] NULL, [StartDate] [date] NULL, [EndDate] [date] NULL, [Duration] [int] NULL )

Test case data for same:

INSERT INTO tblProjects VALUES (1, NULL, '10-Jan-2013', 5); INSERT INTO tblProjects VALUES (2, NULL, '02-FEB-2013', 16); INSERT INTO tblProjects VALUES (3, NULL, '26-Mar-2013', 50);

EDIT - Same functionality, using a function:

CREATE FUNCTION dbo.getStartDate(@EndDate Date, @Duration int) RETURNS DATE AS BEGIN DECLARE @newDate DATE; SET @newDate = DATEADD(day, -@Duration, @EndDate); RETURN (CASE WHEN DATENAME(DW, @newDate) = 'Sunday' THEN DATEADD(day, -2, @newDate) WHEN DATENAME(DW, @newDate) = 'Saturday' THEN DATEADD(day, -1, @newDate) ELSE @newDate END) END;

Then you can rewrite the above query like this:

SELECT ProjectId, dbo.getStartDate(EndDate, Duration) AS StartDate FROM tblProjects