SQL & PL/SQL :: Number Of Years Calculation

Mar 16, 2012

for making the query for following data,when we give start and end date then query need to calculate number of years.

if less than one year then return 0
if exact one year then return 1
if exact 1.5 years (18 months) then return 2
if exact 2 years (24 months) then also return 2
if exact 2.5 years (30 months) then return 3
if exact 3 years (36 months) then also return 3
if exact 3.5 years (42 months) then return 4
if exact 4 years (48 months) then also return 4
and so.

also we need to add leap year 1 day if exist in start and end date.

YearSalary
11
1.52
22
2.53
33
3.54
44
4.55
55
[code]....

View 15 Replies


ADVERTISEMENT

PL/SQL :: Convert A Number To Years And Months

Jul 30, 2012

I would like to convert a number to years & months.

e.g. 119 = 9 years and 11 months

This would be displayed as 9.11

Is there an oracle function or sql that can calculate this value,

View 8 Replies View Related

SQL & PL/SQL :: Repetitive Calculation - Track Number 10th Record?

Sep 11, 2012

I am writing a program in that i have to do a calculation in for loop when every 10 records processed. How can i track the number 10th record .

View 4 Replies View Related

SQL & PL/SQL :: Days To Years Converter

May 11, 2012

Our "client" is delivering data that contains an "age" field. However, they've put the AGE in DAYS instead of years, while our system can only work with years. So I needed to convert the age to years.

We are getting 2 parameters: LOWAGE and HIGHAGE (both in days). However, for the same "test" (laboratory test), we are also getting some other records

eg:

TEST 1 lowage = 0 upperage = 5 OTHER values = xxx
TEST 1 lowage = 6 upperage = 100 OTHER values = yyy

So if the person would be 5 years or less, the values xxx would apply. If they are 6 years and older, the yyy values apply. Now, the problem is that we are not getting this data in years, but in days. And whichever function I try, the upperage from the first test also seems to be equal to the lowerage from the second test. And this gives an error, because both would apply to this person....

I've tried stuff like:

age/365
age/365.25 (leap years)
trunc(months_between(sysdate+ranges.lowerage,sysdate)/12)

View 6 Replies View Related

PL/SQL :: Query To Get List Of Years?

Feb 15, 2013

I am trying to generate a list of years, starting with 2010 and counting up through 5 years from the current date. For example, if it is currently 2013, the list would include 2010 through 2018. 2 years from now, I want the list to go from 2010 through 2020.

I am currently using

SELECT to_char(add_months(to_date('2010','YYYY'),12 * (rownum-1)),'YYYY')
FROM dual
CONNECT BY level <= 10to get a list with 10 entries.

How can I make this dynamic enough to return a variable number of records depending on the current year?

View 1 Replies View Related

SQL & PL/SQL :: Find Years Between Any Given Date Range

Apr 5, 2011

I require to find the years between any given date range. For example what are the years between the dates '01/12/2010' and '01/02/2012'? Answer must be '2010,2011,2012'. how to code the query for this result?

View 2 Replies View Related

SQL & PL/SQL :: Chinese Zodiac Associated With Birth Years

Aug 8, 2010

I have a question to write a pl/sql for Chinese zodiac but i cant do it, even i Google it i cant find any solution..the question is as below:

The Chinese zodiac associates birth years with the following; animals:

Birth Year Animal
1924,1936.1948,1960,1972,1984,1996 Rat
1925,1937,1949,1961,1973,1985,1997 Cow
1926,1938,1950,1962,1974,1986,1998 Tiger
1927,1939,1951,1963,1975,1987,1999 Rabbit
1928,1940,1952.1964,1976,1988,2000 Dragon
1929,1941,1953,1965,1977,1989,2001 Snake
1930,1942,1954,1966.1978,1990,2002 Horse
1931,1943,1955.1967,1979,1991,2003 Sheep
1932,1944,1956,1968,1980,1992,2004 Monkey
1933,1945,1957,1969,1981,1993,2005 Chicken
1934.1946,1958,1970,1982,1994,2006 Dog
1935,1947,1959,1971,1983,1995,2007 Pig

Write a command to declare a date variable named birth_date, and assign to it your birth date. Use an IF/ELS1F structure to test every year and determine the animal associated with your birth year. Then display your birth year and the associated animal name. For example, the program would display the following output for someone born in 1984:

