comparing a date value in one table against multiple values in another to categorise by age



  • I have created a form which allows users to enter multiple, unlimited month number values and label them to use against inventory to categorise the age of each item.

    Table 1 - AgeConfig - Manually enterted by users

    id customer_id start_month label
    1 171 0 Current
    2 171 3 3-6 Months
    3 171 6 6-12 Months
    4 171 12 12+ Months

    Table 2 - Inventory

    id customer_id item_no entry_date qty cost
    1 171 CH72394 2022-01-28 3 430.10
    1 171 CH72395 2022-02-28 4 30.10
    1 171 CH72396 2022-03-28 1 130.10
    1 171 CH72397 2022-01-22 5 320.50
    1 171 CH72398 2022-02-22 1 22.30
    1 171 CH72399 2022-01-22 1 92.30
    1 171 CH72400 2022-01-24 1 392.80

    I am trying to present this in SQL using CASE but I dont know how to compare it against a variable number of rows and make sure I start at the highest and work to the lowest.

    I am trying to use CASE but am stumped

    SELECT  customer_id,
        item_no,
        entry_date,
        CASE
            WHEN (TIMESTAMPDIFF(MONTH, entry_date, NOW()) > MAX(start_month)) THEN `label`
            WHEN (TIMESTAMPDIFF(MONTH, entry_date, NOW()) > 2NDMAX(start_month)) THEN `label`
            WHEN (TIMESTAMPDIFF(MONTH, entry_date, NOW()) > 3RDMAX(start_month)) THEN `label`
            ...
        END,
        qty,
        cost
    FROM Inventory
    INNER JOIN AgeConfig USING (customer_id)
    WHERE customer_id = 171
    

    I dont know how to incrementally test against highest down to lowest and then use the corresponding label so that the result is:

    id customer_id item_no entry_date age qty cost
    1 171 CH72394 2022-01-28 3-6 Months 3 430.10
    1 171 CH72395 2022-02-28 Current 4 30.10
    1 171 CH72396 2021-03-28 Current 1 130.10
    1 171 CH72397 2022-01-22 3-6 Months 5 320.50
    1 171 CH72398 2021-08-22 6-12 Months 1 22.30
    1 171 CH72399 2021-01-22 12+ Months 4 92.30
    1 171 CH72400 2022-01-24 3-6 Months 1 392.80


  • I ended up using php in between to get a fast resolution - probably not the most efficient but it works. Still interested to know if there is an SQL only solution

    SELECT period_start_mth, period_label FROM AgeConfig WHERE customer_id = 171 ORDER BY period_start_mth DESC

    Then built the SQL with the resulting array

    $entry_date_query = "";
    

    if ( !empty($agePeriods) )
    {
    foreach ( $agePeriods as $period )
    {
    $entry_date_query .= "WHEN (TIMESTAMPDIFF(MONTH, entry_date, NOW()) >= {$period["period_start_mth"]}) THEN '{$period["period_label"]}'".PHP_EOL;
    }

    $entry_date_query = "CASE" . PHP_EOL . $entry_date_query . "ELSE 'Other'" . PHP_EOL . "END AS entry_date_age,";
    

    }
    else
    {
    $entry_date_query = "'Default Catch All' as entry_date_age,";
    }

    $query = "SELECT customer_id,
    item_no,
    entry_date,
    $entry_date_query
    qty,
    cost
    FROM Inventory
    WHERE customer_id = 171";


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2