Create View in SQL Server to create multiple rows for each date,with calculated date and identifier column

Question

I need to create a VIEW/Select statement that will take a start date, and create 3 different rows for each date. One row calculates 30 days, from the start date, another 60 days, and another 90 days. Also each row needs to have an identifier that states whether the date is 30 days, 60 days or 90 days from the start date. So say that the start date is 09/01/2020. Then the View will return this for each start date:

Row Header : Start Date, AdditionalDate, AdditionalDays
Row 1      : 01/01/2020, 02/01/2020, 30
Row 2      : 01/02/2020, 03/01/2020, 60
Row 3      : 01/01/2020, 04/01/2020, 90

Sorry, forgot to mention, but start date is from a table. Like (Select startDate from Appointment)

I am using Microsoft SQL Server and a new SQL user. Really appreciate any help and advice.

Thank you!

Answer 1

I am unsure why what do you expect from a view for that - views don't take parameters.

Here is, however, a query that, from a given date parameter, generates three rows, at 30, 60 and 90 days later:

declare @start_date date = '2020-01-01';
select 
    @start_date,
    dateadd(day, additional_days, @start_date) additional_date,
    additional_days
from (values (30), (60), (90)) x(additional_days)

I am unsure whether you really mean 30 days or a month. If you want months, then:

declare @start_date date = '2020-01-01';
select 
    @start_date,
    dateadd(month, additional_months, @start_date) additional_date,
    additional_months
from (values (1), (2), (3)) x(additional_months)

On the other hand, if you are starting from an existing table, then that's a cross join:

select 
    t.*, 
    dateadd(day, x.additional_days, t.start_date) additional_date,
    x.additional_days
from mytable t
cross join (values (30), (60), (90)) x(additional_days

Answer 2

You cannot use a view for this purpose, but you can use an inline table-valued function:

create function dates (
    @date date,
    @period int,
    @num int
)
returns table 
as return 
        with dates as (
              select @date as start_date,
                     dateadd(day, @period, @date) as additional_date,
                     @period as additional_days, 1 as n
              union all
              select start_date,
                     dateadd(day, @period, additional_date),
                     additional_days + @period, n + 1
              from dates
              where n < @num
             )
        select start_date, additional_date, additional_days
        from dates;

Here is a db<>fiddle.

Answer 3

You can utilize a recursive cte:

with cte as 
 ( Select 1 as Header
     ,Start
     ,dateadd(day, 30, Start) as AdditionalDate
     ,30 as AdditionalDays 
   from Appointment
   union all
   select Header+1
     ,Start
     ,dateadd(day, 30, AdditionalDate)
     ,AdditionalDays + 30
   from cte
   where Header <= 2
 )
Select * from cte

Or for adding months instead of days:

with cte as 
 ( Select 1 as Header
     ,Start
     ,dateadd(month, 1, Start) as AdditionalDate
     ,datediff(day, Start, dateadd(month, 1, Start)) as AdditionalDays 
   from Appointment
   union all
   select Header+1
     ,Start
     ,dateadd(month, 1, AdditionalDate)
     ,datediff(day, Start, dateadd(month, 1, AdditionalDate))
   from cte
   where Header <= 2
 )
Select * from cte

See fiddle