Solved

MySQL Select latest stock count records

Posted on 2016-10-27
11
89 Views
Last Modified: 2016-10-27
Hello Experts Exchange
I have a MySQL query that gives me all the stock count records from the database.

I want to select the latest records for the stock items selected.

I have attached a CSV file of example data.

This is my current query;

Select dl.area, dl.name as location, si.id, si.name as item_ref, ssi.Updated_at,ssi.count
From data_location as dl
inner join stk_stocktake_header ssh on dl.id = ssh.location_id
inner join stk_stocktake_item ssi on ssi.stocktake_header_id = ssh.id
inner join stk_item as si on si.id = ssi.stk_item_id
inner join stk_product as sp on si.stk_product_id = sp.id
inner join stk_stock_ref as ssr on sp.stk_stock_ref_id = ssr.id
Where dl.area in ('Area 1 Scotland')
and dl.name in ('Callander - 030')
and si.id in (11391,11392)
order by si.id,ssi.Updated_at desc

Open in new window


The records I want from the example data are;

Area 1 Scotland      Callander - 030      11391      PLU3941 Diary-2017 Corporate Pink      2016-10-27 09:09:20      12.00000
Area 1 Scotland      Callander - 030      11392      PLU3942 Diary-2017 Corporate Turquoise      2016-10-27 09:09:20      8.00000

How do I change my SQL to only select the latest records for each item?

Regards

SQLSearcher
General_Stock_Output.csv
0
Comment
Question by:SQLSearcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 41862001
Hi!

You can do it like this.

Select dl.area, dl.name as location, si.id, si.name as item_ref, ssi.Updated_at,ssi.count
From data_location as dl
inner join stk_stocktake_header ssh on dl.id = ssh.location_id
inner join stk_stocktake_item ssi on ssi.stocktake_header_id = ssh.id
inner join stk_item as si on si.id = ssi.stk_item_id
inner join stk_product as sp on si.stk_product_id = sp.id
inner join stk_stock_ref as ssr on sp.stk_stock_ref_id = ssr.id
Where dl.area in ('Area 1 Scotland')
and dl.name in ('Callander - 030')
and si.id in (11391,11392)
and ssi.Updated_at = (select max(ssi2.Updated_at) from stk_stocktake_item ssi2 where ssi2.stocktake_header_id = ssi.stocktake_header_id and ssi2.stk_item_id = ssi.stk_item_id)
order by si.id,ssi.Updated_at desc

Open in new window


Hope this helps.

Regards,
    Tomas Helgi
0
 

Author Comment

by:SQLSearcher
ID: 41862038
Hi Tomas
I tried your SQL but it selected the same records my SQL code selected and not the two records I wanted.

Regards

SQLSearcher
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41862075
What's the unique key in stk_stocktake_header?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 41862077
Hi!

Try this

Select dl.area, dl.name as location, si.id, si.name as item_ref, ssi.Updated_at,ssi.count
From data_location as dl
inner join stk_stocktake_header ssh on dl.id = ssh.location_id
inner join stk_stocktake_item ssi on ssi.stocktake_header_id = ssh.id
inner join stk_item as si on si.id = ssi.stk_item_id
inner join stk_product as sp on si.stk_product_id = sp.id
inner join stk_stock_ref as ssr on sp.stk_stock_ref_id = ssr.id
Where dl.area in ('Area 1 Scotland')
and dl.name in ('Callander - 030')
and si.id in (11391,11392)
and ssi.Updated_at = (select max(ssi2.Updated_at) from stk_stocktake_item ssi2 where ssi2.stocktake_header_id = ssi.stocktake_header_id )
order by si.id,ssi.Updated_at desc

Open in new window


Or this
Select dl.area, dl.name as location, si.id, si.name as item_ref, ssi.Updated_at,ssi.count
From data_location as dl
inner join stk_stocktake_header ssh on dl.id = ssh.location_id
inner join stk_stocktake_item ssi on ssi.stocktake_header_id = ssh.id
inner join stk_item as si on si.id = ssi.stk_item_id
inner join stk_product as sp on si.stk_product_id = sp.id
inner join stk_stock_ref as ssr on sp.stk_stock_ref_id = ssr.id
Where dl.area in ('Area 1 Scotland')
and dl.name in ('Callander - 030')
and si.id in (11391,11392)
and ssi.Updated_at = (select max(ssi2.Updated_at) from stk_stocktake_item ssi2 where ssi2.stk_item_id = ssi.stk_item_id)
order by si.id,ssi.Updated_at desc

Open in new window

Regards,
    Tomas Helgi
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 41862084
Edit:  Oops, the posts clashed.  Since it was already written, may as well leave it.

Use a derived table to grab the latest "Updated_at" for each "stk_item_id". Then JOIN back to the other tables on those 2 fields. Something like this:

