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


ADVERTISEMENT

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

Hour Must Be Between 0 And 23

Jan 2, 2008

follow SQL query would lead to an ORA 01850: hour must be between 0 and 23 error? I'm getting this error don't not sure if this query is causing it.

select
mlh_date_begin,
mat_desc,
mlh_alert_text,
mat_code
from s_mlh_mem_alert_history,
s_mat_member_alert_type
where mlh_mem_uid=f_mem_uid
and mlh_date_begin <= trunc(sysdate)

[Code]...

View 3 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

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

ORA-01850 - Hour Must Be Between 0 And 23?

Oct 4, 2007

I am receiving the following Oracle ERROR:

HIPDAO -> getXAlerts():SQLExceptionORA-01850: hour must be between 0 and 23

HERE is the code where it's occuring:

public List xMethod(
int memUID)
throws DAOException {

[Code].....

The error doesn't make sense because I don't think I'm setting a date anywhere in the code.

View 3 Replies View Related

PL/SQL :: Every One Hour Update Of Xml

Apr 15, 2013

After prepared this xml . I make some edit(like add two record) in my emp and dept table i need every one hour update of my xml . How i will do this one ?

select xmlelement("AllDepartments",
xmlagg(
xmlelement("Department", xmlattributes(d.deptno as deptno, d.dname as dname),
xmlagg(
[code].........

View 3 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

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

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

Extract Hour From A Date?

Mar 29, 2004

how to extract hour part from a date?

suppose my date is like

29-mar-2004 09:20:34

i wanna get only hour from the above date-

View 3 Replies View Related

Redo Generated In Last 2 Hour

Feb 7, 2013

Is there any way to get the amount of redo generated in last 2 hour. which has below chareteistic

1. redo generated by currently connected session from last 2 hour.
2. redo generated by session disconnected during last 2 hour.

total_redo = disconnected sessoin during last 2 hour + connected session generating redo during last 2 hour.

View 7 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

PL/SQL :: How To Convert Minutes To Hour And Minute

Oct 4, 2012

i want to convert minutes that is 90 want to convert it in hours and minute that format should come in 01:30 format.

can i get in this format?

View 12 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

Number Of Waits Reaches To 100 In 8-hour Snapshot

Oct 8, 2013

in the awr report I saw some segments in Segments by ITL Waits section. Number of waits reaches to 100 in 8-hour snapshot. Is it small/big number? Should I consider increase INITRANS or there is nothing to be worried because value 80-100 are not too high?

View 3 Replies View Related

SQL & PL/SQL :: How To Manipulate Greater Than 24 Hour Clock In Oracle

Jul 14, 2011

I have an Oracle application that deals with a 29 hour clock. so the days begin at 5am instead of midnight. I am trying to get data from a table where time is up to certain number of hours, but when it reached the 00 clock my whole query returns no records, even though there is plenty of records before the midnight hour, because the data is saved as lets say 2530 instead of 0130

I am using this

SELECT *
FROM WORK.WORK_UNIT
WHERE (to_char (current_garage) = :entry_blk.curr_garage
OR 'all' = :entry_blk.curr_garage)
AND route >= 0 AND run >= 0
AND ((package_id > 0 and work_code = 99) OR
(package_id is null and work_code = 1))
and Nvl (Emp_No, 0) = 0
AND work_date = :entry_blk.p_work_date
AND on_time <= TO_CHAR(SYSDATE + :Entry_Blk.up_to_hour/24, 'HH24MI')

since oracle deals with a 24hour clock, my code doesn't seem to work if there is data for after midnight (00 hour). I am using :Entry_Blk.up_to_hour/24 to determine up to how many hours I want to see data, i.e.

on_time <= TO_CHAR(SYSDATE + 2/24, 'HH24MI')
will give me data up to 2 hours

View 9 Replies View Related

SQL & PL/SQL :: Time Range Include A Specific Hour?

Aug 11, 2010

I'm looking for a way to say if a time range includes a specific hour.

If I was looking to work out if 01:00 to 04:00 includes 02:00, then this works:

CASE WHEN TO_DATE('02:00','HH24:MI') BETWEEN TO_DATE('01:00','HH24:MI') AND TO_DATE('04:00','HH24:MI') THEN 1 ELSE 0 END

But, if the time go over two days (with midnight in between), it doesn't work:

CASE WHEN TO_DATE('02:00','HH24:MI') BETWEEN TO_DATE('18:00','HH24:MI') AND TO_DATE('04:00','HH24:MI') THEN 1 ELSE 0 END

View 3 Replies View Related

Replication :: Daily Refresh At Particular Hour / Minute?

Apr 28, 2009

However, I need to refresh the group manually sometimes. Therefore, I cannot set the interval as sysdate+1.have tried setting the interval as follows. However, they are not correct

Quote:trunc(sysdate+1) +4/24
The next interval will show 9:25:20pm.

Quote:trunc(sysdate+1) + interval '4' hour
Incorrect syntax

View 7 Replies View Related

Generating ADDM Reports Automatically Every Hour?

Jun 9, 2012

I have been trying to develop a script for generating ADDM Reports every hour and save it in a directory on the server. I was able to develop a script to run the AWR Reports for every hour and save them in a directory, but I ran into troubled waters in the ADDM script.

Database Version : Oracle Database Enterprise Edition 10.2.0.3
OS : IBM AIX 5.3

I'm trying to debug the script below to generate ADDM reports on a per hour basis and save them in a folder as well as mail than to a particular entity.

########################################################################################
# Set up Oracle environment variables...
#------------------------------------------------------------------------------
export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/oracle/app/oracle/product/10.2.0/bin:/usr/bin/X11:/sbin:.:/oracle/app/oracle/product/10.2.0/bin
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0
export ORACLE_SID=sarcotest2.ora
export ORAENV_ASK=NO

[Code].....

Initially, the script did return me an ADDM report, but the problem was that it generated the report for only a set of two snapshots (eg: 410110 and 410111). later when the snapshots advanced further, it still generated the same report for the same snapshots.

Now, it seems it have made several changes to the script in the declare and dbms_advisor section and the reports are not being generated anyway.

One problem I am facing is

ORA-13605: The specified task or object ADDM_UAT does not exist for the current user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2043
ORA-06512: at "SYS.DBMS_ADVISOR", line 560
ORA-06512: at line 1

It seems the task is not being generated at all in my user's schema.

View 1 Replies View Related

Creating AWR Report Including Current Hour?

Jun 4, 2013

In an 11g database is it possible to create an awr report including the current hour or do I always have to wait till the full hour?

View 1 Replies View Related

PL/SQL :: Query Returning Zero Results For Yesterdays Same Hour

Apr 22, 2013

I have the following query:

SELECT
d_dtm,
BTS_ID,
CASE WHEN D_DTM = (D_DTM-24/24)
THEN sum(V_ATT_CNT)

[Code]....

But it is not returning any results because of the "having" clause. I know it should return results because all I want it to do is in one column have the V_ATT for the current time period, and in the 2nd column, have the V_ATT 24 hours ago. I've checked the data and I should get results back but can't seem to figure out why this is not working...

View 3 Replies View Related

Reports & Discoverer :: Recover From Hour Glass Symbol

Mar 21, 2011

I have logged into Oracle and Discoverer several times and every time when I'm running a report in Discoverer, it quits responding. This is after I already have the data, and I'm scrolling to the bottom of the data to get the next 10,000 rows.

I just get the "hour glass symbol" and then i basically have to shut down and restart my computer.how to recover from these type of hour glass symbol.

View 2 Replies View Related

Server Administration :: Sun Solaris - Load Average Too High At Specific Days

Oct 16, 2010

My problem is only on Some Saturday's my Oracle server's Load average goes high (more than 300 ).

-- oracle version 11.2.0.1.0
-- runs on Sun solaris 10
-- Sun fire V 440
-- Sun storEdge 3315 connected to the server.

Same setup is working find with higher volumes without any problem but only on saturday's, that too not on all saturdays, some specific saturdays the load average goes high.

At the time nothing will be processed from the application side and the cpu utilisation goes high upto 95 %.

I am sending necessary information as follows.

vmstat 6 10
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s3 s4 in sy cs us sy id
3 0 0 28660024 6513408 285 212 2044 2 2 0 0 0 17 0 42 830 3469 1380 22 5 74
125 0 0 29027480 6156256 2 6 34 0 0 0 0 0 5 0 10 791 53770 30278 83 17 0
125 0 0 29027680 6157496 29 140 21 1 1 0 0 0 5 0 9 786 52756 30309 83 17 0
116 0 0 29031600 6159896 24 125 0 0 0 0 0 7 3 0 5 819 54081 31069 83 17 0

[code]....

View 1 Replies View Related







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