CODEWITHSUNDEEP
sql-servert-sqlwindow-functionspartition-by
beginsql
beginsql

Reputation: 135

How to partition data by date intervals?

I'm curious is this possible to partition my data by date range using window functions? I have table TerritoryRevenue:

TerritoryID name                                               Revenue               orderdate               RankByRevenue
----------- -------------------------------------------------- --------------------- ----------------------- --------------------
4           Southwest                                          1027923,9413          2006-09-01 00:00:00.000 1
4           Southwest                                          892043,959            2006-08-01 00:00:00.000 2
4           Southwest                                          796734,8925           2007-05-01 00:00:00.000 3
4           Southwest                                          782962,2628           2006-11-01 00:00:00.000 4
4           Southwest                                          775273,9148           2007-09-01 00:00:00.000 5
4           Southwest                                          760638,3393           2007-07-01 00:00:00.000 6
1           Northwest                                          733397,8075           2007-08-01 00:00:00.000 7
6           Canada                                             713068,2334           2007-12-01 00:00:00.000 8
7           France                                             707659,8229           2007-08-01 00:00:00.000 9
6           Canada                                             677073,7101           2007-09-01 00:00:00.000 10
4           Southwest                                          676897,9351           2006-12-01 00:00:00.000 11
4           Southwest                                          672075,0706           2007-08-01 00:00:00.000 12
6           Canada                                             660610,2897           2006-08-01 00:00:00.000 13
4           Southwest                                          644851,3869           2007-02-01 00:00:00.000 14
7           France                                             640657,4244           2007-11-01 00:00:00.000 15
6           Canada                                             638115,2008           2006-07-01 00:00:00.000 16
4           Southwest                                          630143,7952           2007-06-01 00:00:00.000 17
4           Southwest                                          623321,4611           2007-10-01 00:00:00.000 18
7           France                                             616132,3947           2008-05-01 00:00:00.000 19
1           Northwest                                          606621,8131           2007-09-01 00:00:00.000 20
4           Southwest                                          598632,0709           2008-06-01 00:00:00.000 21
1           Northwest                                          597304,4287           2007-11-01 00:00:00.000 22
4           Southwest                                          591577,4069           2008-04-01 00:00:00.000 23
4           Southwest                                          589925,8656           2008-05-01 00:00:00.000 24
6           Canada                                             589015,6507           2008-06-01 00:00:00.000 25
4           Southwest                                          588013,9785           2007-12-01 00:00:00.000 26
4           Southwest                                          578002,5452           2007-11-01 00:00:00.000 27
6           Canada                                             572102,5723           2007-08-01 00:00:00.000 28
1           Northwest                                          567438,8724           2008-05-01 00:00:00.000 29
6           Canada                                             564052,7259           2006-09-01 00:00:00.000 30
1           Northwest                                          555031,8462           2006-08-01 00:00:00.000 31
1           Northwest                                          524780,7777           2008-06-01 00:00:00.000 32
1           Northwest                                          521845,3013           2007-12-01 00:00:00.000 33
4           Southwest                                          517015,445            2008-02-01 00:00:00.000 34
1           Northwest                                          516421,3597           2006-11-01 00:00:00.000 35
6           Canada                                             510381,3556           2006-11-01 00:00:00.000 36
6           Canada                                             502797,2246           2007-07-01 00:00:00.000 37
10          United Kingdom                                     496271,7803           2007-09-01 00:00:00.000 38
4           Southwest                                          496217,5327           2006-07-01 00:00:00.000 39
6           Canada                                             489254,911            2006-12-01 00:00:00.000 40
1           Northwest                                          483485,0034           2007-05-01 00:00:00.000 41
7           France                                             477976,7538           2008-02-01 00:00:00.000 42
4           Southwest                                          474840,9012           2008-01-01 00:00:00.000 43
6           Canada                                             469752,6114           2007-05-01 00:00:00.000 44
6           Canada                                             467334,1973           2008-03-01 00:00:00.000 45
4           Southwest                                          466123,0048           2008-03-01 00:00:00.000 46
1           Northwest                                          456366,7061           2008-02-01 00:00:00.000 47
6           Canada                                             443495,2247           2007-06-01 00:00:00.000 48
1           Northwest                                          441917,9528           2008-03-01 00:00:00.000 49
4           Southwest                                          438879,4098           2007-03-01 00:00:00.000 50
(...)

As you can see I've ranked my output by Revenue but there is no partition by clause. I want to get rank by Revenue in two intervals, let's say from July 2006 to June 2007 and July 2007 to June 2008.

Upvotes: 0

Views: 58

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37500

Try below query, you cane use case when statement to generate grouping column:

select TerritoryID,
       name,
       Revenue,
       orderdate,
       row_number() over (partition by [06-08] + [06-07] order by Revenue) RankByRevenue
from (
    select TerritoryID,
           name,
           Revenue,
           orderdate,
           case when orderdate between '2006-07-01' and '2007-01-01' then 1 else 0 end [06-07],
           case when orderdate between '2007-07-01' and '2008-01-01' then -1 else 0 end [07-08]
    from MyTable
) a

Upvotes: 1

Related Questions