I have a table as below:
Year Month Value
2011 01 15
2011 02 26
2011 03 34
[code]...
The value is an aggregate Year to Date Figure And I was wondering what the best method of splitting this data out into a Monthly Figure so that it would look like below:
Year Month Mth Value
2011 01 15
2011 02 11
2011 03 8
2011 04 9
[code]...
Using Oracle 11g...We have a table in our database of data with the following information:
MASTER_RECORD,
MEMBER_RECORD,
BUSINESS_UNIT,
GENDER,
DOB (date),
age [at time of month_record],
MONTH_RECORD (date) [31-MON-YEAR for recorded active month]
The table has ~55 million records. Existing index is only on MASTER_RECORD.There is now a need to create a view which is an aggregate count of member records, grouped by business_unit,gender, age per year. eg:
business_unit, gender, age, month_record, num_of_members -> for every combination
unit5, F, 25, 31-JUN-2011, 622
unit3, M, 18, 31-MAY-2011, 573
The view can be created now, but, is not fast enough to be reasonably considered a view. This table is re-created every month from a procedure, so there is flexibility on how it is created. Use interval partitioning by year( something I have not experienced using), create an index on the month_record,then create view.
I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.
View 3 Replies View RelatedI got a table with a date-typed column called: "Birth_date", and I wanna write a function that retrieves all the records that "got a birthday" this week.
in order to check weather a record got a birthday this week I need to check only the day & month of "Birth_date" that BETWEEN (sysdate-7) AND (sysdate),
but I don't know how..
I have a sql like this
select inv_dtime where inv_dtime between trunc(sysdate,'YYYY') and last_day(trunc(sysdate,'YYYY')) from temp;
I want to have the start date of the year and end date of the year in my condition. like between 01-JAN-2012 AND 31-DEC-2012. I tried the above but it doesn't come.
How to get a date using Year (say 2009), Week (35) and Weekday (5). I have read only permission to database, so I can not create a function.
View 8 Replies View RelatedI have a table and below are the rows:
create table employee_rev(employee_id number, month_name varchar2(10), month_end date, financial_year date, revenue_amt number)
Begin
insert into employee_rev values(111, 'DEC-09', '31-DEC-2009', '01-APR-2009', 1300);
insert into employee_rev values(111, 'JAN-10', '31-JAN-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'FEB-10', '28-FEB-2010', '01-APR-2009', 800);
insert into employee_rev values(111, 'MAR-10', '31-MAR-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'APR-10', '30-APR-2010', '01-APR-2010', 1000);
insert into employee_rev values(111, 'MAY-10', '31-MAY-2010', '01-APR-2010', 1100);
insert into employee_rev values(111, 'JUN-10', '30-JUN-2010', '01-APR-2010', 2100);
End;
I also need a YTD (Year to Date) field at the last which is sum of current month revenue_amt and sum(revenue_amt) for previous months for a particular financial_year_date.
The output should be:
Person_ID Month End Date Year Date Rev YTD
111 Dec-09 31-Dec-09 1-Apr-09 1300 1300
111 Jan-10 31-Jan-10 1-Apr-09 1000 2300
111 Feb-10 28-Feb-10 1-Apr-09 800 3100
111 Mar-10 31-Mar-10 1-Apr-09 1000 4100
111 Apr-10 30-Apr-10 1-Apr-10 1000 1000--change in financial year
111 May-10 31-May-10 1-Apr-10 1100 2100
111 Jun-10 30-Jun-10 1-Apr-10 2100 4200
I guess this should be achievable using some analytical functions, but I am unable to get the desired output.
IF THE USER ENTER parameter for date(yymm) in a report as 201301
select vndr#,sum(net_sales_value) from mnthly_sales_value where vndr# = 111 and yymm = :yymm group by vndr#;
but I need result of 3 months, but in my table data is stored for one month so how to get 3 months then again 3 months so on for Q1,Q2,Q3,Q4
yymm between 201301 and 201303 and
yymm between 201304 and 201306 and
yymm between 201307 and 201309 and
yymm between 201310 and 201312
in the same query
what i have to do for this
I've done this once before, but can't seem to find the sql.
How can I sort by month and year on a column called ex: TEST_dATE
JAN 2007
FEB 2007
APR 2008
SEP 2009
OCT 2009
FEB 2010
JUN 2010
i want to find out difference between two dates in day-month-year. How can i do it?
For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'
basically I have to insert in a date field a string that is a year(four characters).
The problem is that I have to convert a string in date obtaining just a year. How can i do this?
select b.penjara_id, p.penj_lokasi, a.no_daftar, b.episod, b.nama1,to_char(a.trkh_mula_prl,'dd/mm/yyyy') as trkh_mula_prl, to_char(bulan_proses,'mm/yyyy') as bulan_proses,
b.epd, b.lpd
from prl_daftar_proses a, senarai_pesalah b, penjara p
where a.no_daftar=b.no_daftar
and a.episod=b.episod
and b.penjara_id = p.penjara_id
and a.setuju_jplp is null
and a.bulan_proses between to_date(:FROM,'dd/mm/yyyy') and to_date(:TO,'dd/mm/yyyy')
order by b.penjara_id, a.bulan_proses,a.no_daftarHi,
how i can insert only month and year from the value that have full date in the database?
for example,the date is 09/18/2012, but i just want to insert 09/2012 as parameter. If i want to insert only one parameter, i can do that..But i have problem when I want to insert two parameters..
Getting problem with below query.
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error starting at line 1 in command:
select to_date('30-DEC-00','DD-MON-YYYY') from dual
Error report:
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
i have a requirement to create a query which gets the same month from the previous year of the selected date .
Example : i select SEP-2011 ( as sysdate )
it will return the SEP-2010 as well .
here is the code which works fine with sysdate, dual :
SELECT
TO_CHAR(ADD_MONTHS(SYSDATE,-12),'MON-YYYY') TMLY, TO_CHAR(SYSDATE,'MON-YYYY') TMTY FROM DUAL;
in my schema table Ive got a filed named PERIOD_NAME (varchar2) , which holds date values in ( Mon-YYYY ) format (e.g Sep-2011)
am unable to apply the above code on it , i guess its the data type pf the field .
I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.
View 5 Replies View RelatedI given the table name,column name,datatype and sample record in the table. I have given the sample record for 01-jan-2008 to 8-Jan-2008, but in the real thing it will be for 30 years.
My Requirement:
For each class_no (202,203..), I need the missing date excluding weekends (sat, sun), I have provided the sample output below.
Table Name : ABC
Column Name : Class_no Data Type : Number
Column Name : Class_DateData Type : Date
Sample Record in the Table :
Class_noClass_Date
202 1-Jan-08
202 2-Jan-08
202 7-Jan-08
202 8-Jan-08
203 1-Jan-08
203 2-Jan-08
203 3-Jan-08
203 7-Jan-08
203 8-Jan-08
OUTPUT:
Class_noClass_Date
202 3-Jan-08
202 4-Jan-08
203 4-Jan-08
How do I make a select statement which will add up numbers from different columns or add up sums from different columns?
View 1 Replies View RelatedI need to aggregate the data based the two dates criteria.
I have two tables
1. Table1
Number Date1 Date2
1 10-Jun-2010 30-Jun-2010
2 10-Feb-2010 30-Feb-2010
----------------------------------
2. Table2
Number Date Revenue
1 11-Jun-2010 100
1 09-Jun-2010 100
1 12-Jun-2010 100
2 11-Feb-2010 100
2 12-Feb-2010 100
2 13-Feb-2010 100
......................
......................
So on
Output:
Number Revenue Date2
1 200 30-Jun-2010
2 300 30-Feb-2010
I have two table and I want to merge them
TERMS_TABLE
ID | TERMS
309 | 'hardware'
309 | 'software'
309 | 'computer'
TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....'
Now I want to add TERMS column of TERMS_TABLE to terms column of TFIDF_TABLE but If TFIDF_TABLE already contains TERMS of TERMS_TABLE then I should not insert this term to the NEW_TFIDF_TABLE , like that
result should be:
NEW_TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....,hardware,software'
How can I do that ?
I have performance problem with 7 queries involving groupby clauses in OLAP database.These are queries triggered during siebel DAC run
kumar[size="4"][/size][color="#0000FF"][/color]kumardba
I am trying to Show all Customers (custId, last name, first name) who bought cars at the Arctic AND Pacific branches and Order it by CustId.
These are the tables:
VIN CARTYP BRNAME BUYERID SALEDATE
------- ------ ---------- ---------- -----------
1JJ0001 CT9015 Arctic
1NN0001 CT9014 Arctic 10010 18-mar-2012
1PP0001 CT7013 Arctic 10007 25-sep-2012
1QQ0001 CT7012 Atlantic 10014 22-dec-2009
1RR0001 CT7011 College 10013 30-sep-2012
2JJ0001 CT9015 Pacific
2NX0001 CT9014 Pacific
2PP0001 CT7013 Arctic
2QQ0001 CT7012 Arctic
[Code]...
CUSTID LNAME FNAME BALANCE_DUE
---------- ------------ ------- -----------
10001 Disney Daisy 9000.5
10010 Dupont Caryn 40000
10006 Gordon Diane 75000.5
10005 Gordon Tracy 3000.25
10013 Gross Henry 50000
10015 Hilton Kate 40000
10011 Lea Diane 12000
10003 Lopez Athena
[Code]...
No matter how I write the query, I get the message that no rows have been selected.
I have one table emp with columns id , name and dept. i want query to get the list like this........
ID name did
1 ajay 10,30,40,60
2 sree 10,30,70
I have a query more or less like this:
SELECT field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY field1;
For field1 I want to get a count, but if field1 is like 'ABC%' then I want to combine all of those.
So if I have the following:
ABC1 | 5
ABC2 | 10
XYZ1 | 3
I want results like this:
ABC | 15
XYZ1 | 3
I've tried using some case statements like
SELECT CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END AS field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END;
but this just gives me
ABC | 5
ABC | 10
XYZ1 | 3
How can I combine record 1 and 2 from the last record set example above?
I intend to get for every client the start date and end of a contiguous range of days. Example for the same client have two records, in the first goes from day 1 to day 5 and the second from day 3 to day 9, i intend to get a record for this client where indicated that the start date is on day 1 and ending on Day 9.
SELECT 123 as CLI_ID, TO_DATE('20100101', 'YYYYMMDD') as DT_START, TO_DATE('20100105', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100208', 'YYYYMMDD') as DT_START, TO_DATE('20100321', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100219', 'YYYYMMDD') as DT_START, TO_DATE('20100228', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100227', 'YYYYMMDD') as DT_START, TO_DATE('20100405', 'YYYYMMDD') as DT_END FROM DUAL UNION
SELECT 123 as CLI_ID, TO_DATE('20100901', 'YYYYMMDD') as DT_START, TO_DATE('20101013', 'YYYYMMDD') as DT_END FROM DUAL
Expected :
CLI_ID,DT_START,DT_END
123,01/01/2010,05/01/2010
123,08/02/2010,05/04/2010
123,01/09/2010,13/10/2010
Do we have analytic function equivalent of following?
select object_type,owner from dba_objects group by object_type,owner ;
I am trying to get a unique srno for a combination of a 2 fields - here object_type and owner
OWNEROBJECT_TYPESRNO
SYSVIEW1
SYSTABLE2
SYSPROCEDURE3
SYSTEMVIEW4
SYSTEMTABLE5
SYSTEMFUNCTION6
SYSTEMPROCEDURE7
SCOTTTABLE8
SCOTTVIEW9
.......................
also how can I get the SRNO?
I can' use sequence in the group by function and if I get equivalent analytic for above group by even then I can't write row_number as the order by gives detail record
I don't want to wrap this select inside other select
I am getting most of the time 'out of process memory' when i run the 'ord' procedure.here i am providing the tables and the procedure.
i have 2 table orders which holds distinct values, and departments table has ordvalue column holds long string of values for a particular record from orders table. for example if the values in orders table as follows:
ord_code ord_level ordid ordstatus ord_num user utimestamp
SR11 1 2 A 101 V SYSDATE
SR11 1 2 A 102 V SYSDATE
SR11 1 2 A 103 V SYSDATE
SR11 1 2 A 104 V SYSDATE
SR11 1 2 A 105 V SYSDATE
SR11 1 2 A 106 V SYSDATE
SR11 1 1 B 101 R SYSDATE
SR11 1 1 B 102 R SYSDATE
SR11 1 1 B 103 R SYSDATE
SR11 1 1 B 104 R SYSDATE
SR11 1 1 B 105 R SYSDATE
SR11 1 1 B 106 R SYSDATE
ETC...
AT TABLE departments data will be like this
ord_code ord_level ordid ordstatus ord_num user utimestamp
SR11 1 2 A 101,102,103,104,105,106 V sysdate
SR11 1 2 B 101,102,103,104,105,106 R sysdate
from the get_ord procedure the data aggreates using the xmlelement and gets as a long string value into departments table of ord_num column.
CREATE TABLE test.orders
(
ord_CODE VARCHAR2(4 BYTE) NOT NULL,
ord_LEVEL VARCHAR2(1 BYTE) NOT NULL,
ordID NUMBER(5) NOT NULL,
ordstatus VARCHAR2(1 BYTE) NOT NULL,
ord_num NUMBER(3) not null,
user VARCHAR2(8 BYTE),
UTIMESTAMP DATE
[code]...
I'm calculating a Z score based on some simple numerical data thus:
create table t (id number, val number);
insert into t values(1, 1795);
insert into t values(2, 1753);
insert into t values(3, 1743);
insert into t values(4, 1876);
insert into t values(5, 1848);
[Code] .....
the logic is quite simple - calculate a moving average over the previous 12 rows, and a stdev over the same window. Then subtract the prior row's moving average from the current value, and divide by the prior row's stdev.
The issue is I want to expose this logic in a BI tool (OBI EE v10g), meaning I can't use the nested analytic functions. How to achieve this logic in a single analytic pass? The sql above took about 2 minutes to write this morning, then I've spent all day looking at user-defined aggregate functions, but haven't even been able to get the first step, the moving average, working. I can understand that I can probably create an udaf to replicate the avg(val) over (order by id ROWS BETWEEN 11 PRECEDING AND 0 FOLLOWING) functionality, but I can't see how to bundle the logic for the other three steps in the calculation into this.
From what I've read, the ODCIAggregateMerge should allow me to combine different threads that can return the different values I need for the current row calculation. Is this correct?
The only example udafs I can find are either not relevant (STRAGG) or very simple (ie don't appear to invoke multiple passes). I've also had a look at the COLLECT function, but again I can't see a way to use this.
I'm trying to build up a materialized view with aggregate and FAST REFRESH for INSERT and UPDATE, DELETE with no success. But the web doesn't deny it ?
--build up needed schema objects
CREATE TABLE empt
( ename VARCHAR2(20),
empno INTEGER,
[Code]....
i have table with following data.
yearquartersales Revenue
2004Q145678
2004Q287456
2004Q356732
2004Q4120986
2005Q12398
2005Q23900
2005Q36522
2005Q42763
I want the output in following way.tell me the select query for this
yearquarterSales Revenue
2004 Q145678
Q287456
Q356732
Q4120986
2004 total Sales310852
2005 Q12398
[code]....
What is the best practices to maintain aggregate columns? Suppose I have the following 2 tables:
DROP TABLE TEST.ORDERS_DET_T;
DROP TABLE TEST.ORDERS_T;
CREATE TABLE TEST.ORDERS_T (
ID NUMBER NOT NULL PRIMARY KEY,
ORDER_CODE VARCHAR2(100) NOT NULL,
[code]....
I want the following test script to act int the way, described in comments. Basically this means that the sum of TEST.ORDERS_DET_T.ORDER_DET_SUMA must be equal to TEST.ORDERS_T.ORDER_SUMA after transaction commits.
INSERT INTO TEST.ORDERS_T(ID, ORDER_CODE, ORDER_SUMA) VALUES(1,'FRUITS',100);
INSERT INTO TEST.ORDERS_DET_T(ID, ORDERS_T_ID, ORDER_DET_CODE, ORDER_DET_SUMA) VALUES(2,1,'APPLES',40);
INSERT INTO TEST.ORDERS_DET_T(ID, ORDERS_T_ID, ORDER_DET_CODE, ORDER_DET_SUMA) VALUES(3,1,'PEAT',60);
COMMIT; --SHOULD BE OK, 40+60=100
[code]....
P.S. Creating views based on ORDERS_T and ORDERS_DET_T are not an option, if the user would still be allowed to modify data in tables (as in test scenarios). This is because of current business situation, where the client has 2 teams (outsourcing for back-office solution and insourcing for web solution) that are accessing/modifying the data in the same tables.