SQL Server: get all previous values that are half a year in a month (not in the future)?
I have values like
201401 201411 201501 201504 201508 201606
If I select values for the last six months from 2015, I want to get 201411. If the last six months are from 20160, then nothing. If from 20150, then 201504.
I have a month column of varchar form 201601
.
How can I get the last six months relative to each month with some datatype objects like datepart functions?
+3
source to share
2 answers
Another option that will reduce the processing / conversion of the recording level
Declare @YourTable table (SomeCol varchar(6))
Insert Into @YourTable values
(201401),
(201411),
(201501),
(201504),
(201508),
(201606)
Declare @Target varchar(6) = '201508'
Select *
From @YourTable
Where SomeCol >= format(dateadd(MONTH,-6,cast(@Target+'01' as date)),'yyyyMM')
and SomeCol < @Target
Returns
201504
+3
source to share
Here's one way:
where left(val, 4) * 12 + right(val, 2) >= left('201501', 4) * 12 + right('201501', 2) - 6
It's pretty inelegant. Basically converts values to months from a date.
Alternatively, you can use date arithmetic:
where cast(val + '01' as date) >= dateadd(month, -6, cast('201501' + '01' as date)
In both cases, you can add a computed column and then an index on the computed column to make queries run faster.
+1
source to share