asked on

help modifying a query

I need help modifying this query

DECLARE @END DATE = '12/01/2017'
DECLARE @Start DATE =DATEADD(month, -11, '12/01/2017')


;WITH _DateList([DATE]) AS(
SELECT @START
UNION ALL
SELECT DATES = DATEADD(MONTH,1,[DATE])   
 FROM _DateList 
 WHERE DATEADD(DAY,1,[DATE])<@END
)
SELECT DISTINCT 
        Yr                  =      YEAR(DATE)
       ,ID     =			RTRIM(e.ID)
       ,NumMonths    =      COUNT(*)
INTO #MemMonths
FROM  dbo.enrollkeys      e 
JOIN   program         p
       ON p.programid = e.programid
JOIN _DateList
       ON DATEADD(DAY,5,DATE) BETWEEN e.effdate AND e.termdate

GROUP BY RTRIM(e.ID), YEAR(DATE) 
OPTION (MAXRECURSION 2000)


SELECT * FROM  #MemMonths

Open in new window

I want is not year to show month/year and group by that. Also, I want to show only 12 months from the start date to the end date.

arnold

Change your declaration of @start and use @end instead
This way, when you change the declaration of end date, the start date will be 11 months earlier.
If you want twelve months, subtract 12
Or change and take away one year..

DECLARE @ENDDATE = '12/01/2017'
DECLARE @StartDATE =DATEADD(month, -12, @ENDDATE)

Open in new window

vbnetcoder

ASKER

I want it also to show the month and dates such as this

1/17 2/18 3/18 etc.

arnold

Change
GROUP BY RTRIM(e.ID), YEAR(DATE)
To group by by month rather than year.

arnold

Group by year,month

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbnetcoder

ASKER