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.
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.
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]....
I have an upgrade question. My customer wants to minimize downtime and so wants to do a rolling upgrade of crs, asm, dbhomes and databases from 10.2.0.4 to 10.2.0.5.
Customer wants to upgrade server1 during normal business hours- CRS/ASM/DBHOMES, then move databases from server2 to server1 and upgrade during planned downtime, then upgrade server2 during normal business hours -CRS/ASM/DBHOMES.
3 steps, taking 3 days to complete. Is this possible? I know CRS can be done in a rolling upgrade, but what about ASM?Is this doable?
Main Aim : To find all those id's who have taken all the tests within a rolling window of 45 days.
I have a table "MBS_FIRST_DATE" with the following data :This table has the patients who have the test along with the first date..This table is derived such that it has only one record with the first date of the test irrespective of the test.
create table MBS_FIRST_DATE ( medical_record_number VARCHAR2(600), requested_test_name VARCHAR2(39), result_date DATE
[code]..
Process :will be explaining with a patient id : 1) Consider the patient 1001274 from mbs_first_date table. 2) This patient has an date of July 08th 2008 & test SBP from first table. (keep this test an an anchor). 3) For the patient above loop through the all_recs table with test & result date ordered for the patient. (excluding SBP) 4) The first record we have is CHL with 08/05/2009 (May 8th 2009).. 5) Since this record is not within 45 days from SBP date for the patient..we go to the next record of SBP for the patient. 6) The next record for SBP is 11/05/2009 (May 11th 2009) . 7) Consider the CHL date again which is with 08/05/2009 (May 8th 2009).. Since both are within 45 days ..store both the values keeping SBP date as an anchor date as it's the test that's having minimum date from table 1. Even though there is one more CHL date which is within 45 days from SBP we don't care about it. 9) Go to the next test for the same patient which is DBP.. 10) The DBP first date is July 08th 2008.. 11) Since it's not within 45 days from previously stored SBP date (11/05/2009) ignore the record. 12) GO to the next record which is 10/05/2009..as this is within 45 days from SBP & already CHL (stored date) is within 45 days..Grab all the 3 dates as all are within 45 days from anchor date (SBP date).
SO the o/p will be 1001274 SBP 11/05/2009 1001274 CHL 08/05/2009 1001274 DBP 10/05/2009
Code which I wrote :I know some where I am missing the loop
I have a shells script which invokes a SQL file. However even with AUTOCOMMIT OFF and on SQLERROR EXIT ROLLBACK. Sqlplus fails to rollback.
My sql file has 3 lines 3 are correct and 1 is incorrect. For example: INSERT INTO TEST_ROUTING VALUES (24, 'ROUTING'); INSERT INTO TEST_ROUTING VALUES (25, 'ROUTING'); INSERT INTO TEST_ROUTING VALUES (26, 'ROUTING);
Lets say file is called 1.sql
My shell script invokes this SQL as follows: (Where $File1 = 1.sql)
$SQLPLUS_PATH/sqlplus -s /nolog <<-EOF>> ${LOGFILE} connect $DB_USER/$Password1@$Database1 SET AUTOCOMMIT OFF @$File1 WHENEVER SQLERROR EXIT ROLLBACK; EOF [code]......
So tried SET AUTOCOMMIT, tried SQLERROR ROLLBACK and tried few variations.
I have a 2 node RAC environment (11.2.0.3) where each node has there own local Grid_home and RDBMS_home.
I am installing a Rolling Bundle Patch with OPatch in this environment. The installation document says that "The order of patching in RAC install is GRID_HOME, then RDBMS_HOME" so i did the following.
1. stopped all oracle related services on node1 2. set oracle_home=<Grid_home> 3. applied the opatch 4. opatch succeeded on node1 and it says "The node 'NODE2' will be patched next... Is the node ready for patching?
1. Should i shutdown the oracle services in Node2 and continue to patch the Grid_home ? If yes then the DB will be completely down for user access. This defeats the purpose of rolling mode which says there is no downtime. 2. Should i patch the RDBMS_home on node1 , start all the oracle services on node1 , stop the oracle services on node2 and then resume the opatch on node1 which is waiting to patch the Grid_home on node2 ?
SELECT LOC_CD, TO_CHAR(DT,'fmMon RRRR'), SUM(QTY) , GROUPING_ID(LOC_CD) FROM WIP WHERE LOC_CD IN (1,2,3,4) GROUP BY ROLLUP(LOC_CD), TO_CHAR(DT,'fmMon RRRR'),TRUNC(DT,'MM') ORDER BY TRUNC(DT,'MM'),LOC_CD
This query result attached
The red coln is the total I want to place it in row-wise
Date loc_1 loc_2 loc_3 loc_4 Total May 2012 4,554 6,644 11,198 June 2012 4,986 5,838 777 11,601 22,799
I want to replace below multiple call to procedure with a Single query. Currently this proc is getting called multiple times from application.
FUNCTION f_get_shows_info(i_booking_wk_id IN NUMBER, i_screen_id IN NUMBER) RETURN VARCHAR2 IS v_act_shows booking_wk_screen.act_shows%TYPE; v_expected_shows booking_wk_screen.expected_shows%TYPE; v_return VARCHAR2(50); BEGIN SELECT NVL(act_shows, 1), NVL(expected_shows, 1) INTO v_act_shows, v_expected_shows FROM booking_wk_screen WHERE booking_wk_id = i_booking_wk_id [code]...
Is there anyway through which we can achieve this in Oracle 10g.
I need a single select query which converts all the rows into a single value . Below is my requirement :
Create table email_tbl(emailid varchar2(30)); insert into email_tbl('1@y.com'); insert into email_tbl('2@y.com'); insert into email_tbl('3@y.com'); insert into email_tbl('4@y.com');
Now , I need a single select query which gives me the below results.
1@y.com,2@y.com,3@y.com,4@y.com
I have done the above by using a cursors in the pl/sql objects.But want to achieve this with a single sql/query.
INSERT INTO CFL VALUES (11, 1, 'JAN-10', 10); INSERT INTO CFL VALUES (21, 1, 'FEB-10', 20); INSERT INTO CFL VALUES (31, 1, 'MAR-10', 10); and so on (12 records for a year with same quota_id, e.g. here it is 1) [code].......
Also this column values period is dynamic.. it can be for any year.
The select will return values like
Select per_id, PERIOD, amount from cfl where quota_id = 1
Basically 12 rows will be the output: per_id period amount 1 JAN-10 10 1 FEB-10 20 1 MAR-10 10 ..............and so on
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 )
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 .
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
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?
,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:
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.
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)
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]....
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
