CODEWITHSUNDEEP
sql-servert-sqldate
wali
wali

Reputation: 239

Find start date based upon End Date and Duration - 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.

Upvotes: 1

Views: 1467

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

A bit harder to read, but slightly faster

SELECT ProjectId, 
DATEADD(d, -Duration - CASE DATEDIFF(d, +Duration, EndDate) % 7 
  WHEN 5 THEN 1 
  WHEN 6 THEN 2 
  ELSE 0 END
  , EndDate)
FROM tblProjects

Upvotes: 1

Victor Zakharov
Victor Zakharov

Reputation: 26454

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

Upvotes: 4

Related Questions