2

I've got a table with articles, and their stock amount changes:

TABLE stock_history:
+------------+---------------------+--------+
| article_id | amount_at           | amount |
+------------+---------------------+--------+
|       0001 | 2016-01-19 08:00:00 |    100 |
|       0001 | 2016-02-20 08:00:00 |    500 |
|       0001 | 2016-03-21 08:00:00 |      0 |
|       0002 | 2016-01-17 08:00:00 |    999 |
|       0002 | 2016-02-23 08:00:00 |    500 |
|       0002 | 2016-04-21 08:00:00 |   5500 |
|       0003 | 2016-02-19 08:00:00 |   1000 |
|       0003 | 2016-03-03 08:00:00 |    500 |
|       0003 | 2016-03-21 08:00:00 |  -1500 |
+------------+---------------------+--------+

Now I need a query which lists all articles with their amounts at a certain date+time, for example 2016-03-01:

I will need to use subqueries like here: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-row.html

But I will need to use there SQL expressions somehow:

  • amount_at < '2016-03-01 00:00:00'
  • MAX(amount_at)
  • GROUP BY article_id

The expected output should be like this:

+------------+---------------------+--------+
|       0001 | 2016-02-20 08:00:00 |    500 |
|       0002 | 2016-02-23 08:00:00 |    500 |
|       0003 | 2016-02-19 08:00:00 |   1000 |
+------------+---------------------+--------+

note I will need to join other tables, too, after, but that's another problem. :)

It's a little bit difficult...

  • 1
    You have found the parts you need, now mix them up ;) Jokes aside, have you tried some queries? What should the expected output be? – ypercubeᵀᴹ Jan 15 '16 at 14:40
  • I've added the expected rows, the date+time is not really necessary... – Artisan72 Jan 15 '16 at 14:50
  • For article 0003, what would be the expected value for the amount on 3/21? If you used MAX, it would still be 1000. Should it be the SUM of all values as of that date (so 0), or should it actually be -1500? – Jonathan Fite Jan 15 '16 at 15:19
1

I you don't care about the time in the result it's pretty easy:

select article_id, sum(amount)
from stock_history 
where amount_at <= '2016-03-01 00:00:00'
group by article_id

If you want time, you need to determine which time you should present. If you want the same time as in the where clause you can just add it since it is a constant:

select article_id, '2016-03-01 00:00:00', sum(amount)
from stock_history 
where amount_at <= '2016-03-01 00:00:00'
group by article_id

Assuming it is the last time before this time you can map all times to this time with a derived table and use that:

select a.article_id, b.amount_at, sum(a.amount)
from stock_history a
join ( select article_id, max(amount_at) as amount_at 
       from stock_history 
       where amount_at <= '2016-03-01 00:00:00'
       group by article_id
) as b
    on a.rticle_id = b.article_id
   and a.amount_at <= b.amount_at
group by a.article_id, b.amount_at;
0

This would be easily solved with window functions in other DBMS that have implemented these features, like PostgreSQL, Oracle, DB2 or with a LATERAL join inj Postgres (or the similar CROSS/OUTER APPLY in SQL Server). In MySQL, it's not trivial but can be done. My favourite way of doing this kind of queries:

select 
    d.article_id, s.amount_at, s.amount
from 
    ( select article_id
      from stock_history 
      group by article_id
    ) as d                      -- the basis of the query. If there is a table
                                -- that has artcile_id as PK, use that instead
  join
    stock_history as s          -- where we'll get the data from
  on  s.article_id = d.article_id
  and s.amount_at  = 
          ( select si.amount_at
            from stock_history as si
            where si.amount_at < '2016-03-01 00:00:00'
            order by si.amount_at desc
            limit 1
          ) ;

Assuming that (article_id, amount_at) has a UNIQUE constraint, the query will return one row per article_id (or none for those artciles that there is no row before the wanted date). If there is another unique or PRIMARY KEYyou want to use instead, replace this part:

  on  s.article_id = d.article_id
  and s.amount_at  = 
          ( select si.amount_at
          ...

with:

  on  s.PK =
          ( select si.PK
          ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.