SQL & PL/SQL :: Select Only Last Date?

Jun 25, 2011

How do I select only last date for each contragentid? So for contragentid = 111270 it should be only '14.05.2010'.

select dd.contragentid,
decode(dd.ratingvalue,'PK1',1,'PK2',2,'PK3',2,'PSR',2,'UN4',2,'VVL',2,'BK',4,3) as ratingvalue from
(select 36 as contragentid, 'UN1' as ratingvalue, '25.02.2010' as ratingstartdate from dual
union all
select 111270 as contragentid, 'PK1' as ratingvalue, '26.11.2009' as ratingstartdate from dual
union all
select 111270 as contragentid, 'PK3' as ratingvalue, '14.05.2010' as ratingstartdate from dual
union all
select 111270 as contragentid, 'BK' as ratingvalue, '14.06.2011' as ratingstartdate from dual ) dd
where dd.ratingstartdate <= to_date('31.05.2010', 'DD.MM.YYYY')

Also I need to select all rows from second test case for those contragentid which absent in first case, it should be one statement for both cases.

select * from
(select 5 as contragentid, 2 as ratingvalue from dual
union all
select 111270 as contragentid, 1 as ratingvalue from dual ) hh

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Select Max Date And Pk?

Sep 6, 2013

here is my query

select
max(PERIOD_DUE_DATE) , form_submission_id
from form_submission
group by
form_submission_id

but this returns all the records, I need only the max date along with its form_submission_id.In reality Its a complex query but to explain my problem I putting this simple query, how to select max(column) and column2 from table.

View 4 Replies View Related

SQL & PL/SQL :: Select Max Date Between Columns In A Row?

May 3, 2012

BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

See attached file for creation script and data load.Each staff member is required to complete at least one task every three years. The source table contains an EID (aka User ID) and a date column for each task with a date of when the task was completed. If a task has never been started/completed the date value is "NULL".

If a row looks like this:

EID,DATE01,DATE02,DATE03,DATE04,DATE05,DATE06,DATE07,DATE08,DATE09,DATE10,DATE11,DATE12,DATE13
68,NULL,11/10/2009,5/3/2010,4/22/2012,NULL,NULL,4/14/2010,NULL,NULL,NULL,NULL,4/14/2010,4/14/2010

The the result set would look like this:

EID,MaxDate,Within_Last_3_Years
68,4/22/2012,'YES'

The result set will show the EID, date of latest task completed and if the task was completed within the last 3 years from given date (for example June 30, 2012).

View 4 Replies View Related

SQL & PL/SQL :: Select Max (date) From Group?

Jun 13, 2011

I am having a following sql query:

Select
Product.code,
Customer.code,
Backlog.Date
SUM(Backlog.Qty)
From
Product,
Customer,

[code]....

Using this query i am getting following output::

Prod Cust Date Qty
A X 17-june 1000
A X 18-june 2000
A X 21-june 4000
B Z 11-May 200
B Z 15-May 500
C W 1- Dec 300

Out of these groups, i want to select qty for each product and customer, where date is maximum,that is following results::

Prod Cust Date Qty
A X 21-june 4000
B Z 15-May 500
C W 1- Dec 300

what condition/clause should i add in my query,tried a lot with having clause but on success.

View 17 Replies View Related

PL/SQL :: How To Select Max Value Of Date Column

Feb 14, 2013

How to select max value of date column which tables are having date coulmn.

View 1 Replies View Related

PL/SQL :: Select Record Between Date Gap

Aug 2, 2013

