How to dynamically allocate all months based to a column on condition in SQL Server?

Question

I have a table in which I am storing the Product Name and it's Renewal Date along with the payment plan (Monthly/Quarterly/Yearly). Now if the payment plan of the product is Yearly or Monthly it will display the get the month of Renewal and show the Rate/Amount against that month but if the payment plan is Monthly it should display the Rate/Amount in front of each month as shown below.

For example if a product named ABC has payment plan of Yearly, subscription rate of 276 and Renewal Date 2019-12-01 and there is another product XYZ with payment plan of Monthly, subscription rate of 17 and Renewal Date 2019-08-15 then the result set I want should something like this

ProductName    RenewalMonth   Rate
------------------------------------
ABC            December       276
XYZ            January         17
XYZ            February        17
XYZ            March           17
XYZ            April           17
XYZ            May             17
XYZ            June            17
XYZ            July            17
XYZ            August          17
XYZ            September       17
XYZ            October         17
XYZ            November        17
XYZ            December        17

Here is the query which I have wrote which is returning data that's present in the database fine but not the months other than December for Product XYZ. Keeping in mind this should only display same rate for all other months provided in the desired dates where Payment Plan is 'Monthly', for other payment plans it should show rate in front of given month as present in the database.

Sample data is as follows:

CREATE TABLE ItemDefinition 
(
    ID INT NOT NULL,
    ProductName VARCHAR(50),
    PaymentPlan VARCHAR(50),
    RenewalDate DATETIME,
    UnitRate NUMERIC(18, 0)
);

INSERT INTO ItemDefinition 
VALUES (1, 'ABC', 'Yearly', '2019-12-01', 276),
       (1, 'XYZ', 'Monthly', '2019-08-15', 17);

And the query I am writing is

SELECT 
    ProductName, SUM(UnitRate) Rate,
    DATENAME(MONTH , DATEADD(MONTH , MONTH(RenewalDate)-1 , '1900-01-01')) RenewalMonth
FROM
    ItemDefinition 
WHERE 
    MONTH(RenewalDate) IS NOT NULL
    AND RenewalDate BETWEEN @dtStart AND @dtEnd
GROUP BY 
    ProductName, MONTH(RenewalDate)
ORDER BY 
    MONTH(RenewalDate)

Answer 1

It might be something like this:

DECLARE @DateBeg DATE = '2019-01-01'
       ,@DateEnd DAte = '2020-12-01';


WITH Ranges AS
(
    SELECT *
          ,@DateBeg AS [DateBeg]
          ,@DateEnd AS [DateEnd]
    FROM ItemDefinition DS
)
SELECT *
      ,DATENAME(MONTH ,ISNULL([GeneratedDate], [RenewalDate])) AS RenewalMonth
FROM Ranges
OUTER APPLY
(
    SELECT DATEADD(MONTH, [number], [DateBeg])
    FROM 
    (
        select number 
        from master.dbo.spt_values
        where [type] = 'P'
    ) numbers
    WHERE DATEADD(MONTH, [number], [DateBeg]) < [DateEnd]
        AND [PaymentPlan] = 'Monthly'
) AutoDates ([GeneratedDate]);

You can change the DateEnd parameter to something less and you will see how less months are generated.

The idea is to have start and end date for each row and depending on it to generate your months.

---

To get the records for the years use the following:

WITH Ranges AS
(
    SELECT *
          ,@DateBeg AS [DateBeg]
          ,@DateEnd AS [DateEnd]
    FROM ItemDefinition DS
)
SELECT *
      ,DATENAME(MONTH ,ISNULL([GeneratedDate], [RenewalDate])) AS RenewalMonth
      ,IIF([PaymentPlan] = 'Monthly', [UnitRate], IIF(CONVERT(VARCHAR(7), [RenewalDate], 121) = CONVERT(VARCHAR(7), [GeneratedDate], 121), [UnitRate], NULL))
FROM Ranges
OUTER APPLY  
(
    SELECT DATEADD(MONTH, [number], [DateBeg])
    FROM 
    (
        select number 
        from master.dbo.spt_values
        where [type] = 'P'
    ) numbers
    WHERE DATEADD(MONTH, [number], [DateBeg]) < [DateEnd]
) AutoDates ([GeneratedDate]);

or the following to get the year rate for the first record:

DECLARE @DateBeg DATE = '2019-01-01'
       ,@DateEnd DAte = '2020-12-01';


WITH Ranges AS
(
    SELECT *
          ,@DateBeg AS [DateBeg]
          ,@DateEnd AS [DateEnd]
    FROM ItemDefinition DS
)
SELECT *
      ,DATENAME(MONTH ,ISNULL([GeneratedDate], [RenewalDate])) AS RenewalMonth
      ,IIF([PaymentPlan] = 'Monthly', [UnitRate], IIF([number] = 0, [UnitRate], NULL))
FROM Ranges
OUTER APPLY  
(
    SELECT DATEADD(MONTH, [number], [DateBeg])
          ,[number]
    FROM 
    (
        select number 
        from master.dbo.spt_values
        where [type] = 'P'
    ) numbers
    WHERE DATEADD(MONTH, [number], [DateBeg]) < [DateEnd]
) AutoDates ([GeneratedDate], [number]);