skip to Main Content

I have a table having below schema

Item:Varchar
Date:Date
Quantity:Float
trasactionid:int

Sample Data:

Item Date Quantity transactionid
Part1 01-01-2023 10 3
Part1 01-01-2023 15 5
Part1 01-01-2023 17 2
Part1 01-01-2023 13 6
Part1 02-01-2023 13 7
Part1 02-01-2023 1 8
Part1 02-01-2023 22 10
Part1 02-01-2023 5 12

I need to sort the data by transaction id and then find the unique part and date and Qty from first row of a group as Opening Balance and from last row as closing balance.

Required Output

Item Date transactionid OpeningBal transactionid ClosingBal
Part1 01-01-2023 2 17 6 13
Part1 02-01-2023 7 13 12 5

I tried to use window funtion first_value and last value but unable to achieve the result

5

Answers


  1. SELECT item, `date`, 
           MAX(CASE WHEN first_last.opening = test.trasactionid THEN test.trasactionid END) OpeningId,
           MAX(CASE WHEN first_last.opening = test.trasactionid THEN quantity END) OpeningBal, 
           MAX(CASE WHEN first_last.closing = test.trasactionid THEN test.trasactionid END) ColsingId,
           MAX(CASE WHEN first_last.closing = test.trasactionid THEN quantity END) ClosingBal
    FROM test
    JOIN (
      SELECT item, `date`, MIN(trasactionid) opening, MAX(trasactionid) closing
      FROM test
      GROUP BY 1,2
    ) first_last USING (item, `date`)
    GROUP BY 1,2
    

    https://dbfiddle.uk/QQlpAnJt

    Login or Signup to reply.
  2. I am not sure about mySql syntax but many SQL engines supports WITH syntax and I think it will be very easy for you to translate. This is a way to resolve your problem:

    WITH min_max AS (
      SELECT item, date, min(tran_id) AS min_id, max(tran_id) AS max_id
        FROM my_table
       GROUP BY item, date
    )
    SELECT item, date, min_id, my_table1.quantity, max_id, my_table2.quantity
      FROM min_max
      JOIN my_table AS my_table1
        ON min_max.min_id = my_table1.tran_id
      JOIN my_table AS my_table2
        ON min_max.max_id = my_table2.tran_id
    

    If WITH syntax is not supported you can use FROM (SELECT ...) syntax

    Hope it helped

    Login or Signup to reply.
  3. Starting with version 5.5, this is a working solution for any version of mysql:

    select t.Item, t.Date, t.opening_transaction, op.Quantity as OpeningBal, t.closing_transaction, oc.Quantity as ClosingBal 
    from (
      select Item, Date, min(transactionid) as opening_transaction,
                       max(transactionid) as closing_transaction
      from mytable
      group by item, Date
    ) t
    inner join mytable op on op.transactionid = t.opening_transaction
    inner join mytable oc on oc.transactionid = t.closing_transaction
    

    Demo here

    Login or Signup to reply.
  4. Using first_value and last value window functions, try the following:

    select Item, Date_, transactionid_1, OpeningBal, transactionid_2, ClosingBal
    from
    (
      select Item, Date_, 
       min(transactionid) over (partition by Item, Date_) transactionid_1,
       first_value(Quantity) over (partition by Item, Date_ order by transactionid) OpeningBal,
       max(transactionid) over (partition by Item, Date_) transactionid_2,
       last_value(Quantity) over (partition by Item, Date_ order by transactionid range between unbounded preceding and unbounded following) ClosingBal,
      transactionid
     from table_name
    ) t
    where transactionid = transactionid_1
    order by Item, Date_
    

    demo

    Login or Signup to reply.
  5. You can use window functions like so:

    select item, date, 
        transation_id opening_transaction_id, balance opening_quantity,
        closing_transaction_id, closing_quantity
    from (
        select t.*, 
            max(transation_id)    over(partition by item, date) closing_transaction_id,
            first_value(quantity) over(partition by item, date order by transaction_id desc) closing_quantity,
            row_number()          over(partition by item, date order by transaction_id) rn
        from mytable t
    ) t
    where rn = 1
    

    The subquery computes the date and the quantity of the last daily transaction , using max() and first_value() respectively. It also identifies the row that correspond to the first transaction, using row_number(). Then, the outer query just filters on the top record per group, which already contains the information we need.

    The upside of this method is that it avoids aggregation and self-joins (which are less efficient in most cases) – and it has as few window function calls as possible.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search