SQL & PL/SQL :: Cumulative Running Balance By Condition?
Mar 24, 2013
Is there a way to find running balance for accounts, suppose i have two tables.one for transaction and one summary of balance.
i want to have the closing balance based on transactions subtracted or added based on credit and debit for example.it will add to the running total if its debit and subtract if its credit and final output is the balance.
create table acnt_trans ( acnt_code varchar2(12),trans_date date,debit_amt number,credit_amt number);
insert into acnt_trans values ('1001','02-FEB-2013',200,0);
insert into acnt_trans values ('1001','03-FEB-2013',0,100);
insert into acnt_trans values ('1001','04-FEB-2013',50,0);
[code]....
--I want the output as below.if i run the report as of '04-feb-2013'
trans_date , acnt_code , debit_amt,credit_amt , balance
02-feb-2013 1001 200 0 3200
03-feb-2013 1001 0 100 3100
04-feb-2013 1001 50 0 3150 -- this is the closing balance.
I have one table where the item stock balance can be taken.What i want is like a ageing report based on the ending balance.If the ending balance is 10,000 for item a.It should bring all the Receipts which have qty lesser than 10,000.
I managed to write one function like below to get the receipts numbers but the format is 3778578-3778612-3790428 and i want to convert them into rows like
3778578 3778612 3790428
The function used is as below.
CREATE OR REPLACE FUNCTION l_get_rcpt ( l_item IN VARCHAR2, l_date DATE, l_bal IN NUMBER )
Is it possible to calculate cumulatively in oracle sql queries, i.e. using the results of the last row for calculating the values in the current row?For example I want my query to return the following:
In col3 above, for feb I want to use the result returned for jan ((100 * 1)*2+200), for mar I am using the result returned for feb((100 * 2 + 200) * 3 + 300) and so on.
i.e. I want to use the previous value of column3 to derive the current value of column3. Like using the LAG function but on the analytically derived column itself.
i have a table empl which have three column (name,period,attendance)if we pass parameter which value is based on period column
like :January
then out should come sum of all attendence of january group by name like that
name attendance a60.00 b20.00 c20.00 w40.00
if we pass parameter value :February then attendance should come sum of (January and February) and if we pass parameter March then attendance should come sum of(January,February and march)
create table empl (name varchar2(10),period varchar2(10),attendance number) insert into empl values('a','January',20) insert into empl values('a','January',10) insert into empl values('a','January',30) insert into empl values('a','February',20) insert into empl values('a','March',60) insert into empl values('b','January',20) insert into empl values('c','January',20) insert into empl values('w','January',40)
i want to get balanced amount based on max(suid ).
my query is
create or replace procedure prc_stk_upd_pur(v_comp_suid in i_purchase_h.pih_company_suid%type, v_area_suid in i_purchase_h.pih_area_suid%type, v_invoice_dt in i_purchase_h.pih_invoice_date%type, v_pih_suid in i_purchase_h.pih_suid%type, v_type in i_purchase_h.pih_type%type) is [code]......
my output is SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID 1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382 2121511/29/20121317151346LP11115755.00055.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383 3121611/29/20121317151346LP11115730.00030.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
my output should come SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID 1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382 2121511/29/20121317151346LP11115755.00075.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383 3121611/29/20121317151346LP11115730.000105.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
My problem is that I am working with a balance (Infor/WorkBrain - Time & Attendance) I have a balance called Bank Holiday(bal_id 10044). Every holiday that an employee works, he is allowed to take another day off in lieu of the day he worked. The employee has up to twelve months to take that holiday.
My job is to figure out when he worked that holiday (not a problem) and send an email/note at the 11 month mark and notify the employee and supervisors that said employee has 1 month to use his banked holiday.
My problem is the data/constraints I am working with. I can tell what day the holiday was based on a time code (EBLOG_MESSAGE) and EBLOG_DELTA shows a positive 1. I can also tell when the employee has used a bank holiday (EBLOG_DELTA is -1) and EBLOG_MESSAGE = FR_HWO.
But how to I perform the deduction so that I get four most current dates. Meaning since my first date that the employee gets for a holiday wokred is 6/3/2010 but the employee used a bank holiday on 8/31/2010. So I want to display the dates 7/1/2010, 7/27/2010, 8/1/2010 and 10/15/2010.
My preference would be not to add anything new to this table as it is a core table for Infor/WorkBrain.
I am having a table with 5 lakhs transactions. I want to fetch the last balance for a particular date. So i have have returned a query like below.
SELECT curr_balance FROM transaction_details WHERE acct_num = '10'
[Code]...
This has to be executed for incrementing of 12 months to find the last balance for each particular month. But this query is having more cpu cost, 12 times it is taking huge time. how to remodify athe above query to get the results in faster way using analytical query. Whether this can be broken into two part in PL/SQL to achive the performance. ?
I am doing a report to show current balance, aging (30-60), (60-90), (90-120) and 120+ and a combination of all of these should be the total balance overdue.
If i use a formula cclumn in the report builder, how should i write this query to calculate the total?
I need to calcuate balance for last two cooprative years on input date.
example
if input date is-->"01-AUG-2012" if (month >=7) -->then i need to calculate from 01-july-2010 to 30-june-2011 and 01-july-2011 to 30-june-2012 and 01-july-2012 to input date.
if input date is-->"01-june-2012" if (month<7) -->then i need to calculate from 01-july-2009 to 30-june-2010 and 01-july-2010 to 30-june-2011 and 01-july-2011 to input date.
Background: We are migrating a lot of databases from one SAN appliance to another. We are doing this by adding new disks from the new SAN appliance to the existing disk groups, re balancing, removing the old disks from the disk groups, and then re balancing again.
Question: If I execute two ALTER commands with the same power on 2 or more separate disk groups, do both operations start executing right away? Or do they queue up and execute one after another?
I ask because we would like to queue up several re-balances so we don't have DBAs watching status bars all day.
I have EMPLOYEE table that have 3 records with EMP_ID 1, 2, 3. Now I want to run below query
select emp_id from employee where emp_id in (1, 2, 3, 4, 5);
It will return only 3 records but i want those records also which is not available in employee table. Is this possible without using another table or creating another table. Actually I don't have enough privileges to create table.
& want output like below
EMP_ID 1 2 3 4 Not Found 5 Not Found
Here emp_id 4, 5 is not available in employee table, but query should return those value also with comments like "Not Found"
I need to check somewhere in the code as " Check the variable with in 20 varaiables , if not excute somes statements"
Example
a1=20,a2=30,a3=40......a20=200; (Like this way i have 20 varaibles with values) v1=150; if( v1<>a1 AND V1<> a2 AND V1 <> a3...... AND V1<>20) execute some insert block; End if;
But it's writting very diffcult as i need to compare with 20 varaiables.Was there any alternative sloution like taking some VARRAY and checking in IF condition like v1 NOT IN(Va) (Where 'Va' is like VARRAY)
Below is my query Issue faced is that based on union I want to limit the result and does not want second query to give duplicate result as both queries deal with same table
SELECT TAB1.ID, TAB1.CRNCY_CODE, TAB2.SCHEME_CODE, DECODE( 'INFENG',CAST( '' AS VARCHAR(20)), NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC), TAB1.SUB_HEAD_CODE, DECODE( 'INFENG' ,CAST( '' AS VARCHAR(20)),NVL(TAB1.ALT1_SUB_HEAD_DESC , TAB1.SUB_HEAD_DESC),TAB1.SUB_HEAD_DESC)
[code]...
select null ID,null crncy_code,TAB2.SCHEME_CODE , NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC,null SUB_HEAD_CODE from TAB2 where TAB2.SCHEME_TYPE ='SCHEME1';
I had a procedure in which there will be multiple update and select statements Eg: Update table T1 set Column1='X' where Column2 in ('A','B','C') All the update/select queries will have same set of values in the in condition ('A','B','C') will remain same. But the tables and columns will vary.So, I would like to declare a variable/array which holds the values in the in condition
var1 := {'A','B','C'}
and use in my statement like :-
Update table T1 set Column1='X' where Column2 in var1. Is there any way to acheive this?
I would like to use REGEXP_LIKE condition expression with '[:alnum:]' operator in Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 for to check the string is alphanumeric or not.Why do i need: I have xinput field [RAW(24)] which not sure to contains always an alphanumeric string. We should use only when xinput field is an alphanumeric string.
x varchar2(50); x:=rtrim(ltrim(utl_raw.cast_to_varchar2(xinput)));
how should i use REGEXP_LIKE
select 'TRUE' from dual where REGEXP_LIKE( 'True', '[:alnum:]') select 'TRUE' from dual where REGEXP_LIKE( 'False%*^tt123.***', '[:alnum:]')
I'm passing some parameters from java code to a query as follows:
select * from table1 where col1 = 'yes' --want to make this part dynamic *$[variable]*
where the $[variable] = 'and col2 like ''some%'' and col3 = ''good'''
So this query is giving me an error saying that it cannot construct such query because i guess what it's trying to run is:
select * from table1 where col1 = 'yes' *'and col2 like ''some%'' and col3 = ''good'''*
I need to remove the first and last single quote... I tried using double quote for my string and replace all single quotes with empty but the "LIKE" function only works with single quotes (like 'some%')..
I also tried using:
select * from table1 where col1 = 'yes' and trim (both '''' from 'col2 like ''some%'' and col3 = ''good''')
but i get a "ORA-00920: invalid relational operator"...
I want to run multiple sql select command after loggin to sqlplus and need to pass the value to variable to check the condition.
When I am running the below script I am getting the below error ---------------------- SP2-0734: unknown command beginning "return :va..." - rest of line ignored. SP2-0734: unknown command beginning "return :va..." - rest of line ignored.
I have a query with order by clause, which takes 30 sec to execute with order by clause. And if i remove the order by clause it executes within 1 sec.
The column in the order by condition has index. but when i see Explain plan output. it doesn't show this index is being used. I tried to execute query with INDEX hint but still explain plan is not showing this index.
And I just need to display only the T's that would meet the condition Col2 in (15,18) and Col3 in (20,19).
I try with a condition where col2 in (15,18) and col3 in (20,19) and that works, but I don't need to display T1, because T1 has a Col2=5 and Col3=20, I just have to display T2 that just satisfies all my conditions, Col2 in (15,18) and Col3 in (20,19). It's something like a vertical search.
INSERT INTO emp VALUES (v_empid, v_ename, v_salary);
where v_empid, v_ename AND v_salary are variables.v_salary IS nullable Now IF v_salary IS null, oracle IS IS showing an error LIKE below:
ERROR at line 54: ORA-06550: line 54, column 172: PL/SQL: ORA-00984: column not allowed here ORA-06550: line 52, column 5: PL/SQL: SQL Statement ignored
Can I use an if stmt inside the insert stmt. If v_salary is null I want null there else the value in v_salary?
