CODEWITHSUNDEEP
sqlhadoophivehiveql
Dora
Dora

Reputation: 25

Calculate the difference between the max value of a row and other rows based on the date

I have a table as follows:

date          table_name   count_table
2018-07-19    A            50  
2018-07-19    B            40
2018-07-18    A            25
2018-07-18    B            30
2018-07-17    A            10
2018-07-17    B            5

I would like to calculate the difference of the count between every date. The result table would be as:

date1        date2        table_name    diff
2018-07-17   2018-07-18   A             15
2018-07-18   2018-07-19   A             25
2018-07-17   2018-07-18   B             25
2018-07-18   2018-07-19   B             10 `

Thank you for your help

Upvotes: 0

Views: 61

Answers (3)

JERRY
JERRY

Reputation: 1173

If Multiple Dates and entities are there then you can use below code. i just written in MariaDb. You can use in any database by changing Temp table syntax.

 CREATE TABLE test(dates date, table_name varchar(10), count_table INT);
 INSERT INTO test(dates, table_name, count_table) 
 VALUES('2018-07-19','A', 50),
 ('2018-07-19', 'B', 40),
 ('2018-07-18', 'A', 25),
 ('2018-07-18', 'B', 30),
 ('2018-07-17', 'A', 10),
 ('2018-07-17', 'B', 5);

 CREATE TEMPORARY TABLE test2(dates date, table_name varchar(10), count_table INT, Ranks INT);

INSERT INTO test2
select *, ROW_NUMBER() OVER(partition BY table_name order by dates) as Ranks
from test;

select t1.dates as date1, t2.dates as date2, t1.table_name, t2.count_table - t1.count_table
from test2 t1
INNER JOIN test2 t2 ON t1.table_name = t2.table_name and t1.ranks = t2.ranks - 1
ORDER BY table_name, t1.dates

Upvotes: 1

kiran gadhe
kiran gadhe

Reputation: 743

SELECT IT1.date1, ITM.date2, IT1.table_name, IT2.count_table - ITM.count_table AS diff
FROM input_table IT1
cross JOIN (
    SELECT *
    FROM input_table IT2
    WHERE IT1.DATE < it2.DATE and IT1.table_name = IT2.table_name)
) ITM

Upvotes: 0

Sjoerd
Sjoerd

Reputation: 191

Use the same table twice in 1 query and give it an alias. 

   SELECT a.date AS date1, b.date AS date2, a.table_name AS table_name, b.count_table - a.count_table AS diff
   FROM yourTable a, yourTable b
   WHERE a.date1 < b.date2
   GROUP BY date1, date2, table_name

Upvotes: 0

Related Questions