I was born in 1984, which is the year of the Rat. Declare and use additional variables as needed.

View 18 Replies View Related

Forms :: Setting A Date 18 Years In Future?

Mar 26, 2011

I am trying to set a item to system date + 18 years. What type of formula or where to put such a thing. i tried putting- select add_months(sysdate,216) from dual; into the calculation-formula in property palette with no success.

View 1 Replies View Related

SQL & PL/SQL :: Query To Find User Who Did Not Login From Past 3 Years?

Nov 2, 2012

I have two table APPLICATION_1 and APPLICATION_2, where user's login date is captured.There will be multiple records for each user.Same user may or may not be present in both tables.

The requirement is to find those users who have not logged-in in the last 3 years.

-- Test Case

DROP TABLE application_1;
DROP TABLE application_2;
CREATE TABLE application_1
(
seq NUMBER ,
user_id VARCHAR2(30),

[code].....

Using the below query I'm able to get the desired output.

SELECT user_id, MAX(login_date) last_login_date FROM (
SELECT user_id, MAX(login_date) login_date FROM application_1 GROUP BY user_id
UNION ALL
SELECT user_id, MAX(login_date) login_date FROM application_2 GROUP BY user_id
) GROUP BY user_id

[code].....

View 10 Replies View Related

SQL & PL/SQL :: Calculate Balance For Last Two Cooperative Years On Input Date

Aug 24, 2012

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.

how to achieve this logic

View 4 Replies View Related

PL/SQL :: Set Boolean Flag To True If Hire_date Is Greater Than 5 Years

Dec 27, 2012

For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false

DELARE
v_Hire_date date :='12-Dec-2005';
v_five_years BOOLEAN;
BEGIN
IF
[code].....

View 17 Replies View Related

SQL & PL/SQL :: Gratuity Calculation?

Mar 16, 2012

i have attached the .csv file for gratuity calculation.

In the given criteria you can see if number of years 1 then he can get 1 salary if years 1.5 then 2 salary and so on also we need to take care about the leap year.

View 2 Replies View Related

SQL & PL/SQL :: For Ratio Calculation?

Sep 14, 2011

,i had given the sample data below

create table ex (sno number,ename varchar2(10),job_code char(4),sal number);
insert into ex values(101,'John','Java',21000,'IT');
insert into ex values(102,'Michel','BI',25000,'IT');
insert into ex values(103,'Johny','Java',30000,'IT');

[code]...

My expected output is attached in a text file

View 12 Replies View Related

SQL & PL/SQL :: Field Name Based On Calculation

Apr 19, 2010

I want to display Week No as the heading.So, I have these:

define week_no=number
column week_col new_value week_no
select to_number(to_char(sysdate, 'ww')) week_col from dual;

I have tested it and it is working as the way I expected: select 'to display week no as column' "Week &week_no" from dual;

The output:
Week 16
----------------------------
to display week no as column ...So how do I display Week 15, Week 17 etc based on the calculation of &week_no e.g. &week_no+1?

View 19 Replies View Related

SQL & PL/SQL :: Calculation On A Time Field?

Jul 5, 2012

I have time fields that have been converted from a 12hr clock to a 24hr clock and these fields hold only the time...no date.

I need to be able to determine the difference between column A and B eg.

Column A: Column B:
11:00 14:00

I can remove the ':' by using the replace command, but as I need to run on a actual 24hr clock I am not sure how to tackle the calculation as it is not the full oracle datetime format.

View 8 Replies View Related

SQL & PL/SQL :: Cumulative Calculation In Oracle?

Feb 24, 2011

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:

Month col1 _ col2 _ col3
jan 1 _ 100 _ 100 * 1
feb 2 _ 200 _ 100 * 2 + 200
mar 3 _ 300 _ 400 * 3 + 300
apr 4 _ 400 _ 1500 * 4 + 400
may 5 _ 500 _ 6400 * 5 + 500

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.

View 16 Replies View Related

SQL & PL/SQL :: Time Zone Calculation

May 30, 2010

Check the following

SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'gmt')
AT TIME ZONE '+05:30' "Time at Time zone"
FROM DUAL;

Time at Time zone
01-DEC-99 04.30.00.000000 PM +05:30

My requirement is that

I want to add 2 hours to DateTime i get here i.e. add two hours in result and display the resultant date and tine in time zone '+05:30'. I also want to check that the resultant time falls in business hours (9 am to 6 pm).