SELECT
      dl.area
      , dl.name as location
      , si.id
      , si.name as item_ref
      , ssi.Updated_at
      , ssi.count
FROM  data_location as dl
      INNER JOIN stk_stocktake_header ssh ON dl.id = ssh.location_id
      INNER JOIN stk_stocktake_item ssi ON ssi.stocktake_header_id = ssh.id
      INNER JOIN stk_item as si ON si.id = ssi.stk_item_id
      (
           SELECT stk_item_id, MAX(Updated_at) AS Updated_at
             FROM   stk_stocktake_item
             GROUP BY stk_item_id
      ) mx
      INNER JOIN mx.stk_item_id = si.id AND mx.Updated_at = ssi.Updated_at
      INNER JOIN stk_product as sp ON si.stk_product_id = sp.id
      INNER JOIN stk_stock_ref as ssr ON sp.stk_stock_ref_id = ssr.id
WHERE       dl.area in ('Area 1 Scotland')
AND      dl.name in ('Callander - 030')
AND      si.id in (11391,11392)
ORDER BY si.id
      , ssi.Updated_at desc
0
 

Author Comment

by:SQLSearcher
ID: 41862096
Hello
The Unique key in stk_stocktake_header is id.

Tomas your SQL got better but did not resolve the issue it brought back 4 records 2 for each item.

Regards

SQLSearcher
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41862114
Try my query above.  Though it is essentially doing the same as Tomas' 2nd query.  So if mine works, his should too. At least I think so, without having run it..
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 41862141
Hi!

Try this

Select dl.area, dl.name as location, si.id, si.name as item_ref, ssi.Updated_at,ssi.count
From data_location as dl
inner join stk_stocktake_header ssh on dl.id = ssh.location_id
inner join stk_stocktake_item ssi on ssi.stocktake_header_id = ssh.id
inner join stk_item as si on si.id = ssi.stk_item_id
inner join stk_product as sp on si.stk_product_id = sp.id
inner join stk_stock_ref as ssr on sp.stk_stock_ref_id = ssr.id
Where dl.area in ('Area 1 Scotland')
and dl.name in ('Callander - 030')
and si.id in (11391,11392)
and ssi.Updated_at = (select max(ssi2.Updated_at) from stk_stocktake_item ssi2 where ssi2.stk_item_id = ssh.id)
order by si.id,ssi.Updated_at desc

Open in new window


_agx_ your derived table would search all data from the table then do filtering on max instead of filtering out just the needed id's in the stk_stocktake_item table.

Regards,
     Tomas Helgi
0
 

Author Closing Comment

by:SQLSearcher
ID: 41862200
Thank you for your help.

The SQL Script I ended up with looked like this;

SELECT
            dl.area
            , dl.name as location
            , si.ref as item_ref
            , si.name as item_name
            , ssi.count as qty
 FROM  data_location as dl
       INNER JOIN stk_stocktake_header ssh ON dl.id = ssh.location_id
       INNER JOIN stk_stocktake_item ssi ON ssi.stocktake_header_id = ssh.id
       INNER JOIN stk_item as si ON si.id = ssi.stk_item_id
       INNER JOIN (
            SELECT ssh.location_id, stk_item_id, MAX(ssi.Updated_at) AS Updated_at
              FROM   stk_stocktake_item ssi
                        Inner join stk_stocktake_header ssh ON ssi.stocktake_header_id = ssh.id
                        INNER JOIN data_location as dl ON dl.id = ssh.location_id
                        where stk_item_id in (11391,11392)
                        and dl.area in ('Area 1 Scotland','Area 7 M25 & South')
                        AND dl.name in ('Callander - 030','Harrow - 063')
              GROUP BY ssh.location_id,stk_item_id
       ) mx
       On mx.stk_item_id = ssi.stk_item_id
            AND mx.Updated_at = ssi.Updated_at
            AND mx.location_id = ssh.location_id
       INNER JOIN stk_product as sp ON si.stk_product_id = sp.id
       INNER JOIN stk_stock_ref as ssr ON sp.stk_stock_ref_id = ssr.id
 WHERE    dl.area in ('Area 1 Scotland','Area 7 M25 & South')
 AND dl.name in ('Callander - 030','Harrow - 063')
 AND      si.id in (11391,11392)
 ORDER BY si.id, ssi.Updated_at desc

Regards

SQLSearcher
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41862300
@Tomas Helgi - True.  The net effect should be the same, but you'd have to look at the execution plan to see which is more efficient. It's quite possible the subquery approach would be more efficient than mine.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41862329
@SQLSearcher - Since you're already filtering in the derived query, you shouldn't need to do it again in the outer WHERE clause.  It won't hurt anything, but it's redundant.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question