Home » SQL & PL/SQL » SQL & PL/SQL » Unable to get running total conditionally (Oracle Database 10g Rel.2)
| Unable to get running total conditionally [message #611414] |
Wed, 02 April 2014 05:11  |
mazam
Messages: 47
Registered: October 2009
Location: Multan
|
Member |
 
|
|
Dear Gurus
I am using Oracle Database 10g Rel.2. I've an inventory table like this
Create Table INV (Transaction_No Number,
Transaction_Date Date,
Transaction_Type Varchar2(50),
Department Varchar2(50),
Item Varchar2(50),
Transaction_QTY Number(5));
Its data is like this:
Transaction_No Transaction_Date Transaction_Type Department Item Transaction_QTY
1 31-Dec-2013 Receipt Production Bolt 100
2 01-Jan-2014 Receipt Production Bolt 10
3 01-Jan-2014 Issue Production Bolt 40
4 02-Jan-2014 Issue Production Bolt 15
5 03-Jan-2014 Receipt Production Bolt 5
Now I need output like this on 01-Jan-2014:
Transaction_No Transaction_Date Transaction_Type Department Item Opening Transaction_QTY Running Stock
2 01-Jan-2014 Receipt Production Bolt 100 10 110
3 01-Jan-2014 Issue Production Bolt 100 40 70
4 02-Jan-2014 Issue Production Bold 100 15 55
5 03-Jan-2014 Receipt Production Bolt 100 5 60
I've a function which calculates Opening QTY. Opening QTY = Sum(Receipt) - Sum(Issue) before 01-Jan-2014.
So my query doesn't need calculation for Opening. It just has to add and subtract Receipt & Issue respectively from 01-Jan-2014.
I've used:
Decode(Transaction_Type, 'Receipt',
My_Function(...) Opening_Value +
Sum(Transaction_QTY) Over(Partition By Item, Transaction_Type
Rows Between Unbounded Preceding
And Current Row),
'Issue',
My_Function(...) Opening_Value -
Sum(Transaction_QTY) Over(Partition By Item, Transaction_Type
Rows Between Unbounded Preceding
And Current Row) Running_Stock
But this is not giving my required output as described above.
So can u help me to solve this query?
|
|
|
|
|
|
|
|
| Re: Unable to get running total conditionally [message #611481 is a reply to message #611480] |
Thu, 03 April 2014 00:20  |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:Could u advise me please how align this data?
Insert spaces between them? Of course align headings and values, reduce the number of characters in column headings to make the line fit at most 100 characters
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.
[Updated on: Thu, 03 April 2014 00:23] Report message to a moderator |
|
|
|
| Re: Unable to get running total conditionally [message #611489 is a reply to message #611481] |
Thu, 03 April 2014 01:16 |
mazam
Messages: 47
Registered: October 2009
Location: Multan
|
Member |
|
|
Dear Michael...Thanx for yours advice. Does this sample data fulfills yours requirement?
With Data As (Select 1 Transaction_No, To_Date('31-Dec-2013','dd-mon-yyyy') Transaction_Date, 'Receipt' Transaction_Type, 100 Transaction_Qty From Dual
Union All
Select 2, To_Date('01-Jan-2014','dd-mon-yyyy'), 'Receipt', 10 From Dual
Union All
Select 3, To_Date('01-Jan-2014','dd-mon-yyyy'),'Issue',40 From Dual
Union All
Select 4, To_Date('02-Jan-2014','dd-mon-yyyy'),'Issue',15 From Dual
Union All
Select 5, To_Date('03-Jan-2014','dd-mon-yyyy'),'Receipt',5 From Dual)
Select Transaction_No, Transaction_Date, Transaction_Type, Transaction_Qty,
Sum(Decode(Transaction_Type,
'Receipt',1,
-1)
* Transaction_Qty) Over (Order By Transaction_No) As Running_Stock
From Data
Required results should be like this:
Trn_No Trn_Date Trn_Typ Dept Item Opn_Qty Trn_QTY Run_Stock Run_Rect Run_Issue
2 01-Jan-2014 Receipt Production Bolt 100 10 110 110
3 01-Jan-2014 Issue Production Bolt 100 40 70 40
4 02-Jan-2014 Issue Production Bold 100 15 55 55
5 03-Jan-2014 Receipt Production Bolt 100 5 60 115
I've achieved almost same result from above mentioned query but just one thing missing, that is, Last Receipt and Issue Quantities keep repeating until next Quantity comes on the way in data.
|
|
|
|
| Re: Unable to get running total conditionally [message #611493 is a reply to message #611489] |
Thu, 03 April 2014 01:29 |
|
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Something like:
SQL> With Data As (Select 1 Transaction_No, To_Date('31-Dec-2013','dd-mon-yyyy') Transaction_Date,
2 'Receipt' Transaction_Type, 100 Transaction_Qty From Dual
3 Union All
4 Select 2, To_Date('01-Jan-2014','dd-mon-yyyy'), 'Receipt', 10 From Dual
5 Union All
6 Select 3, To_Date('01-Jan-2014','dd-mon-yyyy'),'Issue',40 From Dual
7 Union All
8 Select 4, To_Date('02-Jan-2014','dd-mon-yyyy'),'Issue',15 From Dual
9 Union All
10 Select 5, To_Date('03-Jan-2014','dd-mon-yyyy'),'Receipt',5 From Dual)
11 select Transaction_No tx_no, Transaction_Date tx_dt, Transaction_Type tx_type, Transaction_Qty tx_qty,
12 sum(decode(Transaction_Type, 'Issue', -1, 1)*Transaction_Qty)
13 over (order by Transaction_No) run_stock,
14 decode(Transaction_Type, 'Receipt',
15 sum(decode(Transaction_Type, 'Issue', 0, 1)*Transaction_Qty)
16 over (order by Transaction_No)) Run_Rect,
17 decode(Transaction_Type, 'Issue',
18 sum(decode(Transaction_Type, 'Issue', 1, 0)*Transaction_Qty)
19 over (order by Transaction_No)) Run_Issue
20 from data
21 order by 1
22 /
TX_NO TX_DT TX_TYPE TX_QTY RUN_STOCK RUN_RECT RUN_ISSUE
---------- ----------- ------- ---------- ---------- ---------- ----------
1 31-DEC-2013 Receipt 100 100 100
2 01-JAN-2014 Receipt 10 110 110
3 01-JAN-2014 Issue 40 70 40
4 02-JAN-2014 Issue 15 55 55
5 03-JAN-2014 Receipt 5 60 115
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 11:30:05 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
