SQL & PL/SQL :: Analytical Query Moving Average

Aug 10, 2013

I need to calcaulate the salary avarage for three days prior, leaving the current row. That should happen to every row moving back words.I have given all the details.

create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
name varchar(20),
Start_Date DATE,
Salary Number(8,2),
mv_avg number(8,2)
[code]....

View 17 Replies


ADVERTISEMENT

PL/SQL :: Sql Query To Build Average Value

Jul 18, 2013

I'm trying to build a query capture the below information. Table looks something like below . 

RoomSubjectStudentsA1Science10A1Maths20B1Science15B2English25C1Deutsch20A1French15C1English10 

How can i write a query so that i takes the average of students in each room and display the output? I need to write a query so that it also picks up any new rooms and students added.  Average is sum of total students in room by number of times each room is specified.  

eg: A1 is listed 3 times a the result would be 10+20+15 divided by 3.    

A1    B1    B2    C115    15    20    15 

View 7 Replies View Related

SQL & PL/SQL :: Analytical Query To Find First And Last Value In Date?

Jul 2, 2013

My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.

My table looks like this.

Input Table:
-----------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1331-Mar-13
Mem1ABC1-Apr-1331-May-13
Mem1ABC1-Jun-1330-Sep-13
Mem1ABC15-Oct-1331-Dec-13
Mem1ABC1-Jan-1431-Mar-14
Mem1XYZ1-Apr-1430-Jun-14
Mem1XYZ1-Jul-1431-Dec-14

Expected Output:
----------------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1330-Sep-13
Mem1ABC15-Oct-1331-Mar-14
Mem1XYZ1-Apr-1431-Dec-14

In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.

Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.

Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.

1) CNT_VAL is the pseudo column:
-----------------------------
CK_IDPI_IDEFF_DT TERM_DT CNT_VAL
Mem1ABC1-Jan-1331-Mar-131
Mem1ABC1-Apr-1331-May-131
Mem1ABC1-Jun-1330-Sep-131

[code].....

My Query :
----------

I not getting the desired output here as the value in pseudo column is 3.

select CK_ID, PI_ID,EFF_DT,TERM_DT,
(case
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT)
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1)
else to_char(nvl(case_CONT,0))

[code].....

Scripts:
--------------------
Create table lead_test(
CK_ID varchar2(10),
PI_IDvarchar2(10),
EFF_DTDate,
TERM_DT date);

[code].....

View 1 Replies View Related

SQL & PL/SQL :: Distinct With Analytical Query And IN Statement?

Nov 18, 2011

We've got a query which returns one row, but uses an IN statement. The IN statement links to more than one row in the subquery. When we use a combination of DISTINCT and an ANALYTICAL sum, the sum total is multiplied by the number of rows in the sub query. Remove the DISTINCT and we get a single value.

A simplified example of the problem is below.

I can't see how a query which returns a single row then returns multiple values with the addition of a DISTINCT. Removing the analytical sum also provides a single row, but we need this in the actual query we're running. So it seems to be some combination of DISTINCT, ANALYTICAL SUM and IN query is causing multiple values to be returned.

CREATE TABLE go_test_distinct1
(gtd_value NUMBER);
-- Three identical values
-- To replicate the three identical values returned by

[code].....

View 12 Replies View Related

SQL & PL/SQL :: Convert Query To A Rolling Average?

May 2, 2013

I have the following query that creates sums by week.

SELECT cidterr.rnam,
cidterr.rnum,
cidterr.tnam,
cidterr.tnum,

[code]...

I want to convert this query to just return a single line for -cidterr.rnam, cidterr.rnum, cidterr.tnam, cidterr.tnum

With an average sum by week. Similar to how if I did a sum by week from the original query and placed the results into an excel pivot and said show total as average.

View 3 Replies View Related

SQL & PL/SQL :: Query Using Analytical Functions-date Range

Nov 30, 2010

I have a table which has the attached data.

Sample data is here

LOGON_DATE NUMBER_OF_LOGINS
11/28/2010 02:00:001
11/28/2010 03:00:001
11/28/2010 04:00:002
11/28/2010 06:00:004
11/28/2010 07:00:002
11/28/2010 08:00:003
11/28/2010 09:00:006
[Code] ........

I am trying to do a report like this.

Date PeakUsersBetween6AMand6PM AVGUsersBetween6AMand6PM PeakUsersBetween6PMand6AM AVGUsersBetween6PMand6AM

Output should be
11/28/2010 25 11 49 27
11/29/2010...

I am using analytical function to do this, It throws an error range cannot be used for dates.

View 5 Replies View Related