I have a sql query where I need to select only records with an 18 month gap between max(date) and previous date( no dates between max(date)and 18 month gap date), when I run the below query it should only select supid 130, not 120 (even though 120 does contain an 18 month gap date it also has a date that is less then the 18 month gap( '25-NOV-2012','DD-MON-YYYY'). how would get the query to look back 18 months for the next date and evaluate the month_between.

. example: 

create table supply(supID number(8), supply varchar2(20), supdate Date,supamount number(13,2));
 insert into supply values(100,'Tapes',to_date('01-AUG-2013','DD-MON-YYYY'),50.00);
insert into supply values(100,'TV',to_date('01-APR-2013','DD-MON-YYYY'),250.00);
insert into supply values(100,'Discs',to_date('25-DEC-2012','DD-MON-YYYY'),25.00);
insert into supply values(120,'Tablets',to_date('25-AUG-2013','DD-MON-YYYY'),15.00);

[Code]....

and p.supid in(select s.supid from supply s where months_between

(s.supdate,p.supdate)<-18)      SUPID SUPPLY               SUPDATE    SUPAMOUNT---------- -------------------- --------- ----------       120 Tablets              25-AUG-13         15       130 Discs                25-JUL-13         75

View 9 Replies View Related

Select Multiple Date Ranges?

Aug 2, 2011

I have data such as 'hours', 'date' when and employee worked on the project. What I need is to select the total amount of hours per month of March, April, May, etc...

I know how to select data per single date but wonder how to do it per multiple dates. How does one select total amount of hours per multiple date ranges (March, April...)?

View 4 Replies View Related

Last Date / Time When Index Used For Select

Jul 26, 2011

Is there a way I can find what the last date/time and index was used for a select...

I have a table with several indexes on them, which I beleive are not being accessed.

I use the following the query to find indexes that where not accessed in a while but this I believe is limited my my workload repository retention, which is set to 90 days.

select index_name from dba_indexes where table_name='<table name>'
and index_name not in (select c1 from(
select p.object_name c1, p.operation c2, p.options c3, count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner = 'MTAS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id
group by p.object_name, p.operation, p.options
order by 1,2,3))

Without increasing my repository retention is there a way I can get the last date/time, which an index was used instead of just saying it has not been used in 90 days (retention setting). Is this information kept in the SQL plan?

View 2 Replies View Related

Select Data Based On Date

Apr 27, 2010

I have a script which is used to run a job based on the users choice. For example: I have two table, Files and Requests

User select the files to be executed for each request. This data will be stored in Requests table.

Table 1: Files
files
======
file-1
file-2
file-3
..
..
file-n

Table 2: Requests

request file lup_date
==================================
request-1 file1,file2,file3 04-JAN-2009
request-2 file1,file4,file5 06-JAN-2009
request-3 file6,file2 021-JAN-2009
request-4 file1,file2 04-FEB-2009
request-5 file1,file2 08-JAN-2009
request-6 file1,file2 04-MAR-2009
.......... ........... ................
request-n-1 file6,file2,file4 04-DEC-2009
request-n file6,file3,file4 04-DEC-2009

how to get the output in below format. Count how many times each file is selected in a month.

Output format should be like below..
==============================================
File_Name Jan Feb Mar Apr ---------- Dec
==============================================
file1 2 1 3 0 ---------- 2
file2 1 0 2 1 ---------- 3
file-n 8 2 3 0 ---------- 2

View 2 Replies View Related

Date Equality In Select Statement

Sep 3, 2013

Date equality in select statement giving 0 result even table contains record matching to it.

1- select *from  EOE_POC.PRODUCT_TEST_REPORT where CREATE_DATE = '03-SEP-13'  
2 - select *from  EOE_POC.PRODUCT_TEST_REPORT where CREATE_DATE >= '03-SEP-13' 

above query (2nd one) is giving 2 records.But I am intend to check for equality not greater

View 5 Replies View Related

SQL & PL/SQL :: Select Records Where Date Is Max From Tables

Oct 6, 2011

I've got 3 tables and I need to select records where date is max from these tables.

select * from the_table_1
----------------------------------
contract_key date_1 saldo
1234 30.9.2011 12:06:50 14,6638

select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 26.9.2011 11:04:02 5,6638

select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 29.9.2011 17:39:43 2,5438

how to do that ?

View 11 Replies View Related

Insert Or Select Date Data Type Value In AM Or PM?

Oct 26, 2006

How do i insert or select date data type value in am or pm Eg 10:30:00 am or 10:35:00 pm

View 3 Replies View Related

SQL & PL/SQL :: Select Date Values From A Table Between Two Dates?

Feb 16, 2010

Oracle 10g

In a table I have a column update_date and its type is DATE. Sample values from this column are as follows. I am using the following query to select all update_date lie between sysdate and sysdate-90.

select update_date from table1
where update_date between sysdate and sysdate-90

The above query retrun no data even data is there in the table for this range.

Update_date

11-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10

[code]...

View 7 Replies View Related

SQL & PL/SQL :: Select Query To Get Top Two Rows Based On Status Date

Oct 29, 2012

I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.

SELECT ACTIVE_STATUS, ACCT_UNIT, DESCRIPTION, DIRECTOR, DIRECTOR2, STATUS_DATE, OBJ_ID, STATUS_FLAG, SUR_KEY
FROM STSI

View 10 Replies View Related

SQL & PL/SQL :: Select Statement To View Transaction Log For Specific Date?

Jul 24, 2011

I'm using Oracle 9i Enterprise edition, Is there a select statement to view transaction log for specific date?

View 1 Replies View Related

SQL & PL/SQL :: Invalid Results When Including Date With Null Value In Select List?

Jun 15, 2010

I'm working on a Oracle Database, and I'm gettin incorrect results when including a date field in the select list which is NULL in the table.

This works correctly and returns exactly one row:

SELECT firstField FROM table WHERE firstField = 'value'

while this doesn't and returns no rows:

SELECT firstField, secondField FROM table WHERE firstField = 'value'

Where secondField is of type date and its value is NULL (00-000-00). Note that the only thing that changes is the select list.

View 6 Replies View Related

SQL & PL/SQL :: Display Date Ranges In One Column As Separate Date Periods (start And End Date) In Two?

Jun 1, 2010

I'm trying to work out how to take a table like this:

IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09

And display the data like this:

IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

View 13 Replies View Related

Forms :: Expiry Date To Automatically Show A Date 15 Years After Initial Date

Apr 12, 2010

I have a two date fields in my form; valid from date and expiry date.

Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.

I need my expiry date to automatically show a date 15 years after this date?

View 8 Replies View Related

SQL & PL/SQL :: Select First 40 Columns Without Giving All Column Names In Select Clause?

Mar 3, 2011

I have a table with around 80 columns. All i need is to select first 40 columns.

Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.

View 2 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

Application Express :: Unable To Set Default Date Value For Tabular Form DB Date Field?

Dec 3, 2012

Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,

1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE

2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added

:P1_SYSDATE := sysdate

and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.

I get the error

ORA-01790: expression must have same datatype as corresponding expression

I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.

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

Date Arithmetic - Formula To Subtract Years And Give Date

Sep 27, 2007

I am a novice in oracle

I have 2 columns in my table

->Col1 with experience in years entered as an integer
->Col2 with current date

I need to add another column as a date value adn for that i need to subtract Currentdate-Col1 when i tried currentdate-Col1 it just subtracted the days i need the formula to subtract years and give a date

I have worked in DB2 and all u need to do there was add the keyword years at the end but in oracle the same does not work

View 8 Replies View Related

Server Utilities :: How To Get Date And Time In Date Column While Sqlldr

Jun 6, 2012

I am not able to load complete date along with time in the date column. here is my table desc

DESC STAGE
Name Null Type
----------------------------------
TABLE_NAME NOT NULL VARHCAR(20)
RECORDCOUNT NUMBER
CREATED_DATE NOT NULL DATE

my control file is like this

LOAD DATA
APPEND
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
( TABLE_NAME
,RECORDCOUNT
,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"
)

the data gets loaded, but it appears like this in the table
HIGHSCHOOL3080606-JUN-12
MIDDLESCHOOL8768006-JUN-12

BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)

