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


ADVERTISEMENT

SQL & PL/SQL :: To Retrieve Only Latest Repair Information Based On Latest Date

Apr 3, 2012

I would like to retrieve only the latest repair information based on the latest date regardless of the other information, so I would like to query only items 3 and 5 in the following example.

drop table a;
create table a(
seq number,
custom_id number,
repair_id number,
repair_date date);
[code]........

View 10 Replies View Related

SQL & PL/SQL :: How To Find Last Or Latest Inserted Row

Apr 22, 2013

i have a table in that every month i insert rows and my table doesn't have primary key,index and date filed.for example:-table is like this

name salary Id
john5000101
brat4500102
smith4600103
john5500101
brat4600102
smith4800103

i think one cannot tell in above table whether "john" salary is 5000 or 5500(last insert row is 5000 then john salary is 5000)

when it comes to retrieve i have to pick the latest(last) insert row of particular Name.Is there any auto generated row_numbers in Oracle.

View 1 Replies View Related

SQL & PL/SQL :: Latest Date (or Max Date) For Consumers

Feb 8, 2007

The oracle table has the following fields,

A_ID, B_ID, CONSUMER_ID, U_DATE, ACTIVE_FLAG.

1 0 111 Jan-02-07 N
1 1 111 Feb-02-07 N
1 2 111 Mar-02-07 Y
1 3 111 Apr-02-07 Y
1 4 111 May-02-07 Y
1 1 222 Feb-06-07 N
1 1 222 Mar-06-07 N
1 1 222 Jun-06-07 Y
1 1 222 Jul-06-07 Y

The table has incorrect data. meaning for each consumer_id we want the ACTIVE_FLAG to be 'Y' only for it's latest record and the rest to be inactive. i.e. we want the data as follows:

A_ID, B_ID, CONSUMER_ID, U_DATE, ACTIVE_FLAG.

A_ID, B_ID, CONSUMER_ID, U_DATE, ACTIVE_FLAG.

1 0 111 Jan-02-07 N
1 1 111 Feb-02-07 N
1 2 111 Mar-02-07 N
1 3 111 Apr-02-07 N
1 4 111 May-02-07 Y
1 1 222 Feb-06-07 N
1 1 222 Mar-06-07 N
1 1 222 Jun-06-07 N
1 1 222 Jul-06-07 Y

View 14 Replies View Related

SQL & PL/SQL :: Having Latest Date

Aug 9, 2013

I have a query where i need to pull back the latest dated record for a tariff

my data looks like

Col_A Col_B Col_C
1| 100 29-Sep-11 Tariff_1
2| 200 24-Apr-12 Tariff_2
3| 300 17-Oct-12 Tariff_3

and i need to add a subquery which pull back the col_c with the larges Col_B i.e i need to pull back tariff_3 only

i tried using a max but dont think that will work.
...
and c.Col_c in ( select col_c where max(col_b))

View 21 Replies View Related

SQL & PL/SQL :: Selecting Latest Date

Aug 23, 2012

have a bit of a SQL trouble. I have a simple table (pcuk_BG_alloc_TAB) which stores Parts, Quantities and Applied dates

PART_NO QUANTITY APPLIED
PartA 100 10/8/2012
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 50 10/8/2012
PartC 75 15/8/2012
PartC 80 21/8/2012

I am only interested the latest applied date for each part and am looking for this to be returned in a select statement (as below)

PART_NO QUANTITY APPLIED
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 80 21/8/2012

I have tried using the max function (select part_no, quantity, max(applied) from pcuk_BG_alloc_TAB group by part_no, quantity) but seems as the records have different quantities it treats them separately.

View 21 Replies View Related

Query To Get Rows Of Latest Date From Each Group

Aug 9, 2012

I want to get rows by latest date for each group pf rows.

say my data is :

TYPE1 TYPE2 quantity DATE-ORDERED
sweets chocolate 10 05-FEB-2012
sweets chocolate 10 04-DEC-2012
sweets chocolate 10 08-FEB-2012
pastries chocolate 20 08-AUG-2012
[Code] ..........

