partition by and case example, Sql Server

partition by and case example

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

--select @@version as 'sql server version'
create  table yourtable
    ([Tran_Order] int, [acc1] int, [TranDate] datetime, [rate] decimal(3,2))
;
    
INSERT INTO YourTable
    ([Tran_Order], [acc1], [TranDate], [rate])
VALUES
    (4, 11188368, '2007-04-27 00:00:00', 1.35),
    (6, 11188368, '2008-04-30 00:00:00', 0.95),
    (6, 12863288, '2007-04-27 00:00:00', 1.35),
    (8, 12863288, '2008-05-16 00:00:00', 0.95),
    (3, 12978348, '2007-04-27 00:00:00', 1.35),
    (4, 12978348, '2007-05-03 00:00:00', 0.8),
    (3, 14878928, '2006-12-08 00:00:00', 1.35),
    (5, 14878928, '2007-12-13 00:00:00', 0.8),
    (2, 98743553, '2007-03-30 00:00:00', 0.9),
    (3, 98743553, '2007-05-10 00:00:00', 0.75),
    (5, 98743553, '2007-12-03 00:00:00', 0.75),
    (6, 98743553, '2011-05-03 00:00:00', 0.75),
    (7, 98743553, '2013-04-03 00:00:00', 0.75),
    (8, 98743553, '2016-09-14 00:00:00', 0.75),
    (1, 98771726, '2007-04-30 00:00:00', 0.75),
    (2, 98771726, '2011-08-02 00:00:00', 0.75),
    (3, 98771726, '2013-02-18 00:00:00', 0.75),
    (4, 98771726, '2016-07-11 00:00:00', 0.75),
    (1, 98784330, '2007-04-25 00:00:00', 1.1),
    (2, 98784330, '2008-05-21 00:00:00', 1),
    (3, 98784330, '2011-07-01 00:00:00', 1),
    (4, 98784330, '2013-10-09 00:00:00', 1),
    (5, 98784330, '2014-03-18 00:00:00', 1),
    (1, 98790248, '2007-05-03 00:00:00', 1.25),
    (2, 98790248, '2007-06-21 00:00:00', 1.1),
    (3, 98790248, '2007-11-21 00:00:00', 0.8),
    (4, 98790248, '2011-08-02 00:00:00', 0.8)
;

select * from yourtable

select
      t.acc1
    , t.TranDate
    , t.rate
    , case when t.TranDate = D.tranDate then 'Base'
           when t.rate > d.rate then 'Increase'
           when t.rate < d.rate then 'Decrease'
           else 'Equal'
      end as Movement
from YourTable t
inner join (
        select acc1, TranDate, rate, row_number() over(partition by acc1 order by TranDate ASC) as rn
        from YourTable
        ) d ON t.acc1 = d.acc1 and d.rn=1
where (
        t.TranDate = d.TranDate
      or 
        t.rate < d.rate
      )
order by acc1, TranDate

View schema
Execution time: 0,02 sec, rows selected: 51, rows affected: 27, absolute service time: 0,17 sec

	Tran_Order	acc1	TranDate	rate
1	4	11188368	27.04.2007 00:00:00	1,35
2	6	11188368	30.04.2008 00:00:00	0,95
3	6	12863288	27.04.2007 00:00:00	1,35
4	8	12863288	16.05.2008 00:00:00	0,95
5	3	12978348	27.04.2007 00:00:00	1,35
6	4	12978348	03.05.2007 00:00:00	0,80
7	3	14878928	08.12.2006 00:00:00	1,35
8	5	14878928	13.12.2007 00:00:00	0,80
9	2	98743553	30.03.2007 00:00:00	0,90
10	3	98743553	10.05.2007 00:00:00	0,75
11	5	98743553	03.12.2007 00:00:00	0,75
12	6	98743553	03.05.2011 00:00:00	0,75
13	7	98743553	03.04.2013 00:00:00	0,75
14	8	98743553	14.09.2016 00:00:00	0,75
15	1	98771726	30.04.2007 00:00:00	0,75
16	2	98771726	02.08.2011 00:00:00	0,75
17	3	98771726	18.02.2013 00:00:00	0,75
18	4	98771726	11.07.2016 00:00:00	0,75
19	1	98784330	25.04.2007 00:00:00	1,10
20	2	98784330	21.05.2008 00:00:00	1,00
21	3	98784330	01.07.2011 00:00:00	1,00
22	4	98784330	09.10.2013 00:00:00	1,00
23	5	98784330	18.03.2014 00:00:00	1,00
24	1	98790248	03.05.2007 00:00:00	1,25
25	2	98790248	21.06.2007 00:00:00	1,10
26	3	98790248	21.11.2007 00:00:00	0,80
27	4	98790248	02.08.2011 00:00:00	0,80

	acc1	TranDate	rate	Movement
1	11188368	27.04.2007 00:00:00	1,35	Base
2	11188368	30.04.2008 00:00:00	0,95	Decrease
3	12863288	27.04.2007 00:00:00	1,35	Base
4	12863288	16.05.2008 00:00:00	0,95	Decrease
5	12978348	27.04.2007 00:00:00	1,35	Base
6	12978348	03.05.2007 00:00:00	0,80	Decrease
7	14878928	08.12.2006 00:00:00	1,35	Base
8	14878928	13.12.2007 00:00:00	0,80	Decrease
9	98743553	30.03.2007 00:00:00	0,90	Base
10	98743553	10.05.2007 00:00:00	0,75	Decrease
11	98743553	03.12.2007 00:00:00	0,75	Decrease
12	98743553	03.05.2011 00:00:00	0,75	Decrease
13	98743553	03.04.2013 00:00:00	0,75	Decrease
14	98743553	14.09.2016 00:00:00	0,75	Decrease
15	98771726	30.04.2007 00:00:00	0,75	Base
16	98784330	25.04.2007 00:00:00	1,10	Base
17	98784330	21.05.2008 00:00:00	1,00	Decrease
18	98784330	01.07.2011 00:00:00	1,00	Decrease
19	98784330	09.10.2013 00:00:00	1,00	Decrease
20	98784330	18.03.2014 00:00:00	1,00	Decrease
21	98790248	03.05.2007 00:00:00	1,25	Base
22	98790248	21.06.2007 00:00:00	1,10	Decrease
23	98790248	21.11.2007 00:00:00	0,80	Decrease
24	98790248	02.08.2011 00:00:00	0,80	Decrease