insert into balance values ( 1, '2018-10-01', 111 );
insert into balance values ( 1, '2018-10-02', 115 );
insert into balance values ( 12, '2017-10-01', 1134 );
insert into balance values ( 12, '2017-09-01', 159 );
insert into balance values ( 1, '2018-09-02', 130 );
insert into balance values ( 5, '2018-09-02', 150 );
6 rows affected
hidden batch(es)
select * from balance;
id
balance_date
balance
1
2018-10-01 00:00:00.000
111
1
2018-10-02 00:00:00.000
115
12
2017-10-01 00:00:00.000
1134
12
2017-09-01 00:00:00.000
159
1
2018-09-02 00:00:00.000
130
5
2018-09-02 00:00:00.000
150
…
hidden batch(es)
WITH
bal AS (
SELECT
b.*,
row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
FROM balance b
),
last_bal AS (
SELECT id, balance_date, balance FROM bal WHERE r = 1
),
prev_bal AS (
SELECT id, balance_date, balance FROM bal WHERE r = 2
)
SELECT
lb.id,
lb.balance_date AS last_balance_date, lb.balance AS last_balance,
pb.balance_date AS prev_balance_date, pb.balance AS prev_balance
FROM last_bal lb
LEFT JOIN prev_bal pb ON pb.id = lb.id
;
id
last_balance_date
last_balance
prev_balance_date
prev_balance
1
2018-10-02 00:00:00.000
115
2018-10-01 00:00:00.000
111
5
2018-09-02 00:00:00.000
150
12
2017-10-01 00:00:00.000
1134
2017-09-01 00:00:00.000
159
…
hidden batch(es)
-- ПОСМОТРИТЕ как работает функция ROW_NUMBER() !!!
SELECT
b.*,
row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
FROM balance b
