AZGroups.com

I have the following script:

SELECT     TOP 100 PERCENT 'RVUPTConsecutive' AS RVUPT, r1.Campus, r1.YYYYMM, SUM(r1.RVUs) / SUM(r1.Visits) AS RVUPerVisit,
                      SUBSTRING(r1.YYYYMM, 5, 2) + '/1/' + SUBSTRING(r1.YYYYMM, 1, 4) AS Month
FROM         PCS_DERD.[Rollup - Productivity] r1 INNER JOIN
                      PCS_DERD.[Rollup - Productivity] r2 ON r1.Campus = r2.Campus
GROUP BY r1.Campus, r1.YYYYMM
ORDER BY r1.YYYYMM DESC

But I only want results for the last 4 months....I'm missing something small here but I can't for the life of me figure it out!  Thanks in advance!

If r1.YYYYMM is a "datetime" or "smalldatetime", then just  add:

--obviously use GETUTCDATE() if storing the date/time in that format
WHERE ( DATEDIFF( month, r1.YYYYMM, GETDATE() ) <= 4 )

If r1.YYYYMM is some other data type, then you'll want to use something different.

*I notice that you have a column "Month" in the result set, but that it appears to be a concatenation of a fully formatted date.

Kinda late on this but you may see a performance improvement by using a parameter to store the date you are comparing.

declare @4monthsago smalldatetime

set @4monthsago = DATEADD(month, -4, GETDATE())

Then to use Eric's code from the above post:

WHERE (  r1.YYYYMM >= @4monthsago)

However, this assumes that your table doesn't have any dates in the future.

If it does you could add the following line below the where statement:

AND r1.YYYYMM < GETDATE()

If your column is actually a string of somekind (not a datetime or smalldatetime) you can either convert (see "Cast and Convert" in BOL) to get your string to a date type or you can use the parameter we created above but make it a string and convert the result of the GETDATE() function to a string.

Both have pluses (sp?) and minuses.