我在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 tblProjectsThis 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 tblProjects2The 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更多推荐
发布评论