SQL & PL/SQL :: Query To Find MIN Date

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


ADVERTISEMENT

SQL & PL/SQL :: Analytical Query To Find First And Last Value In Date?

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

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

Visual Studio :: Set Query Window Default Date Format To Full Date?

Oct 24, 2013

When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from  TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss')  it only returns  'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?

View 0 Replies View Related

SQL & PL/SQL :: Date Overlap And Merge Date Query

May 11, 2011

Lets say the contents of the table are like this -

key dte_start(number) dte_end(number)
1 20010101 20011231
1 20020101 20081231
1 20030601 20071231
1 20100101 20101231
2 20090101 20091231
2 20090401 20101231
2 20101231 20111231
2 20140101 20141231
3 20080101 20091231
3 20070101 20081231
3 20100101 20101231
3 20120101 20120630

I want the query to resolve overlapping dates as well as merge contiguous segment and leave non-contiguous segments as is. The final result of the query should be like this.

key dte_start(number) dte_end(number)
1 20010101 20081231
1 20100101 20101231
2 20090101 20111231
2 20140101 20141231
3 20070101 20101231
3 20120101 20120630

I tried using the lead over partition along with the standard overlap query and case logic to get the results but couldn't get it to work.

View 14 Replies View Related

SQL & PL/SQL :: How To Find The Latest Date

Jul 31, 2010

SQL> select * from emp;

SINO BOOK UPDATION_
---------- ---------- ---------
1 UB 01-MAR-10
2 UB 12-MAR-10
3 SB 12-MAR-10
4 DB 12-MAR-10
4 MB 12-JUN-10
4 MB 31-JUL-10

6 rows selected.

SQL> SELECT sino, book, updation_date
2 FROM emp
3 WHERE updation_date IN (SELECT MAX (updation_date)
4 FROM emp
5 GROUP BY book);

SINO BOOK UPDATION_
---------- ---------- ---------
2 UB 12-MAR-10
3 SB 12-MAR-10
4 DB 12-MAR-10
4 MB 31-JUL-10

I would like to know, how to find out the latest date from above query without using group functions like max, min,order by and group by.

View 10 Replies View Related

SQL & PL/SQL :: Find From And To Date Between Two Fields

Aug 2, 2010

I have oracle table for Leave

LeaveNo - FromDt - ToDt - Query Retrieve
1 - 01/01/2010 - 31/03/2010 - No
2 - 01/02/2010 - 31/05/2010 - Yes
3 - 01/04/2010 - 30/04/2010 - Yes
4 - 25/04/2010 - 15/05/2010 - Yes
5 - 01/05/2010 - 31/05/2010 - No
6 - 15/03/2010 - 14/04/2010 - Yes
7 - 10/04/2010 - 15/04/2010 - Yes

I want find out who was leave on 01/04/2010 to 30/04/2010. user input parameter may vary like 10/04/2010 to 15/04/2010 or 12/04/2010 to 12/04/2010.

how to write oracle sql?

View 12 Replies View Related

SQL & PL/SQL :: How To Find Out Date Format

Feb 6, 2013

I am getting daily basis data from third party in excel format which i am converting into CSV format and then uploading into oracle tables using External tables.Now problem is that every time i getting the dates in diff format i.e. sometimes dd-mon-yyyy , dd/mm/yyyy etc.

Now every time i have to open my code and change it there ...to make it as oracle date format. IS there way i can find out format of date and based on format i can do operations with getting errors every time.

I am storing the TP(Excel date) date into varchar columns only and then varchar2 column value i m inserting/updating into date format using to_Date ().

View 9 Replies View Related

PL/SQL :: How To Find Max Date From A Table

Mar 2, 2013

I have two table in the database. I want to find the maximum routine date for a case_id when the lock_date is between 20 jan to 29 jan 2012 .

But I am not getting actual output (Output routine_date should be "28-jan-2012" but I m getting "31-jan-2012")

Master
case_id lock_date
101 23-jan-2012
101 24-jan-2012
102 27-jan-2012
102 29-jan-2012
101 30-jan-2012
101 29-jan-2012

Routine (for routine work)
case_id routine_date
101 23-jan-2012
103 28-jan-2012
102 21-jan-2012
102 29-jan-2012
101 21-jan-2012
101 28-jan-2012
101 31-jan-2012

select m.case_id, r.routine_date from master m, routine r,
(select case_id, max(routine_date) from routine group by case_id) rr

where m.case_id=r.case_id
and m.case_id=rr.case_id
and r.routine_date=rr.routine_date

View 7 Replies View Related

Find Records That Have Most Current Date?

Apr 13, 2004

I have two tables

table_1
--Emp_id--|--Sup_id--|Sup_name|--Date--|
-------------------------------------------------
--00001--|--00005 --|---ABCD--|01-MARCH-2004
--00002--|--00006 --|---BCDE--|02-MARCH-2004
--00003--|--00007 --|---CDEF--|03-MARCH-2004
--00001--|--00008 --|---DEFG--|04-APRIL-2004
--00003--|--00009 --|---EFGH--|05-APRIL-2004

