CODEWITHSUNDEEP
sqlsql-serverjoin
qwerty
qwerty

Reputation: 897

to join the same table to match records from last year

I want to create a join that joins records of last year to the same period of the current year. All the data is in the same table.

Input Table: 

A  | B   | C  | D
a   2017   1    10
a   2017   2    20
a   2017   3     5
a   2016   1    100
a   2016   2    50
a   2016   3    1

Output Table: 

A  | B   | C  | D   | E
a   2017   1    10    100
a   2017   2    20    50
a   2017   3     5    1
a   2016   1    100   NULL
a   2016   2    50    NULL
a   2016   3    1     NULL

Upvotes: 0

Views: 400

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

There are several ways of doing this. One is a left join:

select t.*, tprev.d as e
from t left join
     t tprev
     on t.a = tprev.a and t.c = tprev.c and tprev.b = t.b - 1;

Another way is to use window functions:

select t.*, lag(t.d) over (partition by t.a, t.c order by b) as e
from t;

These work on the same on your data. But they are subtly different. The first looks specifically at the data one year before. The second looks at the most recent year before where there is data.

Upvotes: 2

Related Questions