How to SQL query actual Pricelist grouped by several columns

user_10722623 0
Viewed 35

I have following table in postgres (product and variant are foreign key ids to another tables but I used product/variant names to make it more readable):

id productId variantId validFrom currencyCode price
1 iphone X 2021-01-01 EUR 19
2 iphone X 2021-02-01 EUR 29
3 iphone X 2021-03-01 EUR 39
4 iphone 12 2021-01-01 EUR 49
5 macbook air 2021-01-01 EUR 59
6 macbook air 2021-02-01 EUR 69
7 macbook pro 2021-02-01 EUR 79
8 macbook pro 2021-01-01 USD 89

Now when I need pricelist valid for a date 2021-01-20 the result should be:

product variant validFrom currencyCode price
iphone X 2021-01-01 EUR 19
iphone 12 2021-01-01 EUR 49
macbook air 2021-01-01 EUR 59
macbook pro 2021-01-01 USD 89

but when I need pricelist valid for a date 2021-02-08 the result should be:

product variant validFrom currencyCode price
iphone X 2021-02-01 EUR 29
iphone 12 2021-01-01 EUR 49
macbook air 2021-02-01 EUR 69
macbook pro 2021-02-01 EUR 79
macbook pro 2021-01-01 USD 89

Any suggestion how to write the query?

Many thanks

postgresql sql
2 Answers

here is one way :

select * from table t1
where id in ( 
  select id from table t2 
  where t1.productId = t2.productId 
  and t1.careintid = t2.varientid
  and t2.validfrom<= '<date param>'
  order by validfrom desc limit 1
)

or using window function:

select * from(
   select *, row_number() over (partition by productid, varientid order by case when validfrom <= '<dateparam>' then 1 else 0 end desc, validfrom desc) rn
) t where rn = 1
user_13674540 

WITH cte AS
  (SELECT productid AS Product,
          variantid AS variant,
          validfrom,
          currencycode,
          price,
          Row_number() OVER(PARTITION BY productid, variantid
                            ORDER BY To_date(validfrom, 'YYYY-MM-DD') DESC) AS RNUM
   FROM tbl
   WHERE To_date(validfrom, 'YYYY-MM-DD') <= '2021-02-08')
SELECT product,
       variant,
       validfrom,
       currencycode,
       price
FROM cte
WHERE rnum = 1;

Here tbl is the tableName

user_90872500
Related