t-SQL query complex rolling months

Question

Requirement is that, we need records for below 'production_date':

today='2015-08-06 00:00:00.000'.

As of today, production_date's in JULY only.

And in SEPT, production_date's from JULY AND AUGUST.

In OCT, production_date's from JULY, AUGUST and SEPT ..and so on..

BUT once in JAN 2016, production_date's from last 6 months only.

This means from FEB 2016, productions_date's from AUG 2015 TO JAN 2016

additonal info:

Production_date is a date column. And i'm filtering records based on above condition.

I tried:

Production_Date > DATEADD(month, DATEDIFF(month, 0,DATEADD(MM,DATEDIFF(m,DATEADD(month, DATEDIFF(month, 0, GETDATE()),0),'2016-01-01 00:00:00.000')-6,GETDATE())), 0)

AND

Production_Date< DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

Please let me know how to go about gettting this in a query.

What do you want? A query or a custom function? Did you attempt anything? Can you give an example of how you'll use this? — Emacs User
hi Emacs, I wanted a query with expression that can filter on above condition. — Kaushik49

Stuart Ainsworth Stuart Ainsworth · Accepted Answer · 2015-08-06T19:28:19

SELECT  columnlist
FROM    table
WHERE   ProductionDate >= DATEADD(MONTH, -6,
                                  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),
                                          0))
        AND ProductionDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
        AND ProductioNDate >= '2015-07-01'

t-SQL query complex rolling months

1 Answers