Click here to Skip to main content
14,267,453 members
Rate this:
Please Sign up or sign in to 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:

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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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:
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!
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100