View 11 Replies View Related

Forms :: Calculation When Add Numbers

Feb 5, 2011

I am developing a form where I need to add Numbers.In fact we have a bag of Cones that contain 24 cones.In normal calculation when I add numbers for example

5.24 Plus 5.24 it will give the result 10.48

I Need the appropriate method to calculate if I add these two numbers it should give the result 5.24 Plus 5.24 the result should be 12

View 7 Replies View Related

Forms :: Time Calculation

Oct 20, 2011

Currently I am working on payroll system where I have to calculate employees working hours/late coming hours/early going hours against its roster which is defined in the beginning of every month/week.

In roster form user define shift of every employee like

Code Name Shift
7 Saad Nafees A - 09:00 17:00
492 Muhammad Nasir Shahzad B - 17:00 01:00
243 Muhammad Tahir C - 01:00 09:00

Roster table structure

code varchar2
name varchar2
Shift date
Remarks varchar2

shift table structure

code varchar2
timein date
timeout date
latetime date
Hdaytime date

Oracle stores both date and time information in date data type, suppose today user change shift timings from 17:00 to 17:30 or user change timein/timeout in attendance form then oracle will store current date with user define timings.

Now this is the main problem which I am facing because whenever you calculate difference between timein and timeout or compare with its roster then output comes wrong because oracle returns total no of hours whenever you minus two dates.

View 11 Replies View Related

SQL & PL/SQL :: FIFO Method Calculation

Jun 11, 2010

I have three table for the stock calculations. The structure are like this

Product_master
product_id number,
product_name varchar2(30),
company_id number(3),
rate_per_unit number(14,4)

Purchase_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)

Sales_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)

Purchase_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)

Sales_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)

I need to find out the valuation on particular sales date at FIFO method.

View 22 Replies View Related

SQL & PL/SQL :: Date And Time Calculation

Jun 10, 2011

T1 = 06-Jun-2011 4:00PM
T2 = 10-Jun-2011 11:AM

Calculation is Required.

If at the end of Day Hours of T1 > 6 hours then it should calculate 1 Day.