table_2
--Emp_id--|Emp_name|
--------------------------------
--00001--|--QWER--|
--00002--|--ASDF--|
--00003--|--ZXCV--|
--00004--|--POIU--|

table_1 contain records on employee and the supervisor they are under at a certain date.

As some employee(00001 & 00003) have a different supervisor from different date, I'll like to extract from table_1 the record of each employee in the table that only contain the supervisor info on the most recent date.And from table_2, i'll like to extract the employee's name. These records extracted from both the tables would the be put into a new table,table_3

Example: For employee 00001, only extract record that have the most recent date which is 04-APRIL-2004 and not on 01-MARCH-2004

table_3
Emp_id|Emp_name|Sup_id|Sup_name|Date|
------------------------------------------------
00001 |--QWER--|00008 |--DEFG---|04-APRIL-2004
00002 |--ASDF-- |00006 |--BCDE---|02-MARCH-2004
00003 |--ZXCV-- |00009 |--EFGH---|05-APRIL-2004

How to write an SQL statement to perform this?

View 6 Replies View Related

SQL & PL/SQL :: Find Start And End Date Of Week?

Feb 5, 2011

I am creating report for this i want to make one query..

Query like this

My input is YEAR, MONTH AND WEEK and i want to find out start date and end date for this week...

Scenario like this
-----------------
I/P - Year = 2011, WEEK = 2 , Month - FEB
i will get o/p like this '06-feb-2010' and '12-feb-2010'...

View 10 Replies View Related

SQL & PL/SQL :: Find Years Between Any Given Date Range

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

SQL & PL/SQL :: Find Lock On Particular Date And Time?

Dec 18, 2012

we can easily find out the locks which are currently active session.

Is any other way to find out locks on particular date and time using data dictionary?

For example, we have to find whether TT_objects occured the lock or not on ON yesterday.

View 3 Replies View Related

SQL & PL/SQL :: Find Date When Attributes Modified?

Sep 14, 2012

The requirement is to find the last date when either ATTRIB1 or ATTRIB2 were modified for each ID. There are many other columns in the table, but I'm not interested in other columns.

Test Case

SET LINES 100
SET PAGES 100
DROP TABLE test_log
/
CREATE TABLE test_log

[code]....

Expected Output

ID LAST_CHANGE_DATE
---------- --------------------
11 02-SEP-2012 10:58:32
35 05-AUG-2012 10:58:32

I have written the below query, to get the required output.

SELECT MAX(i_date) last_change_date
FROM
(
SELECT seq,id, attrib1, attrib2, i_date,
row_number() OVER (PARTITION BY attrib1 ORDER BY i_date) rn1,
row_number() OVER (PARTITION BY attrib2 ORDER BY i_date) rn2

[code]....

View 6 Replies View Related

PL/SQL :: Query First Date Before Payment Date

Nov 25, 2012

How to get the payment date less than the sch date?

example:

1) for 07/24/2012 sch date the nearest payment date is 07/27/2012.

sch date should be less than the payment date.

Sch dates 07/24/2012 and 07/26/2012 are less than payment date 07/27/2012 but we take the least one.

2) sch date 07/27/2012 is less than the payment date 08/08/2012 so 07/27/2012

Below is the scripts and output needed.

create table cust_sch_tbl
(
customer_NUMBER     number,
c_CODE     char(5),
sch_DATE date
);

[Code]...

Output needed:

cust   cust code payment date      sch date
4611     c1     7/27/2012      7/24/2012
4611     c1     8/8/2012 16:57      7/27/2012
4611     c1     10/19/2012 16:37 10/11/2012
4611     c1     11/15/2012      10/30/

View 6 Replies View Related

SQL & PL/SQL :: Find Out What Claims Overlap Other Via Date Ranges

Sep 3, 2010

Lets say I have a table,
Claim_id,dateA,dateB
it has 5 million rows

I need to see if any dateA,dateB of a claim_id falls within any other dateA,dateB of another claim ID

Basically
select * from table a, table b, where

(a.dateA,a.dateB) overlaps (b.dateA,b.dateB)

now I can write the query simply enough by aliasing the table 2x but no matter how I try I cant see a way to get around doing a Carteasion join

Index are ignored because it has to scan the full table anyway even if I hint the index

and the cost of the join ends up astronomical aka 5million rows X 5 million Rows....

And it ends up doing the full table scan a few times

AHHHHHHHHHHHHHHHHHH

this table is expected to grow to at least 20 million records

View 17 Replies View Related

SQL & PL/SQL :: Find Out Date Difference In Day-month-year?

Jul 8, 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'

View 4 Replies View Related

SQL & PL/SQL :: How To Find Date And Time Of A Column In Table

Jun 20, 2013

How to find date and time of a column in table ?

say for example there is a column called 'date_txn' in a table .When i select that particular column it display output as 'June 2013'.But i want output "with date and time".

View 10 Replies View Related

PL/SQL :: How To Find Out Initial Date Of Creation Of Table

Nov 7, 2013

I have a table which contains some data. But we are dropped and recreated the same table several times. Now I wanted to know that when this table created initially. How to findout the date of creation(very first time). 

