T-sql – Have x number of rows returned for one record

t-sql

I have a Dates table with every single date and a column called weekly, biweekly or monthly . I want to show my data as it would appear if it were scheduled

ex

id    date   weekly   biweekly    monthly
1   01/01/2020  1       0            0
2   01/02/2020  0       1            0
3   01/03/2020  0       0            1

So what I want to do essentially is if:

weekly=1 then show me the current date and the next 6 rows (So all the other information will be the same for this 6 rows but the date will change only)

BIweekly=1 then show me the current date and the next 13 rows

MONTHLY=1 then show me the current date and the next 29 rows

id  date    weekly  biweekly    monthly
1   01/01/2020  1   0   0
1   01/02/2020  1   0   0
1   01/03/2020  1   0   0
1   01/04/2020  1   0   0
1   01/05/2020  1   0   0
1   01/06/2020  1   0   0
2   01/02/2020  0   1   0
2   01/03/2020  0   1   0
2   01/04/2020  0   1   0
2   01/05/2020  0   1   0
2   01/06/2020  0   1   0
2   01/07/2020  0   1   0
2   01/08/2020  0   1   0
2   01/09/2020  0   1   0
2   01/10/2020  0   1   0
2   01/11/2020  0   1   0
2   01/12/2020  0   1   0
2   01/13/2020  0   1   0
2   01/14/2020  0   1   0

Best Answer

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 <> 0 AND (ID + 1)%3 <> 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 <> 0 THEN 1 ELSE 0 END AS Monthly
  FROM CTE
  WHERE DATEADD(DAY, 1, DateValue) < 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 >= d1.DateValue 
  AND d2.DateValue <= 
    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<>fiddle example.

Related Question