Get first row per product after a certain date

Question

I have some table that I want to get certain data information:

Product_code, qty, stock_before, stock_after, date

--------------------------------------------------------------
|product_code | qty | stock_before | stock_after |   date     |
-------------------------------------------------------------- 
| xpto1       |  0  |            1 |           1 | 2015-01-01 |
| xpto1       |  1  |            1 |           2 | 2015-02-01 |
| xpto1       |  2  |           -1 |           1 | 2015-03-01 |
| xpto2       |  0  |            1 |           1 | 2015-01-01 |
| xpto2       |  1  |            1 |           2 | 2015-02-01 |
| xpto2       |  2  |           -1 |           1 | 2015-03-01 |

I want to know to stock_before, for each product after 2015-01-01

So the result I want is

| xpto1 | 1 | 1 | 2 | 2015-02-01 |
| xpto2 | 1 | 1 | 2 | 2015-02-01 |

I am getting this:

| xpto1 | 1 |  1 | 2 | 2015-02-01 |
| xpto1 | 2 | -1 | 1 | 2015-03-01 |
| xpto2 | 1 |  1 | 2 | 2015-02-01 |
| xpto2 | 2 | -1 | 1 | 2015-03-01 |

Lets say that I only want the first record found for each product.

can you post what query you used to get the results shown under "I am getting this"? — p.marino, Jun 02 '16 at 08:58
also, your problem description is a bit confusing. You said "I want to know to stock_before, for each product after 2015-01-01" but then as expected result you want only records for 2015-02-01. Technically, 2015-03-01 is _after_ 2015-01-01 so why do you want records from 02-01 instead? — p.marino, Jun 02 '16 at 09:01
Possible duplicate of [How to get all the fields of a row using the SQL MAX function?](http://stackoverflow.com/questions/2893903/how-to-get-all-the-fields-of-a-row-using-the-sql-max-function) — fancyPants, Jun 02 '16 at 09:25
Hi. I Am using FB 2.5. The query is select distinct sm.id, sm.codigo as product_reference, tm.nome as warehouse, sm.artigo_id as product_code, sm.stock_anterior as initial_stock from stock_movimentos sm left join tab_armazens tm on (tm.id=sm.armazem_id) left join artigos a on (a.codigo=sm.artigo_id) where sm.data between '2015-12-01' and '2016-05-27' and a.sub_familia_id in ('16.01','16.02') and sm.anulado=0 and sm.stk_ok=1 and sm.is_referencia=1 and sm.codigo is not null and sm.ano>=(extract (year from current_date)-2) group by 2,3,45 order by tm.nome, sm.codigo — user2012152, Jun 02 '16 at 13:13
Let me try to explain: The result I want is the stock_before after a certain date, since we have several record after that date, I only need the first one, it's our initial stock. — user2012152, Jun 02 '16 at 13:17
Then the linked duplicate is likely what you are looking for (except then use `MIN` instead of `MAX` and add a condition for the cutoff date). — Mark Rotteveel, Jun 02 '16 at 14:23

score 0 · Answer 1 · answered Jan 03 '17 at 21:14

Select Product_code, qty, stock_before, stock_after, date
From table a
Where a.date > '01.01.2015' and 
a.date = (Select min(b.date) from
 table b where b.product_code = 
a.product_code and b.date = a.date)

This will work if product_code,date is unique combination becase subselect must return only one record.

score 0 · Answer 2 · answered Jan 04 '17 at 10:50

Your table structure is inefficient and should be redesigned.

Usually stock balance is stored in two tables like ballance (id, date) and ballance_product (id, ballance_id, product_id, quantity).

First is retrieved id from ballance timeline for some date, and later from ballance_product you retrieve all available products and quantities at certain ballance_id.

ballance/ballance_product is calculated at closing period from last ballance* values and documents created in the period.

A query that will return product stock at some date will be:

with
  last_move as
  (
    select m.product_code, max(m.adate) as adate
    from product_stock m
    where m.adate <= :to_date
    group by 1
  )
select s.id, s.product_code, s.qty, s.stock_before, s.stock_after, s.adate
from last_move m
join product_stock s on (s.product_code = m.product_code and s.adate = m.adate)

Your assumptions "after some date" is not logical. After some date is an indefinite long period. Stocks are retrieved at some date.

Get first row per product after a certain date

Product_code, qty, stock_before, stock_after, date

2 Answers2