insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10);
insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10);
insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10);
insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10);
insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);
Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';
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?
I am trying to replace a date range in a query. Don't understand why it's not working, below is the dummy query for the same:
/* v_Employee:= 'select emp_name from employee_table where Join_Year=~Replace1~ and Release_Month <=~Replace2~'; v_Replace1:=2013; v_Replace2:=7; pos := 1; WHILE (INSTR(v_Employee, '~', 1, pos) <> 0) LOOP IF (pos = 1) THEN
[code]....
Since actual code is like CLOB type, so I have not provided the same. Here after executing this query i found that v_Employee query is getting replaced by v_Replace1 at both of his position i.e "select emp_name from employee_table where Join_Year=2013 and Release_Month <=2013"
But my actual result should: "select emp_name from employee_table where Join_Year=2013 and Release_Month <=7"
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 query that seems to repeatedly call an index scan on a table for reasons I'm not sure about. Why it would be doing the index scan on totaldwellingarea in the dimensions table (DIMEN_PID_TDWELLAREA) repeatedly? This only seems to happen when I put on the range clause d.totaldwellingarea between scr.lowvalue and scr.highvalue.
I am using Oracle version 9.2.0.3.
select d.propertyid,d.totaldwellingarea, e.size_, scr.size_ from eqid e, dimensions d, brt_eval.size_code_ranges scr where e.style not in ('1','A','G','L') and e.size_ = '0' and d.propertyid = e.propertyid and e.style = scr.style and d.totaldwellingarea between scr.lowvalue and scr.highvalue;
building SQL query to get the result as shown below.
Create Table Temp
CREATE TABLE TEMP ( CASEID NUMBER, SATUS VARCHAR2(1 BYTE), TRANS_DATE DATE ) Insert data
[Code]...
I want to build a query which should give output as shown below. Basically i want to select those rows which having minimum trans_date for a given CASEID & Status.
I need to find out that assume i have a table having 2 column
Num Name 1 Adam 2 Akanksha 1 barren 2 bosli 3 Benergee 4 Bhawna 3 anjani
I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets
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.
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))
We have data as below in the table. I need the to display the records in the order based on number of NULL values and position for each record.
provide a simple query using case in ORDER BY clause.
ID CLASS NAME DIST_ID DIST_NAME 0 NULL KIRAN 0 AP 0 C1213 NULL 0 AP 0 NULL NULL 0 AP NULL C1234 NULL 0 AP 0 NULL NULL 0 AP NULL NULL NULL NULL NULL 0 C123 RAJESH 0 AP NULL C123 RAVI NULL AP
We have to give the rank based on NULL values and NULL value column position.Let us assume column positions as
1 2 3 4 5 ID CLASS NAME DIST_ID DIST_NAME
for the following three records number of NULL values are same. but positions are different.
0 NULL NULL 0 AP NULL C1234 NULL 0 AP 0 NULL NULL 0 AP NULL C123 RAVI NULL AP
Based on the column positions the ranks as 2*2+3*3=13 1*1+3*3=10 2*2+3*3=13 1*1+4*4=17
Which is having high rank (greatest number) that record should come last . The record which is having all values that should come first. The record which is having all NULL values should come last. The out put I want as
ID CLASS NAME DIST_ID DIST_NAME 0 C123 RAJESH 0 AP 0 NULL KIRAN 0 AP 0 C1213 NULL 0 AP NULL C1234 NULL 0 AP 0 NULL NULL 0 AP 0 NULL NULL 0 AP NULL C123 RAVI NULL AP NULL NULL NULL NULL NULL
We have a requirement where we need to pay allowance for the employees based on their number of working days. Say for example if an employee worked from 03/Mar/2012 to 05/Apr/2012.
We have a fixed value for per month 300 Dirhams. But the Number of Days on March s 31 and Number of days in April is 30. So per day allowance for March day would be 300/31 and April would be 300/30.
We are looking for logic opr query which calculates first eh number of days in each month ( across months) and then calculate as below
Number of Working days in March is 31 - 3 + 1 = 29
Allowance A1 = (300 * 29 )/31
Number of Working days in April is 5 ( this also needs to find logical I am guess ) Allowance A2 = (300 * 5 )/30
Then A1 + A2.
The A(n) would be the total allowance where provided the number of month across.
I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?
I am trying to run the following queries in my database.
Q-1)select * from item where trunc(approval_date)='21-MAY-13' When Q-1 is executed it returned row that has approval_date of 21-MAY-13 from database. Q-2)select * from item where approval_date=to_date('21-MAY-13') When Q-2 is executed it says no rows selected.
This data needs to be represented in a SQL report as below. The Date Range in the below i.e., Starts with FEB, by checking against the sysdate.. That is from sysdate it will display the Quarter data for 4 months as below.
Currently I am using a procedural logic to populate data into a different table in the above format. Is there any method to do with a single SQL using PIVOT. Below given is the table structure and Insert scripts.
I want my user to be restricted for entering duplicate time within two times.
create table asd(dt_frm date,dt_to date);
insert into asd VALUES(to_date('01-04-2012 08:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 10:00','dd-mm-yyyy hh24:mi')); insert into asd VALUES(to_date('01-04-2012 09:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 11:00','dd-mm-yyyy hh24:mi'));
now in the second insertion I want to alert the entry user that 9am already falls in the saved record which is 8am to 10am and so that this record can't be saved.
Other than the obvious to me, where interval partitioning creates partitions as needed. Is there any performance benefit from using interval partitions vs date range partitions.
One draw back for me is that developers do access the partition name in some of their queries, so if I use date range partitioning this will not break their code. I could not find a way to assign a name to a partition when using intervals, is this always system generated or can this be over-ridden.
I am running Oracle 11.1.0.7 soon to be running on 11.2.0.0
Our term (strm) is dictated by the term_begin_dt and term_end_dt dates but I want to keep selecting that term until 1 week before the next term opens and then switch to that term.
Basically, I don't want any gaps between a term.
Output:
select strm when sysdate is between term_begin_dt and term_end_dt (strm would equal 3943) select strm until 1 week before the start of the next term (4027) (strm would equal 3943) select strm when 1 week before term_begin_dt (strm would equal 4027)
Repeat for the next term and so on 12/16/2011 select strm from term where trunc(sysdate) between trunc(term_begin_dt) and trunc(term_end_dt)
Output: 3943 12/17/2011 - 01/01/2012
select strm from term where ?
Output: 3943
01/02/2012 - 5/4/2012 select strm from term where ?
Output: 4027
Repeat.
Test Case:
CREATE TABLE TERM ( STRM VARCHAR2(4 BYTE), DESCR VARCHAR2(20 BYTE), TERM_BEGIN_DT DATE, TERM_END_DT DATE )
Insert into TERM (STRM, DESCR, TERM_BEGIN_DT, TERM_END_DT) Values ('3943', '2011 Fall Semester', TO_DATE('08/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/16/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into TERM [code].....
