Click here to Skip to main content
15,894,017 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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:

SQL
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) --it is the foreigh key from another table but it does not matter.
)

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
Posted
Updated 4-Aug-19 6:46am
v2

1 solution

Start by doing it in simple stages:
1) Group together the id_a values. You can't do this easily with GROUP BY, but ... you can with PARTITION:
SQL
SELECT id_a, 
       id_b, 
       [DATE], 
       Amount,
       RANK() OVER (PARTITION BY id_a ORDER BY [date] DESC, id_b desc) as ranking
From b WHERE Amount != 0

Now you can SELECT from that result the rows you need by using a WHERE clause on the ranking column.

That's half your homework done - I'll leave the rest to you!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900