SQL & PL/SQL :: Calculate Balances For Each Month
Apr 8, 2013
have this SQL query works fine but I want to calculate the balances for each month...I STARTING to do so: I have to:
JanuaryBalance = Initialbalance + January;
FebruaryBalance = JanuaryBalance+February;
MarchBalance = FebruaryBalance + March;
SELECT
Sum("F0902"."GBAPYC"/100) AS "initialBalance",
Sum("F0902"."GBAN01"/100) AS "January",
Sum("F0902"."GBAN02"/100) AS "February",
Sum("F0902"."GBAN03"/100) AS "March",
[code]....
View 10 Replies
ADVERTISEMENT
Mar 28, 2013
I need a pl/sql function to calculate the financial month from an date... For eg. If i enter input date as '21-sep-2012' the function should return me the output value in months as 6. so it should calculate from april to september of that year...
View 13 Replies
View Related
Jun 4, 2013
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
View 2 Replies
View Related
May 16, 2013
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53 24
2013 March loc1 55007 78 57 28
2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53
2013 March loc1 55007 78 57
2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
View 5 Replies
View Related
Nov 25, 2010
I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL
FROM (
SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION
SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION
SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
Result
ID_CLIENTDT_REGVAL
101/01/2010200
101/02/2010300
101/03/2010500
101/04/2010650
101/05/2010750
201/01/2010100
201/03/2010320
201/05/2010820
301/02/2010150
301/05/2010250
View 17 Replies
View Related
Jun 3, 2011
only one table A(autoid, number)
how SELECT statment result: B(autoid, number, numberB)
numberB is sum of all the number have autoid > its autoid.
View 4 Replies
View Related
Oct 20, 2010
Trying to accomplish:
I am trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.
DML DDL and package
I the DML and DDL and the package as an attachment.
Problem
Errors below
32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:
, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.
32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:
LINE/COL ERROR
-------- ----------------------------------------------------------------
. ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset
33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset
View 39 Replies
View Related
Oct 21, 2011
How to calculate exact age for example my date of birth is 10-04-1972 and today current date is 21-10-2011 so i want to calculate age how many years, how many months and how many days.
View 27 Replies
View Related
Jan 1, 2013
I need a query to find out the working days. I have attached the sample script to create table and data. Here is the description of the tables.
Emp1 : To record the employe's information and weekly rest day.
Attendance :- To record daily attendance.
Leave_appovd : To record the approved leaves.
Holiday : To record the holidays.
W = Attendance
R = Weekly Rest
L = Leave
H = Holiday
A = Absent
Required output is
Emp No.In Date Out Date Type
736912/1/201212/1/2012W
736912/2/201212/2/2012R
736912/3/201212/3/2012W
736912/4/201212/4/2012W
736912/5/201212/5/2012W
736912/6/201212/6/2012L
736912/7/201212/7/2012H
736912/8/201212/8/2012H
736912/9/201212/9/2012R
736912/10/201212/10/2012A
736912/11/201212/11/2012W
736912/12/201212/12/2012W
736912/13/201212/13/2012W
736912/14/201212/14/2012W
736912/15/201212/15/2012L
736912/16/201212/16/2012L
736912/17/201212/17/2012L
736912/18/201212/18/2012W
736912/19/201212/19/2012W
736912/20/201212/20/2012W
736912/21/201212/21/2012W
736912/22/201212/22/2012W
736912/23/201212/23/2012R
736912/24/201212/24/2012A
736912/25/201212/25/2012A
736912/26/201212/26/2012A
736912/27/201212/27/2012W
736912/28/201212/28/2012W
736912/29/201212/29/2012W
736912/30/201212/30/2012R
736912/31/201212/31/2012W
778212/1/201212/1/2012W
778212/2/201212/2/2012W
778212/3/201212/3/2012W
778212/4/201212/4/2012W
778212/5/201212/5/2012W
778212/6/201212/6/2012W
Separate Query will be run for the following output.
Wroking Days
EmpnoAttend WeeklyRestsLeavesHolidays TotalAbsentsG. Total
7369 17 4 4 2 27 4 31
7782 6 0 0 0 6 25 31
If any body work on weekly rest or holiday, it will be considered as weekly rest and holiday. There working on these days will be treated separately.
View 10 Replies
View Related
Dec 21, 2012
We have 2 tables.
Example :
Table Name1: Sales_orders - Columns(Cust_Id,Customer,Quantity,Unit_Price,........etc)
Table name2: Sales_taxes - Columns(Central_sales_Tax,Service_Tax,ValueAdd_Tax,Entry_Tax,Professional_Tax)
My requirement is;
I what create new table/view which contains all value of both table and new column called Total Amount.
Total amount=SUM(Quantity*Unit_Price+Central_sales_Tax+Service_Tax+ValueAdd_Tax+Entry_Tax+Professional_Tax)
View 9 Replies
View Related
Nov 14, 2006
how would I be able to calculate the Redo rate for using in the Required bandwidth Formula as seen below :
Required bandwidth Formula ((Redo rate in bytes per second / 0.7) * 8) / 1,000,000 = bandwidth in Mbps
Example: 385 KB/sec peak rate would require an available network bandwidth of at least
((394253 / 0.7) * 8) / 1,000,000 = 4.5 Mbps.
SOURCE OF FORMULA Network Bandwidth Implications of Oracle Data Guard...I'm using Oracle 10g
View 3 Replies
View Related
Mar 13, 2013
I want to calculate the running percentage. sample data is as below.
CREATE TABLE prod
(
code VARCHAR2(8),
name VARCHAR2(30),
stdate DATE,
itons NUMBER(7,3),
rtons NUMBER(7,3)
[code]....
Required Output is
CODE NAME STDATE ITONS RTONS %age
-------- ------------------------------ --------- ---------- ---------- -------
0-01-001 SEEDS 17-JUL-12 193.155 0
1-01-001 OIL 17-JUL-12 0 81.906 42.404
1-02-001 MEAL 17-JUL-12 0 104.304 54.000
1-03-001 DURT OIL 17-JUL-12 0 1.242 0.643
2-01-001 WASTE 17-JUL-12 0 5.703 2.953
[code]....
Percentage will be calculated as rtons of code not having first digit 0 devided by itons having having code 0 result multiply 100 for the same date code wise e.g. For dated 17-jul-13 meal percentage will be calculated as round((104.304/193.155)*100,3)=54.000
View 6 Replies
View Related
Apr 5, 2011
How To Calculate Average in Forms 6i for example a summary column named (Amount = 5000) and i want to calculate 15% average of this amount i want to calculate it like summary column .
View 2 Replies
View Related
Jun 25, 2011
i have two dates like
30-may-2011 11:50:34 and 27-may-2011 13:59:37
how i can calculate proper hrs between these dates? My condition is time calculate from 8.30AM to 5.30PM
it does not considered 24 hrs
if i calculate hrs
30-may-2011 11:50:34 - 27-may-2011 13:59:37
then it shows 69.85 hrs . but it considered full 24 hrs.
View 5 Replies
View Related
Sep 14, 2011
Following is the scenario:
CREATE TABLE EMP_VACATION(
EMP_NO NUMBER(3),
VAC_TYPE NUMBER(1),
START_DATE DATE,
END_DATE DATE,
START_PERIOD_DATE DATE,
END_PERIOD_DATE DATE,
PRIMARY KEY (EMP_NO,VAC_TYPE,START_DATE))
[code]....
--How to calculate START_PERIOD_DATE and END_PERIOD_DATE for every time insert reocrd, like for 4 cases?
--I need to check start_date with last period date for same employee.
And based upon calculate START_PERIOD_DATE and END_PERIOD_DATE,,,,,
View 7 Replies
View Related
Aug 10, 2003
I want to calculate time difference b/w two time clocks, just like we calculate the date difference and answer is in days, In the same way i like to have answer in hrs,min and ss.
View 4 Replies
View Related
Nov 23, 2007
how do w calculate sum in a form? for example in datablock sale, I have few rows of data, i want to calculate the total of product purchased (column totitem) and , sum of the customer have to pay. (totalprice). how to do the calculation in a program unit?
View 13 Replies
View Related
Oct 31, 2011
how can create function to calculate the vacation example if i takes 10 days vacation and Friday , Saturday only a normal vacation but The rest of the days is Within the vacation
how can calculate the vacation and make this function dynamic such as when takes the vacation 1-1-2011 and number of days vacation 10 not including the Friday, Saturday days
View 4 Replies
View Related
Jul 8, 2010
We have a table which contains the outage data as below,
TAG CI_NAME OUTAGE_START OUTAGE_END
IM10366 FD0004 06-jul-2010 10:00:00 10-jul-2010 10:00:00
so from the above record, the outage is from 6th - 10th July. So for the given TAG & CI_NAME, we have to calculate the outage for day. So the above record should be split as below
TAG CI_NAME O_START O_END TA_PERIOD OUT_HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 06-jul-2010 14HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 07-jul-2010 24HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 08-jul-2010 24HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 09-jul-2010 24HRS
IM10366 FD0004 06-jul-2010 10-jul-2010 10-jul-2010 10HRS
View 1 Replies
View Related
Feb 24, 2010
can I use calculated fields (b) for another calculation (c) in the same select?
select 10*a as b,
10*b as c
from tab
The code gives an error, but how can I do this?
View 12 Replies
View Related
Mar 2, 2010
I want to calculate two parameters A and B which are interdependent. Unfortunately, my code is not working correctly:
create table temp_res1 as
select *
from temp_res
model
dimension by (rn)
measures ( 0 as A,
0 as B
[code]....
The correct result should be:
create table temp_res (rn integer, a integer, b integer);
insert into temp_res values(1, 5, 10);
insert into temp_res values(2, 11, 6);
insert into temp_res values(3, 7, 12);
insert into temp_res values(4, 13, 8);
insert into temp_res values(5, 9, 14);
insert into temp_res values(6, 15, 10);
insert into temp_res values(7, 11, 16);
insert into temp_res values(8, 17, 12);
but I get:
create table temp_res (rn integer, a integer, b integer);
insert into temp_res values(1, 5, 10);
insert into temp_res values(2, 11, 6);
insert into temp_res values(3, 1, 12);
insert into temp_res values(4, 1, 2);
insert into temp_res values(5, 1, 2);
insert into te
View 4 Replies
View Related
Aug 1, 2012
i have two tables
first table
employees
emp_no pk
emp_name
emp_salary
emp_hiredate
emp_birthdate
-------------------
second table
sanctions
sanc_id
sanc_name
emp_no fk
i need the following
if employees take sanctions and his salary 1000 and sanctions value 50 then the net salary is 950 . I need if the same employee take another sanctions values 50
the result will be
the old net salary is 950
and the new old salary is 900
View 17 Replies
View Related
Dec 14, 2010
PMT function which calculates the payment for a loan based on constant payments and a constant interest rate. I need to build PMT function in oracle.
View 2 Replies
View Related
Jul 26, 2010
My applications is integrate with Excel 2003 by Ole2 package. Some of them excel books are using functions activates with Excel Complements (activate with men options: Tools-Complements-Analyisis Complements and Analysis complements VBA).
The problem is when I invoke this excel book in Oracle Forms, this functions don't work.
View 3 Replies
View Related
Dec 15, 2011
I have to procedure that computes number of project, and average working hours of employees where employee id is passed as a parameter to the procedure. If the average working hours is less than 10 then employee's salary remain the same, otherwise check if number of project is less than 4 then 5% of salary, else 10% of salary is added to the salary.
my tables are:
CREATE TABLE employee(
empid number(5),
empname varchar(20),
address varchar(20),
no_of_dependents number(5),
deptno number(5),
CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(empid),
CONSTRAINT EMPLOYEE_FKEY FOREIGN KEY(deptno) REFERENCES department(deptno));
[code]...
the problem is i have to use only 2 cursors - 1 for select and 1 for update.
View 27 Replies
View Related
Feb 28, 2013
I need to subtract multiple numbers to get the difference between each numbers, the amount of numbers to subtract between each others can vary between 2 and 10.
create table table_1 (
col1 varchar2(5),
col2 number(6,0),
col3 number(12,0)
)
insert into table_1 values ('AAA', 34379, 11111);
insert into table_1 values ('AAA', 39032, 11111);
insert into table_1 values ('AAA', 54337, 11111);
insert into table_1 values ('AAA', 78005, 11111);
insert into table_1 values ('AAA', 66793, 22222);
insert into table_1 values ('AAA', 74323, 22222);
insert into table_1 values ('AAA', 81426, 22222);
Expected Output:
col1 col2 col3
AAA -4653 11111
AAA -15305 11111
AAA -23668 11111
AAA -7530 22222
AAA -7103 22222
View 6 Replies
View Related
Nov 29, 2011
during application migration, i got one table from MS Access, and have situation where two events are splited into 4 columns (start: date1 time1 and stop: dat2 and time2). How to properly calculate duration between these two events, and show it in format: hh:mi ?
CREATE TABLE ACCBTP_DCZASTOJ
(ID NUMBER(11,0),
NASTANAK_KVARA DATE,
VRIJ_NASTKVARA DATE,
VRIJEME_OPRAVKE DATE,
DATUM_OPRAVKE DATE);
[Code]....
View 6 Replies
View Related
Oct 5, 2005
Is there any function avaialble in SQL that can return the highest common factor among a bunch of numbers. For example
10,20,25 have a highest common factor of 5.
View 9 Replies
View Related
May 8, 2010
I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?
Actual Output:::
DEPt_Name SALARY
ACCOUNTING 8750
RESEARCH 10875
SALES 11600
I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column
View 4 Replies
View Related
Aug 27, 2013
we have 7 databases running in a server .
OS : solaris
i want to add a new database on that server . So i want to find , whether we have a sufficient memory to add a new database . how to find it ?
View 5 Replies
View Related
