Get a list of month & year for particular duration

May 11, 2017

Here i have requirement to display month,year wise record count from table.The condition is starting month,year  would be month & year of Min start date in table and end month,year would be current month & year.

So here a query to fulfill the above requirement.

DECLARE @FromDate DATETIME, @ToDate DATETIME
SELECT @FromDate=MIN(CreatedDate) FROM dbo.U***
SET @ToDate = GETDATE()

DECLARE @MonthsAndYears table (Month INT, Year int)

;WITH dates AS
(
    SELECT @FromDate 'date'
        UNION ALL
    SELECT DATEADD(dd, 1, t.date)
        FROM dates t
    WHERE DATEADD(dd, 1, t.date) <= @ToDate
)
INSERT INTO @MonthsAndYears
SELECT
    DATEPART(MONTH, date),
    DATEPART(YEAR, date)
FROM dates
GROUP BY
DATEPART(MONTH, date),
DATEPART(YEAR, date)
option (maxrecursion 0)

SELECT *,CONCAT(DATENAME(MONTH,DATEADD(MONTH,  [Month] - 1, 0)),',',[Year]) AS Duration FROM @MonthsAndYears

Result is :

Capture

By using outer apply with your table on column created date (month & year part) to month and year column in query result ,we can get the desired result.