whitz11 whitz11 - 3 months ago 25
SQL Question

6 month rolling data

This query is currently pulling back 6 month data, so if I run it today I get data from 01/03/2016 to 06/09/2016. What I would like is the data to stop at 31/08/2016.

And then the following month start at 01/04/2016 to 30/09/2016 and so on.

Many thanks

select i.Date
from table as i
where i.Date >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)
order by i.Date desc

Answer

Try :

select i.Date 
from table as i
where i.Date between 
  Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6,  current_timestamp)), 0) 
  AND DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, 0)
order by i.Date desc