View 10 Replies View Related

PL/SQL :: Retrieve Date Range From 2 Search Input Date Fields

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

SQL & PL/SQL :: Declare Date Variable And Assign It To Current System Date

Apr 19, 2011

Write a program to declare a date variable and assign it to the current system date. Depending on the day of the month the program should print the following:

If day is 1-10 then print "It is day<day number> of <month name>. It is early in the month".
If day is 11-20 then print "It is day<day number> of <month name>. It is the middle of the month".
If day is 21-31 then print "It is day<day number> of <month name>. It is nearly the end of the month".

For example, if the day is November 30, then print "It is day 30 of November. It is nearly the end of the month".

View 9 Replies View Related

Application Express :: How To Select MIN Value Under Default Tag Of Select List

Oct 5, 2012

I M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."

BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.

BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.

THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.

View 7 Replies View Related

Can Partition A Table Based On Date If It Does Not Have A Date Column

Jun 21, 2012

How can we partition a table based on date if it does not have a date column.

Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.

I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.

View 1 Replies View Related

Converting Date Field To Datestamp (Epoch Date)

Apr 11, 2013

I have a small prolem thats best described like this....

a table called TONY with a field named VISITED (date as YYYYMMDD).

We want to populate the field TIMESTAMP (Last visited timestamp, 18 digits) using midnight or 00:00:00 on VISITED value.

Something like:

UPDATE TONY SET TIMESTAMP = �(whatever the formula is involving VISITED).

but i cannot figure out the best way to derive the TIMESTAMP value...

it's a date to epoch conversion, and i can find many examples of Epoch to date, but thats the wrong way around for me i'm afraid!

Oracle 11gR2 by the way...

View 4 Replies View Related

SQL & PL/SQL :: Converting Char Into Date With Column Having Non-date Formats

Aug 16, 2010

i have a table with the following description

create table gl_periods(period_name varchar2(10),transactions number (2) );

with the data as :

period_name transactions
------------ --------------
JAN-10 12
FEB-10 12
MAR-10 8
APR-10 23
ADJ_TOM-10 25
MAY-10 37
JUN-10 41
JUL-10 10
PHY_JAY-10 6
AUG-10 14
SEP-10 22

My requirment is to find out the period names and transactions which are in valid date formats and are less than sysdate and the non date formats are adjustments made by different users for their transactions

View 8 Replies View Related







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