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.
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
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.
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
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.
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 ().
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
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?
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.
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))
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
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".
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).
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.
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
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
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';
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
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
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.
