Get all data variations from same sql table based on multiple periods-CodePudding

I have two tables.

Table TDatos - contains ending and starting dates of each month
appointment - contains Valid From and Valid To dates

There is no any relation between the two tables, however, I want to select all appointment.[Valid From] date day differences with each month DatosT.[Month Start]

For example if appointment. [Valid From] is '2022-05-01' a selected data should look something like 120, 89, 61, 30, 0, -31 ... (for all 12 months of the year or more...)

I have done that using loop, but is there a way to do that without looping ?

/* declarations ... */ 

SET @Counter=1
WHILE ( @Counter <= 12)
BEGIN
    set @month_start = (select top 1 month_start_date from TDatos where MonthNumber = @Counter)
    
    insert into @AllRecords
    select 
    DATEADD(day, [@month_start, Valid From]) as NewDays
    from appointment
END

CodePudding user response：

use a recursive query or a RVC...

WITH 
T AS (select top 1 month_start_date, 1 AS N from TDatos where MonthNumber = 1
      UNION ALL 
      select top 1 month_start_date, N   1 from TDatos where MonthNumber = N   1)
    insert into @AllRecords
    select DATEADD(day, month_start, [Valid From])
    from appointment CROSS JOIN T;