SQL & PL/SQL :: Taking Latest Current Balance Using Analytical Query

Jan 22, 2013

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. ?

View 9 Replies View Related

SQL & PL/SQL :: LAST-VALUE Analytical Function?

Sep 11, 2012

I'm posting below test case in which I'm not able to understand output for LAST_VALUE function. I'm expecting maximum value for the salary in a department. Because I'm partitioning by department and ordering a partition as assending so being last value it should give me maximum value within a partition i.e. department in this case.

CREATE TABLE EMP_MST
(
EMP_ID NUMBER(5),
EMP_NAME VARCHAR2(30),
CONSTRAINT PK_EMP_MST PRIMARY KEY(EMP_ID)

[code]...

View 4 Replies View Related

SQL & PL/SQL :: From Analytical To Subquery

Jun 10, 2010

I was reading a tutorial for analytical function and i found something like this

sum(princial) keep(dense_rank first order by d_date) over partition by (userid, alias, sec_id, flow, p_date)

How to translate this into simple queries / subquery? i am aware that analytical function are faster but i would like to know how this can translate to using query without analytical function.

View 12 Replies View Related

SQL & PL/SQL :: Average Over Time

Sep 27, 2011

I have a table with products (xPRODUCT), dates (xDATE) and parameters (xPARAMETER). Now I want to calculate the rolling 12 month average of the parameters over all products for all dates. I tried this:

select xDATE,
avg(xPARAMETER) over ( order by xDATE
range between numtodsinterval(365,'day')
preceding and current row )

[Code]....

... but this does not work. How can I do this?

View 5 Replies View Related

SQL & PL/SQL :: AVERAGE By Hour

May 9, 2010

I wish to query the pro_vitual_bytes by hourly average by server name from 04/01/2010 to 04/02/2010. how i going to perform the query.

SERVER_NAME DATE_TIME PRO_VITUAL_BYTES

MLYORABA04/01/2010 00:00:402,238,312,448.00
MLYORABA04/01/2010 00:01:402,241,458,176.00
MLYORABA04/01/2010 00:02:402,238,312,448.00
MLYORABA04/01/2010 00:03:402,241,458,176.00

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Restriction On Analytical Functions

Dec 6, 2012

Is there any way to apply the restriction on analytical functions, just like WHERE and HAVING .AS we know that we can apply the restriction on table by using WHERE and grouping functions by using HAVING clause .

For Ex: Departments wise count including all employees record :

SQL> select count(*) over(partition by deptno) dept_Count, ce.*
2 from scott.emp ce
3 order by deptno, job;

DEPT_COUNT EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
3 7934 MILLER CLERK 7782 1/23/1982 1300.00 10
3 7782 CLARK MANAGER 7839 6/9/1981 2450.00 10
3 7839 KING PRESIDENT 11/17/1981 5000.00 10
5 7788 SCOTT ANALYST 7566 4/19/1987 3000.00 20
[code]....

View 4 Replies View Related

SQL & PL/SQL :: Oracle Analytical Function

Jul 11, 2011

Here is the test-table creation script:

CREATE TABLE TEST1 (AGG_DATE DATE, COL1 NUMBER(9), COL2 NUMBER(9), COL3 NUMBER(9));
Here is the test-data population script:
insert into TEST1 (AGG_DATE, COL1, COL2, COL3)
values (to_date('01-01-2012', 'dd-mm-yyyy'), 1, 1, 1);
[code]....

The problem is when I wrote an analytical query, it is giving the BEGIN_DATE and END_DATE by taking all the partition values together and so instead of the values above, it is creating an answer as follows:

Wrong Dataset

BEGIN_DATEEND_DATECOL1COL2COL3
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/4/20121/11/2012222
1/4/20121/11/2012222
[code]....

Only the last row is correct. What can I do to get the right answer as I know am falling short? Here is my current query:

SELECT MIN(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) BEGIN_DATE,
MAX(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) END_DATE,
COL1,
COL2,
COL3
FROM TEST1;

View 6 Replies View Related

PL/SQL :: Analytical Functions In Group By

Sep 27, 2012

I have simplified this for ease of understanding. I have a Data column and a Month_ID column like this:

Values Month_ID
--------- -------------------------------------------------------
AAA 1
BBB 2

I split this out to values per year like this

Value_2011 Value_2012 Month_ID
-------------------------------------------------------------------------
AAA 1
BBB 2

Now i am trying to get the max(Value_2011) keep (dense_rank Last order by Month_ID) but i get a NULL. I can understand its because the Month_ID accomodates all years but i only need it to look at Month_ID for 2011 and return me the last dense_rank value, how can i achieve this?

I tried a couple of different methods like Last_Value() but i have group by in my original statement and i think analytical functions dont like GROUP by if they are not part of it. How can i achieve this?

View 2 Replies View Related

Forms :: How To Calculate Average In 6i

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

SQL & PL/SQL :: Average Of Time In HH:MM:SS Format

Jun 17, 2010

how to calculate the average of the time in th e HH:MM:SS format stored database table.. column contains hundreds of time values and need to table the avergae of it

my col look like,and column is declared as timestamp(6).

MY COL
------
1:13:00
1:06:00
0:43:00
0:47:00
0:32:00
0:19:00
0:39:00
0:46:00
0:56:00
1:39:00

[Code]...

View 29 Replies View Related

PL/SQL :: Get Sum Average And Projected Totals In Same SQL

Nov 23, 2012

Version : 11g

I have a table with the following format and data

Serial no    exp_Date  exp_type   exp_amount
1              01-nov-2012   Rent       10000
2              02-nov-2012   gas          250
3              02-nov-2012   insurance   9500
.
.
.

I want to create a sql output for a yearly view in the format

   exp_type        JAN            FEB      MAR        APR ..... NOV  DEC          TOTAL     AVERAGE    PROJECTED
    Rent             10000        10000   10000     10000   10000  10000      120000     10000        120000
    gas                250             250     250        250      250      250          3000         250           3000
.
.

Now a couple of things in this

1. the average gives the average for the year, so lets say its start of 2013 and we are in feb, there will not be any values for the remaining months, so it should do the average for that exp_type for Jan and Feb based on the exp_amount entered against that type and show what is the expected average. Similary, projected will that average amount and mulitply it by 12 to show the exp amount expected based on current expenses

I was able to come up with the following sql to get the sum based on months, was not sure about average, total and projected

SELECT       exp_type
,        SUM (CASE WHEN to_char(exp_date,'Mon') =  'Jan' THEN exp_amt END)     AS Jan
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'Feb' THEN exp_amt END)     AS feb
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'Mar' THEN exp_amt END)     AS Mar
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'Apr' THEN exp_amt END)     AS Apr
,       SUM (CASE WHEN to_char(exp_date,'Mon') =  'May' THEN exp_amt END)     AS May


