PL/SQL :: Query To Find First And Last Call Made By Selected Number For Date Range
Apr 27, 2013
create table call(id number(10) primary key,mobile_no number(10), other_no number(10), call_type varchar2(10),call_date_time date, duration number(10));
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';
Result :
date ,mobile_no , other_no, call_type, duration
24/apr/13 , 9818764535,9899875643,'IN',10
24/apr/13 ,9818764535,9215468734,'IN',10
[Code]....
View 5 Replies
ADVERTISEMENT
Jul 12, 2012
I have a table with two columns Column1 and column2
Like such
create table testTable (column1 number(15), column2 number(15));
insert into testTable values (1,5);I need to find the numbers between column 1 and column 2 including the column 1's number and column's 2 number.
so my answer set should be
1,2,3,4,5
View 4 Replies
View Related
Apr 5, 2011
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?
View 2 Replies
View Related
Aug 14, 2013
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"
View 5 Replies
View Related
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
Dec 26, 2012
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
View 1 Replies
View Related
Oct 16, 2012
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;
CREATE
TABLE "BRT_ADMIN"."EQID"
(
"PROPERTYID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"EQID" CHAR(10 BYTE),
"ZONE_" CHAR(1 BYTE),
"AREA" CHAR(1 BYTE),
[code].....
View 3 Replies
View Related
Nov 5, 2010
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.
OUTPUT:
CASEIDSATUSTRANS_DATE
100A11/02/2010
100B11/07/2010
100A11/12/2010
200A11/02/2010
200B11/07/2010
View 6 Replies
View Related
Apr 20, 2013
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
View 6 Replies
View Related
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
Aug 18, 2013
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
View 15 Replies
View Related
Oct 1, 2012
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.
View 10 Replies
View Related
May 17, 2011
is there any query to find number of primary keys present in a table.
View 13 Replies
View Related
Aug 16, 2012
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 ?
STARTDT_ ENDDT_
01/08/2012 01/08/2012
01/06/2012 31/12/2012
01/08/2012 01/08/2012
01/09/2012 01/09/2012
01/09/2012 31/12/2012
01/07/2012 01/07/2012->not retrieved out
01/08/2012 01/08/2012
01/01/2012 31/12/2012
View 6 Replies
View Related
Aug 8, 2013
select to_date('28-FEB-2013') - TO_DATE('01-FEB-2013') FROM DUAL
gives me 27 days, what should I do to get 28 ? meaning include both the start and end dates in the range ?
View 3 Replies
View Related
Aug 3, 2011
Can we select table's rows of 5 to 15, like
select * from emp
where rowid <=5;
It is through an error when i used the below method.
select * from emp
where rowid >=5 and rowid <=15;
View 4 Replies
View Related
Jun 18, 2013
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.
difference or if any links related to this.
View 4 Replies
View Related
Dec 16, 2011
I am having a requirement like below,
Scene 1:
If duplicate records found for SSN,BWE with SAME DATE_CREATED than take the record with HIGHEST DATE_MODIFIED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 02-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
Scene 2:
If duplicate records found for SSN,BWE with different DATE_CREATED than take the record with HIGHEST DATE_CREATED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
How to achieve this requirement. My source data has 25 million of records like this.
View 2 Replies
View Related
Mar 19, 2013
I have 3 types list items:
1) :1 Day -> 31
2) Month: 1 -> 12
3) Year :1990 -> 2013
I want to check that the user has selected a valid date .. if not an error message will be displayed, for example: 31-02-2013: will be rejected
View 9 Replies
View Related
Sep 20, 2011
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 .
View 5 Replies
View Related
Jul 22, 2011
this is my table
create table sequence ( id int not null primary key);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
View 1 Replies
View Related
Jul 22, 2011
this is my table
create table sequence (
id int not null primary key
);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
View 7 Replies
View Related
Apr 21, 2013
There is a simple table prod having a column crlf.i did the following
Quote:
SQL> update prod set crlf=null;
716 rows updated.
SQL> commit;
Commit complete.
SQL> select * from prod where crlf=null;
no rows selected
why NO row selected against last query?
View 3 Replies
View Related
Feb 4, 2013
I have a cost data for effective date range as below.
MODEL_NOTIER_COSTEFFECTIVE_START_DATEEFFECTIVE_END_DATE
I3 3770 265 2/3/2013 3/31/2013
I3 3770 269 4/1/2013 5/4/2013
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.
MODEL NOFEB FY13MAR FY13APR FY13MAY FY13
I3 3770 265 265 269 269
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.
CREATE TABLE ITEM_TAG(MODEL_NO VARCHAR2(50), TIER_COST NUMBER(13,4), EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE );
Insert statements
Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
(MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('I3 3770', 265, TO_DATE('02/03/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/31/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
(MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('I3 3770', 269, TO_DATE('04/01/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
View 10 Replies
View Related
May 7, 2012
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.
View 8 Replies
View Related
Apr 21, 2011
i have a requirement To partition a Table byRange partition with a Number column. but the issue is the range Must be in a Date datatype ,
For example
partition by range (date_key)
( PARTITION DEF VALUES LESS THAN ('01-SEP-10'))
the date_key column has values of date in number format. like "20101014"
View 2 Replies
View Related
Jan 26, 2010
i am using oracle developer 6i report builder i required this type of query
example
if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;
return (TRUE);
end;
but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.
View 34 Replies
View Related
Dec 16, 2010
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
View 6 Replies
View Related
Jul 14, 2010
How to display date every monday within date range
DEsc start date end date
XXXX 1/2/2010 31/10/2010
output
date every monday
View 25 Replies
View Related
Dec 16, 2011
Problem:
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].....
View 2 Replies
View Related
