Code to list dates end of month dates between specific start and end date ranges

Transact-SQL
1

Hello,

Looking to list out end of month dates between a start and an end point in specific data set.

For instance,

Id start_date end_date

1 07/01/2017 08/01/2018

2 06/01/2015 08/01/2017

result

1 07/01/2017

1 08/01/2017

1 09/01/2017

1 10/01/2017

1 11/01/2017

.

1 08/01/2018

2 06/01/2015

2 07/01/2015

2 08/01/2015

2 09/01/2015

2 10/01/2015

2 11/01/2015

.

2 07/01/2017

I have a large data set to loop through. An efficient solution is greatly appreciated.

Thank you in advance.

2

Don't have usable test data, but I'm almost certain this is it:

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT 0 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
)
SELECT DATEADD(MONTH, ct.number, start_date) AS dates
FROM dbo.main_table mt
INNER JOIN cteTally10K t ON ct.number BETWEEN 0 AND DATEDIFF(MONTH, start_date, end_date)
3

I have a data set of nearly 500K rows. the name of the table is 'sample_set' and the columns are as follows id, startdate__c, enddate__C. could you please help me incorporate the informaiton in to your solution.

Thank you for help. Greatly appreaciate it.

4 
;WITH cteTally10 AS ( SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number) ), cteTally100 AS ( SELECT 0 AS number FROM cteTally10 c1 CROSS JOIN cteTally10 c2 ), cteTally10K AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cteTally100 c1 CROSS JOIN cteTally100 c2 ) 
SELECT ss.id, DATEADD(MONTH, ct.number, ss.startdate__c) AS enddate
FROM sample_set ss INNER JOIN cteTally10K t ON ct.number BETWEEN 0 AND DATEDIFF(MONTH, startdate__c, enddate__c)