DECLARE @ExistingDates TABLE (
    id int IDENTITY(1, 1) NOT NULL,
    ndpmDateProcessed datetime NOT NULL,
    PRIMARY KEY ( ndpmDateProcessed )
    )

INSERT INTO @ExistingDates
SELECT DISTINCT ndpmDateProcessed
FROM tbl_NeedAlloc_PublishedMain
WHERE 
    ndpmPublishType = 'Standard Needs' AND
    ndpmDateProcessed IS NOT NULL --remove if not needed
   

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
    CROSS JOIN cteDigits [1000s]     
),
CalendarDates AS (
    SELECT DATEADD(DAY, tally, Base_Date) AS CalendarDate
    FROM (
        SELECT 
            MIN(ndpmDateProcessed) AS Base_Date,
            MAX(ndpmDateProcessed) AS Last_Date  
        FROM @ExistingDates
    ) AS Control_Dates
    INNER JOIN cteTally ON
        cteTally BETWEEN 0 AND DATEDIFF(DAY, Control_Dates.Base_Date, Control_Dates.Last_Date)
)
--INSERT INTO @Values ( ... )
SELECT 
    DATEADD(DAY, 1, ed_start.ndpmDateProcessed) AS startDate,
    CAST(ed_end.ndpmDateProcessed AS date) AS endDate,
    dbo.YMFmt (DATEADD (MONTH, 1, ed_start.ndpmDateProcessed)) AS DropMonth, 
    dbo.YMFmt (DATEADD (MONTH, 2, ed_start.ndpmDateProcessed)) AS alocAllocationYM
FROM CalendarDates cd
LEFT OUTER JOIN @ExistingDates ed_start ON
    ed_start.ndpmDateProcessed = cd.CalendarDate
LEFT OUTER JOIN @ExistingDates ed_end ON
    ed_end.id = ed_start.id + 1