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.
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
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).
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.
How to select max value of date column which tables are having date coulmn.
View 1 Replies View RelatedI 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
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...)?
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?
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
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
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 ?
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 RelatedOracle 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]...
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
I'm using Oracle 9i Enterprise edition, Is there a select statement to view transaction log for specific date?
View 1 Replies View RelatedI'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.
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.
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?
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.
i want to select dynamic column names in my select statement in my function.
View 4 Replies View RelatedVersion : 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.
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 RelatedI 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
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)
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
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".
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.
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.
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...
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