Have x number of rows returned for one record
You can use CROSS APPLY and a CASE statement in your WHERE clause to produce the results you're after.

Setup:

CREATE TABLE DateTable
(
 ID INT,
 DateValue DATETIME,
 Weekly INT,
 Biweekly INT,
 Monthly INT
)

;WITH CTE AS
(
  SELECT 1 AS ID,
    CAST('2020-01-01' AS DATETIME) AS DateValue,
    1 AS Weekly, 
    0 AS Biweekly, 
    0 AS Monthly
  UNION ALL
  SELECT ID + 1 AS ID,
    DATEADD(DAY, 1, DateValue) AS DateValue,
    CASE WHEN (ID + 1)%2 &lt;&gt; 0 AND (ID + 1)%3 &lt;&gt; 0 THEN 1 ELSE 0 END AS Weekly, 
    CASE WHEN (ID + 1)%2 = 0 THEN 1 ELSE 0 END AS Biweekly, 
    CASE WHEN (ID + 1)%3 = 0 AND (ID + 1)%2 &lt;&gt; 0 THEN 1 ELSE 0 END AS Monthly
  FROM CTE
  WHERE DATEADD(DAY, 1, DateValue) &lt; DATEADD(YEAR, 1, '2020-01-01')
)

INSERT INTO DateTable
SELECT ID, DateValue, Weekly, Biweekly, Monthly
FROM CTE
OPTION (MAXRECURSION 400)


Query:

SELECT d1.ID, d2.DateValue, d1.Weekly, d1.Biweekly, d1.Monthly
FROM DateTable d1
CROSS APPLY 
(
  SELECT * 
  FROM DateTable 
) d2
WHERE d1.ID IN (1, 2, 3)
  AND d2.DateValue &gt;= d1.DateValue 
  AND d2.DateValue &lt;= 
    CASE
      WHEN d1.Weekly = 1 THEN DATEADD(DAY, 6, d1.DateValue)
      WHEN d1.Biweekly = 1 THEN DATEADD(DAY, 13, d1.DateValue)
      WHEN d1.Monthly = 1 THEN DATEADD(MONTH, 1, d1.DateValue)
     END


You can see it working in this db&lt;>fiddle example.