2

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date.

I am providing the start_date and duration as the input to calculate end-date.

While calculating the end_date the Saturday and Sundays are not considered.

Also if the actual_date is having value that value is saved as end_date.

Example : Start_date = 18/05/2020 , Duration : 10. So the End_date should be calculated by excluding 23/05/2020 and 24/05/2020.

I had tried different ways to archive this.

The below is the alter query for the column end_date.

This is meeting half of my requirement but dont know how to exclude the weekends (Saturday and Sunday) from the start_date and duration.

ALTER TABLE [DBName].[dbo].[TestDate] ADD End_Date as CAST(
CASE 
WHEN (Actual_Date='' or Actual_Date IS NULL) then  DATEADD(day, Duration, Start_Date)
ELSE Actual_Date
END as DATE
)
GO

Please help me on this.

0
2

I believe the code you are looking for is

ALTER TABLE [DBName].[dbo].[TestDate] ADD End_Date as  
    DATEADD(D, 
            7 * (Duration / 5) -- Business weeks as calendar days
            + CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) -- Move weekend start to Friday, if needed
            + (Duration % 5) -- Day outside of full business weeks
            +IIF(DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6, 2, 0) -- Weekend Jump
            , StartDate
        )
GO

Basically, this separates out the whole business weeks and the additional days beyond. However, if you consider those additional days to come at the beginning, you just need to know if they go past a Friday or not and if so add some days. To simplify that, we first move Sat/Sun to Friday (you can use 1,0,0,0,0,0,2, in both spots, to move forward to Monday instead), then add 2 days if we end up in the weekend.

If you want to QA the end result, this code will give you all the component parts to review and validate:

WITH TestStartDates AS (
    SELECT TOP (7) 
        DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT 1)), CAST('2020-05-18' AS DATE)) as StartDate
    FROM Sys.Columns
),
TestAddDays AS (
        SELECT TOP (45) 
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) Duration
    FROM Sys.Columns
)
SELECT 
    StartDate
    , DATENAME(DW, StartDate) as Weekday
    , CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) OffsetWeekend
    , DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1), StartDate) WeekendToFriday
    , Duration
    , (Duration / 5) as WeeksToAdd
    , (Duration % 5) DaysOverBusinessWeeks
    , DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) StartDateWeekdayNum
    , CASE WHEN DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6 THEN 2 ELSE 0 END InitialWeekendJump -- 8 = If we pass Friday, jump 2
    , DATEADD(D, 7 * (Duration / 5) + CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) + (Duration % 5) + IIF(DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6, 2, 0), StartDate) as EndDate
    , DATENAME(DW, DATEADD(D, 7 * (Duration / 5) + CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) + (Duration % 5) + IIF(DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6, 2, 0), StartDate)) AS EndWeekDay
FROM TestAddDays CROSS JOIN TestStartDates
ORDER BY Duration, StartDate
5
  • it is a very elegant solution but on my machine it is adding an extra day. compare the results with my results below. I think it just need a quick tweaking. May 26 '20 at 13:38
  • Within the test query, if you can use '2020-05-18' in place of GetDate() then give me the first 11 results you expect (18th-28th), I can tweak.
    – Graham
    May 28 '20 at 20:45
  • Hi. I just came across this and was curious about what issues the other commenter saw in your solution, so I replaced GETDATE() with 2020-05-18 and ran your script. Some things don't seem to add up. In this screenshot showing the first 15 results, I've highlighted four that I find inconsistent with the other 11. I haven't analysed your script in depth (yet) and I realise that as this is an old thread, it might not be fun for you to try and remember what you were trying to do there, but I just wanted to let you know that the query does seem to have issues.
    – Andriy M
    Aug 23 '21 at 11:13
  • For during 2, the 22nd and 23rd are incorrect. Tuesday is the second business day following (alternatively, one could act as though the weekend we the first things Monday, versus last thing Friday). I will update the solution with a correction.
    – Graham
    Aug 24 '21 at 18:18
  • Thanks! I took the liberty of correcting the numbers needed to move forward to Monday instead of back to Friday (you said it was 2,0,0,0,0,0,1, but in fact it's 1,0,0,0,0,0,2, because it's plus one day for Sunday and plus two for Saturday). Other than that, this is really nice. I tried to find a way to get rid of the duplicated CHOOSE expression, but I suspect I may have to accept that it's impossible.
    – Andriy M
    Aug 25 '21 at 10:11