[Code]....

getting the correct avg, total and projected fields also in the same sql?

View 8 Replies View Related

Load Average In AWR Report?

Jul 26, 2013

,I've some database in 11.2 RAC on AIX.I was analyzing the root causes of eviction.Looking AWR Report before the reboot I see: 

DB1  Host CPU (CPUs:    6 Cores:    3 Sockets: )~~~~~~~~         Load Average               Begin       End     %User   %System      %WIO     %Idle           --------- --------- --------- --------- --------- ---------                4.18     12.33     60.9      12.6       1.6      26.5  Instance CPU~~~~~~~~~~~~              % of total CPU for Instance:      27.4              % of busy  CPU for Instance:      37.3 %DB time waiting for CPU - Resource Mgr:      10.6  DB2   Host CPU (CPUs:    6 Cores:    3 Sockets: )~~~~~~~~         Load Average               Begin       End     %User   %System      %WIO     %Idle           --------- --------- --------- --------- --------- ---------                3.77    13.93     60.7      12.5       1.6      26.7  Instance CPU~~~~~~~~~~~~              % of total CPU for Instance:       6.9              % of busy  CPU for Instance:       9.5  %DB time waiting for CPU - Resource Mgr:       0.0 

Do you think these value ar high? This is vmstats at the time of reboot:

  DATARUNBCKAVMFREPREPPIPPOPFRPSRPCYFINFSYFCSCUSCSYCIDCWA07/21/2013  00:08:173107.400.345579.92308100003.292187.01019.56084160007/21/2013  00:08:171717.390.187589.884017600003.681169.99421.48281190007/21/2013  00:08:172717.402.121577.816011500003.150157.21018.50384160007/21/2013 

[code]...

View 3 Replies View Related

SQL & PL/SQL :: Windowing Clause In Analytical Function?

Oct 5, 2010

I need to calculate the sum of values over a period of exactly one month (including the current row). Now if I use a windowing clause of "range between interval '1' month preceding and current row", the total period length is 1 month plus one day (being the day in the current record).

Basically, I want to sum over a period starting at "add_months(startdate, -1) + 1" up until startdate of each row.