same with T2. hours of T2(from begining of Day till end Time (11AM), it should again caculate if >6 hours then 1 Day.

remaining will be date calculation. this is how T2 - T1 should give us 5 Days.

Detail
06-Jun-2011 4:00PM to 06-Jun-2011 12:00AM (8 hours > 6 = 1 Day)
06-Jun-2011 12:00AM to 07-Jun-2011 12:00AM = (1 Day)
07-Jun-2011 12:00AM to 08-Jun-2011 12:00AM = (1 Day)
08-Jun-2011 12:00AM to 09-Jun-2011 12:00AM = (1 Day)
09-Jun-2011 12:00AM to 10-Jun-2011 12:00AM = (1 Day)
10-Jun-2011 12:00AM to 10-Jun-2011 11:00AM (11 hrs > 6 = 1 Day)

this is how 5Days calculation is required.

View 1 Replies View Related

SQL & PL/SQL :: Annual Leave Calculation

May 29, 2012

We want to calculate Annual leave the scenerio is as follows

1. Employee service more the one year
2. if joining date is 07-04-2008 than on 07-04-2009 completed one year and on 07-04-2010 completed two years and so on. day and month of date must be the same.

so we need YEAR*14. e.g. if 1 year completed then 1*14 if two years completed then 2*14 and so on. by this we will get the opening of Annual leaves.

We have another table where we entered the leave day by day in whole year, if he avail 10 leaves in first year then 4 will be remaining and we can say it is the closing of first year and opening of second year. so on second year he will entitled 14 more leaves so 14 + 4 = 18 should be opening of next year.In year Caclulation Day and Month must be the same for example 07 day, 04 month for every year. 14 Annual leaves are fix for each year

following are columns for output.

empid, ename, date of joining, sysdate, leave opening, leave avail, closing balance,
1, jone, 29-05-2009, 29-05-2012
2, herry,29-05-2008, 29-05-2012
3, bell, 29-05-2006, 29-05-2012

between two dates (date of joining and system date)

View 1 Replies View Related

PL/SQL :: Calculation On Sql Column Layer

Aug 31, 2012

V: 10.2.0.3

I want to calculate on column-layer of a sql-query:

select a "column1", "column1" + 1

from ...but it does not work...

View 9 Replies View Related

Pivot Query In Oracle To Get Years From Column And Make Separate Column For Each

Jul 22, 2009

I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.

I have copied the code used in

select stud_id, 2006, 2007, 2008, 2009
from (
select stud_id,
avg(case when year=2006 then ((present/poss)*100) else null end) 2006,
avg(case when year=2007 then ((present/poss)*100) else null end) 2007,
avg(case when year=2008 then ((present/poss)*100) else null end) 2008,
avg(case when year=2009 then ((present/poss)*100) else null end) 2009
from attendance.vw_all_attendance_perc
group by stud_id
);

View 11 Replies View Related

Date Arithmetic - Formula To Subtract Years And Give Date

Sep 27, 2007

I am a novice in oracle

I have 2 columns in my table

->Col1 with experience in years entered as an integer
->Col2 with current date

I need to add another column as a date value adn for that i need to subtract Currentdate-Col1 when i tried currentdate-Col1 it just subtracted the days i need the formula to subtract years and give a date

I have worked in DB2 and all u need to do there was add the keyword years at the end but in oracle the same does not work

View 8 Replies View Related

SQL & PL/SQL :: Perform Math Calculation Upon Dates?

Feb 9, 2011

I got 4 fields:

event_date (01/01/1900 hh:mm:ss)
team_count (number)
interview_count (number)
duration (01/01/1900 hh:mm:ss) - duration of each interview

The calculation is:

(interview_count * team_count * duration(hh:mm)) + event_date
i.e.:
(3 * 2 * 02:30) + 01/01/1900 08:00:00
= 15:00:00 + 01/01/1900 08:00:00
= 01/01/1900 23:00:00.

all I care about is the hh:mm in the result.How can I preform this calculation in pl/sql?

View 7 Replies View Related

SQL & PL/SQL :: Conditional Average Calculation Of Column?

Jul 22, 2012

I want to write a sql qeuery to get result similar to expected average column as shown in sheet.

Record can be uniquely identified by columns ID, PRODUCT and OFFICE. I want to calculate average for each date considering the same record does not exists earlier i.e. I want to consider the latest vote while calculating average. E.g.

Average till date 1/1/2011 is Avg (2, 4, 3) = 3 (as no repeating vote value exists)
Average till date 2/1/2011 is Avg (4, 3, 5, 3) = 3.75 (excluding vote value 2 as 122_UK_LONDON was provided his vote earlier, so considered latest vote value i.e. 5)
Average till date 3/1/2011 is Avg (3, 5, 3, 6, 5, 8 ) = 5 (excluding vote value 2 for 122_UK_LONDON and vote value 4 for 967_Europe_London)

View 15 Replies View Related

Forms :: Calculation Total In Application?

Mar 16, 2011

I'm getting trouble in getting the sum of all the product's prize present in the form fields of invoice form. For example, I user bought 2 products so I want to get there prize sum in the total box. I first want that I've QUANTITY, DISCOUNT and SELLING PRIZE.

If I multiply Quantity with Selling Prize then I gets the result in Product and Quantity Total Box. However, Now I want to add discount option in it. Means if user is providing 10% discount to his/her customer then Quantity * Selling Prize - Discount% becomes a result in product's solution final prize box.

Here is the pic..

Look...Now here..I've added 22 in quantity.. now if I'll insert 10% in discount then the total will comes in Amount Display Box.

I'm currently using this coding on on Prize Box..
:SP_PRODUCT_PTOTAL := NVL(:SP_PRODUCT_QUANTITY,0) * NVL(:SP_PRODUCT_PRIZE,0) * NVL(:SP_PRODUCT_DST,0) / 100;

View 19 Replies View Related

Forms :: Calculation Mode With Conditions

Sep 24, 2013

I have 2 fields f1,f2

I define f1 a summary in calculation mode

I want to sum f1 where f2 = 5

View 3 Replies View Related

SQL & PL/SQL :: Total Records Count Calculation

Jan 14, 2013

We have a front end that is polling the database for some set of data.That set of data is returned by opening a ref cursor and passing it back to the calling environment.Now the problem they also want the count of total number of records that will be fetched by my select statement.One option is execute the select statement once ,get the count and pass it.But in that case i will be executuing the query twice once for count other time while openimng for the ref cursor .

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved