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]