View 13 Replies View Related

SQL & PL/SQL :: Function To Find Max Last Logout Date For Each Agent Daily

Jun 3, 2010

I have to create a function. I need to find the max last logout date for each agent daily. For example, if an agent logged in for the first time at 9:00 and he logged out at 12:00 and he logged in again in 14:00 and he logged out at 15:00 the time I need my report to show is 15:00. How can I do that?In order to make it easiest for you to understand I am sending you this query:

select
a.login as login2,
To_Char(max(s.endtime), 'dd/MM/yyyy, HH24:MI:SS') as lastLogout
from cti.agent a
inner join cti.agentsessionlog s
on s.agentid = a.agentid and To_Char(s.endtime) != '31-DEC-99 11.59.59.000000 PM'
group by a.login;

This query returns the agent's login and the agent's last logout time. It works fine if I enter a date between but I cannot do that. If a use this query as it is and I try to export a report for 31/5 it shows as lastlogout the logout for 01/06 or 2/06. Is there a function I can use? I have a deadline.

View 7 Replies View Related

PL/SQL :: Find Invalid Date And Month Not Match With Calendar?

Mar 21, 2013

I want to find the row with invalid day, month which are not matching with calendar day and month. Also the program should capture the data if the year <1900

with xx as
(select 101 as ID, '24/05/1899' as create_date from dual
union all
select 101, '32/03/2012' from dual
union all
select 102 ,'30/02/2012' from dual
union all
select 101 , '29/02/2013' from dual

[code]...

View 16 Replies View Related

SQL & PL/SQL :: Find Missing Date For A Year Excluding Saturday And Sunday

Feb 3, 2011

I 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

View 5 Replies View Related

Security :: How To Find Out Date View Status Changed To Invalid

Mar 10, 2011

We are trying to audit a view. We are currently seeing that view in production instance and trying to find out when exactly it got invalidated.

View 4 Replies View Related

SQL & PL/SQL :: How To Find The Count In The Query

Apr 16, 2010

from the below string i want to find the count of the "<-" using a single query.

string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"

If not the query, pointer on using SQL functions.

View 3 Replies View Related

SQL & PL/SQL :: Query To Find Usernames From Their IDs

Oct 23, 2012

the below requirement.

I have 2 tables, first one MAIN_T, which contains, user_id and username.

The second table is a relationship table which contains the referal details.

The requirement is to convert the user_id and frnd_id from RELATION_T to their respctive names using MAIN_T.

-- Test Case

CREATE TABLE main_t (user_id NUMBER, username VARCHAR2(10));
CREATE TABLE relation_t(user_id NUMBER, frnd_id NUMBER);

INSERT INTO main_t VALUES(1, 'u1');
INSERT INTO main_t VALUES(2, 'u2');
INSERT INTO main_t VALUES(3, 'u3');
INSERT INTO main_t VALUES(4, 'u4');
INSERT INTO main_t VALUES(5, 'u5');

[Code]....

I have written this query, which gives me the expected output.

SELECT membr.username member_name , frnd.username friend_name
FROM
main_t membr,
main_t frnd,
relation_t rltn
WHERE
rltn.user_id=membr.user_id
AND rltn.frnd_id=frnd.user_id
/

MEMBER_NAM FRIEND_NAM
---------- ----------
u1 u2
u2 u3
u5 u4
u1 u5

4 rows selected.

a better way for this requirement.

View 2 Replies View Related

PL/SQL :: Query To Find Single Row?

Oct 5, 2012

i have a requirement where i have to get the id who are only subscribed to only one course based on the below provided data.

ID Course
103812     CFH     
102968     REP     
103812     DFH     
102968     DFH     
103071     DFH     
102968     CFH

View 5 Replies View Related

SQL & PL/SQL :: Query To Find Empty Column?

Aug 3, 2010

I have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.

I need a query that should return the column which is empty but its next and previous column is not empty.

View 13 Replies View Related

SQL & PL/SQL :: Query To Find Last 6 Month Records?

Aug 23, 2010

SELECT HISTORY_ID ,SUM(MISSED_SCHOOL) AS MISSED_SCHOOL,SUM(MISSED_SCHOOL_LAST) AS MISSED_SCHOOL_LAST
FROM EMRASTHAMAHISTORYDETAILS
WHERE ------
GROUP BY HISTORY_ID

There is no date column in table using sysdate alone need to retrieve last 6 month records

how to use in where condition

View 13 Replies View Related

SQL & PL/SQL :: Query To Find The Number Of Alphabets?

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

SQL & PL/SQL :: Query To Find Relevant Table Name

Jan 16, 2013

i need a SQL query which should return me relevant table names. i.e. if there is table 'EMP' , then query should give table names with below result:

EMP
EMP_1
EMP_2
EMP_3
EMP_4

i.e. All tables which is starting with EMP (No Hardcoding of table, It should be dynamic way).I know we can achieve through SELECT * FROM USER_ OBJECTS WHERE OBJECT_NAME LIKE 'EMP%'. But here object_name i will passing dynamically.

View 10 Replies View Related







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