1

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.

  • 1
    Welcome to dba.se! Please help us out and provide a minimal reproducible example as it will help us better understand the data you're working with. Thanks! – John Eisbrener May 19 at 5:48
  • Tell us about the business requirement that dictates you exclude weekends but not holidays. Is a holiday like Christmas (or any holiday, really) part of your range of business days, unless it also happens to fall on a weekend? The reason I ask is that a solution that just counts by day of week vs. one that takes holidays into account are very different levels of complexity. Stating clear requirements leads to answers you can use the first time. – Aaron Bertrand May 19 at 12:48
  • @AaronBertrand Just want to point out that not all cultures treat Dec 25 as a holiday. – Graham May 26 at 6:35
  • @Graham Most businesses do, and it was just an example holiday anyway. Do any cultures not have any holidays whatsoever? Is the OP part of that culture? Why not let the OP answer if so? I think my point was valid. Yours I’m not so sure. – Aaron Bertrand May 26 at 11:56
  • @AaronBertrand The question "Do any cultures not have any holidays" is irrelevant to my comment, as I did not suggest such. In answer to the original question "Chirs is part of your range unless if falls on a weekend?" is No. Christmas is not in late May or early June. – Graham May 28 at 20:41
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.

| improve this answer | |
  • How can i use the above code to the alter query?Please suggest – Rahul May 19 at 5:40
  • @Rahul Alter table by one query then update with another one. – Akina May 19 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

| improve this answer | |
  • how can I improve this answer? the code works, so what is the reason for the downvote? – Marcello Miorelli May 20 at 7:30
0

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
            + CASE WHEN DATEPART(dw, StartDate) + (Duration % 5) = 8  THEN 1 WHEN DATEPART(dw, StartDate) + (Duration % 5) > 6 THEN 2 ELSE 0 END  -- Weekend Jump
            + (Duration % 5) -- Day outside of full business weeks
        , 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. For Sat, and anything beyond except Sunday, you need to add 2 days. Sunday the next business days is only 1 additional day.

If you want to QA the end result, this code you 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(GETDATE() 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
    , Duration
    , (Duration / 5) as WeeksToAdd
    , CASE WHEN DATEPART(dw, StartDate) + (Duration % 5) = 8  THEN 1 WHEN DATEPART(dw, StartDate) + (Duration % 5) > 6 THEN 2 ELSE 0 END InitialWeekendJump -- 8 = Following Sunday => +1 for Monday, otherwise add 2 to jump the weekend
    , (Duration % 5) DaysOverBusinessWeeks
    , DATEADD(D, 7 * (Duration / 5) + CASE WHEN DATEPART(dw, StartDate) + (Duration % 5) = 8  THEN 1 WHEN DATEPART(dw, StartDate) + (Duration % 5) > 6 THEN 2 ELSE 0 END + (Duration % 5), StartDate) as EndDate
    , DATENAME(DW, DATEADD(D, 7 * (Duration / 5) + CASE WHEN DATEPART(dw, StartDate) + (Duration % 5) = 8  THEN 1 WHEN DATEPART(dw, StartDate) + (Duration % 5) > 6 THEN 2 ELSE 0 END + (Duration % 5), StartDate)) AS EndWeekDay
FROM TestAddDays CROSS JOIN TestStartDates
ORDER BY Duration, StartDate
| improve this answer | |
  • 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. – Marcello Miorelli May 26 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 at 20:45

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.