Hi guys,
I stacked with one interesting issue.
I can't think of a way to find the difference between the rows.
Task:
1)For each 'id_a' find the last 2 rows where 'amount' <> 0
2)Find the difference beetwen 'amount' of this rows
For example resul should be like that:
name | amount | id_a | difference
Konchita | 10 | 1 | 0
Bla | 10 | 1 | 0
Evlampiy | 1 | 4 | -9
Zyzia | 10 | 4 | -9
... | ... | ... | ...
I have tried to did this through windows function but more confused
Example of table:
create table b(
id_b int not null primary key,
name nvarchar (10) not null,
[date] date not null,
[amount] numeric null,
id_a int not null foreign key references a(id_a)
)
insert into b(id_b,name,[date],[amount],id_a) values
(1,'Akakiy','2019-03-03','2',1),
(2,'Traktoe','2019-03-03','60',2),
(3,'Himalay','2018-02-02','50',3),
(4,'Evlampiy','2018-02-02','1',4),
(5,'Zyzia','2018-02-02','10',4),
(6,'Borzyn','2018-02-02','100',2),
(7,'Konchita','2018-02-03','10',1),
(8,'Bycia','2018-02-03','0',1),
(9,'Masik','2018-02-03','0',4),
(10,'Bla','2018-02-03','10',1);
What I have tried:
I have tried to did this through windows function but more confused