I want to get results by latest date,
sweets chocolate 10 04-DEC-2012
pastries chocolate 20 08-AUG-2012
sweets vanilla 10 05-DEC-2012
pastries vanilla 20 05-NOV-2012

I have tried Queries with Max(DATE-ORDERED) and grouping it..its not showing me results because I don't have indexes in my data.

View 2 Replies View Related

SQL & PL/SQL :: Query To Get Data For Latest Date For Some Purchases?

Mar 13, 2012

I need a query to get data for the latest date for some purchases.

My expected output would be

Date - Item - Price - Qty
10/6/2011 10129 .5 1
6/8/2011 10130 13.33 9
2/6/2011 10131 21.74 24

Below are the scripts for this.I have also attached it.

CREATE TABLE XX_PO_LINES_ALL
( CREATION_DATE DATE,
ITEM_ID NUMBER,
UNIT_PRICE NUMBER,
QUANTITY NUMBER )

[code]......

View 4 Replies View Related

Retrieving Max / Latest Date From A Table With A Join To One Another Table

Sep 19, 2011

I am having trouble retrieving the Max, latest date, from a table with a join to one another table and other fields from both.I was able to get the MAX service_date grouped by id. But once I tried to add more fields to the query and another table it won't work.

Here is what I have:

selectMAX(cs.service_date), cs.notes, cs.applicant_id,wr.program_code,wr.last_name,wr.first_name,wr.region_code,wr.status_cd
from cs join wr on cs.applicant_id=wr.applicant_id
where wr.status_cd='AC'
group by cs.applicant_id

View 3 Replies View Related

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 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 :: 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

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

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

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

Get Latest Records?

Jun 9, 2011

I have a table that contains history for vehicle positions. In order to find the latest positions quickly, I've included a LATEST column that is 1 if the record is the latest position and 0 otherwise. The table is maintained via a stored procedure. The procedure first sets the latest record for the vehicle to history...

UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = <vehicle ID> AND latest = 1

It then inserts the new latest record...

INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (<vehicle_id>, <x pos>, <y pos>, SYSDATE, 1)

Is it possible for me to end up with 2 latest records?Consider this scenario...

Session #1: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1
Session #2: UPDATE vehicle_positions SET latest = 0 WHERE vehicle_id = 123 AND latest = 1
Session #1: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8533, -117.1180, SYSDATE, 1)
Session #2: INSERT INTO vehicle_positions (vehicle_id, longitude, latitude, insert_time, latest) VALUES (123, 32.8534, -117.1178, SYSDATE, 1)

I'd end up with 2 latest records. How can I protect against this? I considered using SELECT FOR UPDATE, but seems like there are too many negatives going that route

View 4 Replies View Related

SQL & PL/SQL :: Getting Latest Record From Different Tables?

Feb 4, 2011

I tried to post this issue earlier but it was not very clear. Well. Let me try to put in more better way.I have four tables storing order & customer information.

For given order number and cust _id I need to display latest record. here are my four tables.

SQL> select * from so;
ORD_NO ORD_DATE CUST_ID
---------- --------- ----------
1 01-JAN-10 10
2 02-JAN-09 20
3 03-FEB-11 30

SQL> select * from sol;

FK_ORD_NO FK_CUST_ID CUST_NAME LOCATION
---------- ---------- ------------------------- ----------
1 10 abc MA
2 20 xyz CA
3 30 ijk LA

[code]...

There will be a stored procedure that will take order_no and cust_id as input paremeters and return out ref cursor.

create procedure P1( in_ord_no in number,
in_cust_id in varchar2,
out_cur out sys_refcursor);

Here is the expected output.

Case 1 - in_ord_no =4 , in_cust_id = 10
Expected output - Empty cursor. Because bothe tables does not have matching record.
Case 2- in_ord_no = 3 in_cust_id = 30
Expected output =

[code]...

View 7 Replies View Related







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