0

Iterative solution:

WITH cte AS ( SELECT id, 
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, start_date) > 5 
                                       THEN 9 - DATEPART(WEEKDAY, start_date) 
                                       ELSE 1 
                                       END, start_date) cur_date, duration-1 reminder
              FROM test
              UNION ALL
              SELECT id, 
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, cur_date) > 5 
                                       THEN 3 
                                       ELSE 1 
                                       END, cur_date), 
                     reminder - 1
              FROM cte
              WHERE reminder > 0 )
UPDATE test
SET test.end_date = cte.cur_date
FROM cte
WHERE test.id = cte.id
  AND cte.reminder = 0;

fiddle

PD. It is optimal hardly... for large duration values you may int. divide it by 7 and add according weeks (5 days per each) then iterate by int. reminder only.

2
  • How can i use the above code to the alter query?Please suggest
    – Rahul
    May 19 '20 at 5:40
  • @Rahul Alter table by one query then update with another one.
    – Akina
    May 19 '20 at 5:42
0

I have created the table:

use tempdb
go

if OBJECT_ID('dbo.testdate','U') is not null
   drop table dbo.testdate

create table [dbo].[TestDate] (
 actual_dt date null,
 Start_dt date null, 
 Duration int 
 )

then I create a function to do the dirty work for me:

--==============================================================================
GO
if OBJECT_ID('dbo.fnfoo','Fn') is not null
   drop FUNCTION dbo.fnfoo
go
CREATE FUNCTION dbo.fnfoo
( 
    @startdate date,
    @duration int
)
RETURNS date
AS
BEGIN

    declare
    --@startdate date ='3-MAY-2020',
    --@duration int = 20,
    @end_date date

    DECLARE @Radhe TABLE(I INT IDENTITY(1,1) 
                   NOT NULL PRIMARY KEY CLUSTERED, thedate date)

   SELECT @end_date  = DATEADD(DAY,@DURATION*2,@startdate)



;WITH dates_CTE (thedate) as 
(
  SELECT DATEADD(DAY,-3,@startdate)
  UNION ALL
  SELECT DATEADD(day, 1, thedate)
    FROM dates_CTE
   WHERE thedate <= @end_date 

)     
INSERT INTO @Radhe
SELECT thedate 
FROM dates_CTE
WHERE DATEPART(dw, [thedate]) NOT IN (1,7) --not a weekend
OPTION (MAXRECURSION 0)

    RETURN (
                SELECT W.THEDATE
                FROM @RADHE W 
                WHERE W.I = 
                (
                SELECT TOP 1 R.I+@DURATION-1
                FROM @RADHE R
                WHERE R.thedate >= @startdate
                ORDER BY R.I ASC
                )
    )
END
go
--==============================================================================
GO

and then I use that function inside the computed column in the following alter table:

GO
ALTER TABLE [dbo].[TestDate] ADD End_Date as CAST(
CASE 
WHEN (Actual_Dt='' or Actual_Dt IS NULL) then  dbo.fnfoo( start_dt ,duration)
ELSE Actual_Dt
END as DATE
)
GO

after that I do some tests, like inserting some data into our table and see if it comes out with the right figures:

insert into dbo.TestDate(
 Start_dt,
 Duration) values ('1-may-2018',10)

  insert into dbo.TestDate(
 Start_dt,
 Duration) values ('3-may-2018',20)

insert into dbo.TestDate(
 Start_dt,
 Duration) values ('1-may-2020',10)

 insert into dbo.TestDate(
 Start_dt,
 Duration) values ('3-may-2020',20)

This is what I get with the above examples. check if this is correct:

 select * from [dbo].[TestDate] 

enter image description here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.