PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Mysql Select last price based on document type, group by item code-VBForums
Results 1 to 3 of 3

Thread: Mysql Select last price based on document type, group by item code

  1. #1

    Thread Starter
    Member
    Join Date
    Jan 2017
    Posts
    40

    Mysql Select last price based on document type, group by item code

    Name:  datatable_example.jpg
Views: 111
Size:  47.8 KB

    The first table shows the raw data, the second table is the record set that i am trying to achieve.

    The record set is build using the following logic:

    to select the item code and the last unit price (according to date) where the warehouse_id=1 and the
    document type is GRV

    If this returns a null result (i.e. there is no existing grv for that item code in that warehouse) then
    return the last unit price where the warehouse_id=1 and the document type is 'IWR'

    If this returns a null result (i.e. there is no existing grv or iwr for that item code in that warehouse) then
    return the last unit price where the warehouse_id=1 (irrespective of the document type)

    Any help would be greatly appreciated

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,534

    Re: Mysql Select last price based on document type, group by item code

    for your sample data this works in Access. you'll have to translate it to MySQL

    sql Code:
    1. SELECT DISTINCT  
    2. p1.itemcode,
    3. iif(isnull(p2.unitprice),iif(isnull(p3.unitprice),p4.unitprice,p3.unitprice),p2.unitprice) AS price
    4.  
    5. FROM ((tabelle1 AS p1
    6.  
    7. LEFT JOIN  
    8. (SELECT t1.itemcode, t2.unitprice
    9. FROM
    10. (SELECT  
    11. itemcode, MAX(mydate) AS datum
    12. FROM  tabelle1
    13. WHERE TYPE="grv"
    14. GROUP BY itemcode  
    15. ORDER BY itemcode ASC, MAX(mydate) DESC) AS t1
    16. LEFT JOIN
    17. tabelle1 AS t2 ON t1.itemcode=t2.itemcode AND t1.datum=t2.mydate) AS p2 ON p2.itemcode=p1.itemcode)
    18.  
    19. LEFT JOIN
    20. (SELECT
    21. t1.itemcode, t2.unitprice
    22. FROM
    23. (SELECT  
    24. itemcode,
    25. MAX(mydate) AS datum
    26. FROM tabelle1
    27. WHERE TYPE="iwr"
    28. GROUP BY itemcode  
    29. ORDER BY itemcode ASC, MAX(mydate) DESC) AS t1
    30. LEFT JOIN
    31. tabelle1 AS t2 ON t1.itemcode=t2.itemcode AND t1.datum=t2.mydate) AS p3 ON p1.itemcode=p3.itemcode)
    32.  
    33. LEFT JOIN
    34. (SELECT t1.itemcode, t2.unitprice
    35. FROM
    36. (SELECT  
    37. itemcode,
    38. MAX(mydate) AS datum
    39. FROM tabelle1
    40. GROUP BY itemcode  
    41. ORDER BY itemcode ASC, MAX(mydate) DESC) AS t1
    42. LEFT JOIN
    43. tabelle1 AS t2 ON t1.itemcode=t2.itemcode AND t1.datum=t2.mydate) AS p4 ON p4.itemcode=p1.itemcode
    44. WHERE
    45. p1.warehouse_id=1
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3

    Thread Starter
    Member
    Join Date
    Jan 2017
    Posts
    40

    Re: Mysql Select last price based on document type, group by item code

    Thanks, i will check it out when i am back in my office, thanks for your time and effort

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width