drop table window_tst;
create table window_tst
( id number primary key

[Code]....

So instead of having 01-feb going back to 01-jan, it should only include 02-jan till 01-feb

I could of course recalculate the period length back to a number of days for each row, but that is not really what I would prefer, as it would make the code rather unreadable.

View 5 Replies View Related

SQL & PL/SQL :: Analytical Function Instead Of Group By Clause?

Sep 18, 2012

I want to use Analytical function instead of group by clause for below query..

select
CASE
WHEN ADMT.SOURCESYSTEM ='CLU'
THEN COUNT(ADMT.TOTAL_COUNT)*5
ELSE COUNT(ADMT.TOTAL_COUNT)
END TOTAL_COUNT
from ESMARTABC.ABC_DRVR_MFAILS_TMP ADMT
group by ADMT.SOURCESYSTEM

View 1 Replies View Related

SQL & PL/SQL :: Analytical Function With Distinct Keyword

Mar 8, 2012

can we use distinct keyword with the count and sum analytical functions?

View 5 Replies View Related

SQL & PL/SQL :: Cursor Calculate Number / Average / If

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

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

PL/SQL :: Average In Tree With Partial Results

Feb 27, 2013

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE     11.2.0.1.0     Production"
TNS for HPUX: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

I have a problem related with hierarchical queries, I have this tree.
-    a
    /
   b   c
/ |
d  e  fI need to calculate an average per node but using always the child's results for example: Only leafs will have values:

d = 10
e = 20
f = 30
c = 40So the expected result is:
d = 10
e = 20
f = 30
c = 40
b = (10(d) + 20(e) + 30(f)) / 3 (number of child nodes) = 20
a = (20(b) + 40(c)) / 2 (number of child nodes) = 30.

I have tried with recursive queries, hierarchical queries, I guess it's possible with model too but I can't produce the exact results that I need. Maybe in fact there is a very simple solution but I cannot figure it.

Here is an auxiliary WITH that you can use to start your tests:
WITH tree AS
(
     SELECT 'd' child, 'b' parent FROM dual UNION ALL
     SELECT 'e' child, 'b' parent FROM dual UNION ALL
     SELECT 'f' child, 'b' parent FROM dual UNION ALL
     SELECT 'b' child, 'a' parent FROM dual UNION ALL
     SELECT 'c' child, 'a' parent FROM dual UNION ALL
     SELECT 'a' child, null parent FROM dual
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Purpose Of Over And Partition By Keywords In Analytical Functions

Jan 3, 2012

what is the purpose of over and partition by keywords in analytical functions

View 3 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records Using Analytical Function

Dec 22, 2012

how to delete duplicated records from a table without using row_id. I found the duplicated rows from a table using Analytical Function. But i could not use the Analytical function in the where condition.

My table(tab2) Structure is

DEPTNODEPT_NAMEEMPIDSEXID1
107jadf 1F1
40asdf 55
10purchase 2M2
10sales 3M3
30HR 4F4

I found the Duplicate Record by using the query

with a as
(select deptno,dept_name,empid,sex,id1,row_number()over(partition by deptno order by deptno) rnum from tab2)
select * from a where rnum >1

how to delete duplicate record .

View 19 Replies View Related

SQL & PL/SQL :: Average For Month (no Weekend Data In Table)

Jun 10, 2011

I've got values for each workday of month in table. Chief said we don't load values of weekend because of space and time economy. Weekend value is copy of last workday. How do I count average for month in this case? Technical task in Excel worksheet consist of each day of month, so average value is 14 689 262,86. I should get the same result in my query. My example below shows values, 0 is workday (present in table), 1 is weekend (absent in table)..

select 12230427, 0 from dual
union all
select 11960157, 0 from dual
union all
select 12965902, 0 from dual
union all
select 13939736, 0 from dual

[Code]...

View 31 Replies View Related

RAC & Failsafe :: Global Cache Average CR Get Time?

Mar 4, 2012

from last two weeks we are receiving rhe below database alert on our second NODE (RAC ENV).

Global cache Average CR get time " is at .61789
Threshold is set on >.5 (M.Second).

me to figure this out because i really dont know the nature of this alert. what value i can set for the warning threshold.

View 1 Replies View Related

SQL & PL/SQL :: Update Salary In Employees Table With Analytical Functions?

Feb 16, 2013

i have employee table i want to update salary with all employee 5 percent

View 4 Replies View Related

PL/SQL :: Average / Standard Deviation Of Multi-Column Record

Jun 28, 2012

I want to read a record that has 6 columns

RECORD     |Col1     |Col2     |Col3      |Col4     |Average      |Standard Deviation     
0001____|Null_|5___|8___|10__|8.75___|2.986079

With SQL I want to calculate Average and Standard Deviation.

View 4 Replies View Related







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