CODEWITHSUNDEEP
sqlsql-servert-sql
Сая Соколова
Сая Соколова

Reputation: 21

Sort dates order between dates

I have two dates as a parameters and list of current dates and same periods of previous years . I need partition list between parameters. Logic is if date between '20171201' AND '20180301' then 1, if date between '20161201' and '20170301' then 2 and so on. But count of periods in list not static, like:

DECLARE @DateEnd DATE = '20180331'
  , @DateBeg DATE = '20171201'

DECLARE @tab TABLE([date] DATE)
INSERT INTO @tab ([date])
VALUES('2014-12-01')
, ('2015-01-01')
, ('2015-02-01')
, ('2015-03-01')
, ('2015-12-01')
, ('2016-01-01')
, ('2016-02-01')
, ('2016-03-01')
, ('2016-12-01')
, ('2017-01-01')
, ('2017-02-01')
, ('2017-03-01')
, ('2017-12-01')
, ('2018-01-01')
, ('2018-02-01')
, ('2018-03-01')

Result should be

Date        Cnt
2014-12-01  4
2015-01-01  4
2015-02-01  4
2015-03-01  4
2015-12-01  3
2016-01-01  3
2016-02-01  3
2016-03-01  3
2016-12-01  2
2017-01-01  2
2017-02-01  2
2017-03-01  2
2017-12-01  1
2018-01-01  1
2018-02-01  1
2018-03-01  1

Upvotes: 0

Views: 113

Answers (2)

Squirrel
Squirrel

Reputation: 24803

use CASE WHEN satetement to check for the date and return the value accordingly

select  *, 
        Cnt = CASE 
              WHEN date BETWEEN '20171201' AND '20180331' THEN 1
              WHEN date BETWEEN '20161201' AND '20170331' THEN 2            
              WHEN date BETWEEN '20151201' AND '20160331' THEN 3
              WHEN date BETWEEN '20141201' AND '20150331' THEN 4            
              END
from    @tab

EDIT :

Or maybe this is what you want ?

this will gives you the begining of the financial year

dateadd(month, - month(date) % 12, date)

your Cnt is no of years between the financial year and today ?

select  *, 
        Cnt = datediff(year, dateadd(month, - month(date) % 12, date), getdate())
from    @tab

Upvotes: 2

Paul Maxwell
Paul Maxwell

Reputation: 35623

Using dateadd() within a case expression should enable this:

select
  datecol
, case when datecol between @DateBeg and @DateEnd then 1 
       when datecol between dateadd(year,-1,@DateBeg) and dateadd(year,-1,@DateEnd) then 2
       when datecol between dateadd(year,-2,@DateBeg) and dateadd(year,-2,@DateEnd) then 3
       when datecol between dateadd(year,-3,@DateBeg) and dateadd(year,-3,@DateEnd) then 4
  end
from @tab

also see: http://rextester.com/BTF82190

Upvotes: 0

Related Questions