Postgres 9.1+ holds purchase invoices in two tables:
Invoice headers:
create table dok (
dokumnr serial primary key,
invdate date not null
);
Invoice rows:
create table rid (
id serial primary key,
dokumnr int not null references dok,
product char(20),
quantity numeric(12,5) not null,
price numeric(12,2)
);
How to find changed prices in last two purchase invoices ?
Result table should contain 3 columns:
- product
- prices and quantities in last (by date) invoice
- prices and quantities in invoice immediately before (by date and dokumnr) last invoice
Result should contian only products where any prices for same product in last two invoices are different. How to create query which returns this from invoice headers and rows?
Postgres 9.1 and later versions are used.
Single invoice can contain same product multiple times with different prices.
Update
Tried using script below. It does not return any rows.
It should return prices with quantities, like
5 pcs with 2;6 pcs with 1 in last price column and 4 pcs with 2 in previous price column. If at least one price is different, columns should contain all prices and quantities separated by ;
It there are different prices in same day invoices, they should also returned. Probably ordering should include dokumnr field in additon to invdate.
create temp table dok (
dokumnr serial primary key,
invdate date not null
) on commit drop ;
create temp table rid (
id serial primary key,
dokumnr int not null references dok,
product char(20),
quantity numeric(12,5) not null,
price numeric(12,2)
) on commit drop ;
insert into dok values (1,'2016-10-02'), (2,'2016-10-03');
insert into rid values
(1,1,'p',4, 2),
(2,2,'p',5, 2),
(3,2,'p',6, 1)
;
with top2prices as (
select product
,max(case when rnk=1 then price end) latest_price
,max(case when rnk=2 then price end) latest_2_price
from (
select r.*,d.*
,dense_rank() over(partition by r.product order by d.invdate desc) rnk
from rid r
join dok d on d.dokumnr = r.dokumnr
) t
group by product
)
select * from top2prices
where latest_price <> latest_2_price
