Home » SQL & PL/SQL » SQL & PL/SQL » running total query (11 g)
running total query [message #677450] Sat, 21 September 2019 02:46 Go to next message
annu-agi
Messages: 232
Registered: July 2005
Location: Karachi
Senior Member

dear Experts ,

i need to see data in the given format and i am using query as below , How do i calculate opening as for transaction_date ??? any idea would be appreciable
SELECT DISTINCT
    inventory_item_id,
    organization_id,
    trunc(transaction_date) transaction_date,
    SUM(
        primary_quantity
    ) OVER(PARTITION BY
        inventory_item_id
        ORDER BY
            trunc(transaction_date)
    ) CLOSING_BALANCE
FROM
    mtl_material_transactions
WHERE
        inventory_item_id = 37155
    AND
        organization_id = 290
    AND
        subinventory_code IN (
            'Fresh','Hold'
        ) 


------- need format -----
icode   whid    date                     opbl    clbl  
37155	290	30-NOV-2015 00:00	0	13465
37155	290	01-DEC-2015 00:00	13465	13465
37155	290	02-DEC-2015 00:00	13465	13465
37155	290	03-DEC-2015 00:00	13465	13128
37155	290	04-DEC-2015 00:00	13128	11700
37155	290	05-DEC-2015 00:00	11700	11700
37155	290	06-DEC-2015 00:00	11700	11700
37155	290	07-DEC-2015 00:00	11700	78776
37155	290	08-DEC-2015 00:00	78776	76288
37155	290	09-DEC-2015 00:00	76288	74809
37155	290	10-DEC-2015 00:00	74809	72017



regards
Anwer
Re: running total query [message #677451 is a reply to message #677450] Sat, 21 September 2019 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also you forgot to feedback in your previous topics...

Re: running total query [message #677452 is a reply to message #677451] Sat, 21 September 2019 03:51 Go to previous messageGo to next message
annu-agi
Messages: 232
Registered: July 2005
Location: Karachi
Senior Member

thank you Michel

to avoid delays , i just directly ask question , i thought its a simple case. take my apologies ..well i will defiantly create a case and get back to you. What if i use lead or lag function with the said query ?? is it workable???
Re: running total query [message #677454 is a reply to message #677452] Sat, 21 September 2019 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 66650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know I have no idea of what are your table and data.

Re: running total query [message #677604 is a reply to message #677454] Mon, 30 September 2019 06:10 Go to previous message
Bill B
Messages: 1899
Registered: December 2004
Senior Member
Try this
  SELECT Inventory_item_id,
         Organization_id,
         TRUNC (Transaction_date)
             Transaction_date,
         SUM (Primary_quantity)
             OVER (ORDER BY TRUNC (Transaction_date)
                   ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
             Opening_balance,
         SUM (Primary_quantity)
             OVER (ORDER BY TRUNC (Transaction_date)
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
             Closing_balance
    FROM Mtl_material_transactions
   WHERE     Inventory_item_id = 37155
         AND Organization_id = 290
         AND Subinventory_code IN ('Fresh', 'Hold')
ORDER BY TRUNC (Transaction_date);
Previous Topic: Cannot drop a user
Next Topic: Co-related Subquery Throwing Error While Using rank function
Goto Forum:
  


Current Time: Mon Oct 21 20:35:56 CDT 2019