Home » SQL & PL/SQL » SQL & PL/SQL » Latest date (or max date) for consumers
Latest date (or max date) for consumers [message #218600] Thu, 08 February 2007 22:22 Go to next message
gulabo
Messages: 5
Registered: February 2007
Location: FL
Junior Member

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
Can someone suggest a way to achive this. Also, the table is very very big.

Thanks,
sverma
Re: Latest date (or max date) for consumers [message #218624 is a reply to message #218600] Fri, 09 February 2007 00:42 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
plz send me crate table structure and insert statement list
thnaks,
srinivas
Re: Latest date (or max date) for consumers [message #218630 is a reply to message #218624] Fri, 09 February 2007 01:23 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
select * from table where U_DATE in
(select max(U_DATE) from table where CONSUMER_ID in (111,222) group by CONSUMER_ID)
Re: Latest date (or max date) for consumers [message #218638 is a reply to message #218630] Fri, 09 February 2007 02:01 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry
see this quary

select * from table where U_DATE in
(select max(U_DATE) from table where CONSUMER_ID in (111,222)and ACTIVE_FLAG='y'
group by CONSUMER_ID)

thanks
pavuluri
Re: Latest date (or max date) for consumers [message #218639 is a reply to message #218600] Fri, 09 February 2007 02:01 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This could be one way to do it:
UPDATE TEST t SET
  active_flag = CASE
                  WHEN t.u_date = (SELECT MAX(t1.u_date) FROM TEST t1
                                   WHERE t1.consumer_id = t.consumer_id
                                  ) THEN 'Y'
                  ELSE 'N'
                END;
Re: Latest date (or max date) for consumers [message #219703 is a reply to message #218639] Thu, 15 February 2007 10:34 Go to previous messageGo to next message
gulabo
Messages: 5
Registered: February 2007
Location: FL
Junior Member

Thanks. It worked!
Re: Latest date (or max date) for consumers [message #486801 is a reply to message #218639] Mon, 20 December 2010 06:09 Go to previous messageGo to next message
Asila
Messages: 4
Registered: December 2010
Junior Member
Hello
nice hint.

Thank you

[EDITED BY LF]

I hate answering deleted messages. User removed it and posted what is written above the line. This was the original text:

Hello
How can you avoid the following outcome (I added a row before the last one):

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 4 222 May-02-07 Y
1 1 222 Jul-06-07 Y

Thank you

[Updated on: Mon, 20 December 2010 06:24] by Moderator

Report message to a moderator

Re: Latest date (or max date) for consumers [message #486804 is a reply to message #486801] Mon, 20 December 2010 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain your requirements with words.

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Latest date (or max date) for consumers [message #486806 is a reply to message #486804] Mon, 20 December 2010 06:21 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How did you get that result (so that you'd want to avoid id)? 111 and 222 are CUSTOMER_IDs. A row you added is this one:

1 4 222 May-02-07 Y

As "May" is not the latest record (but July, for customer 222), it can't be "Y" - it is "N".

So - you don't have to do anything (except, perhaps, explaining what you meant; but Michel already told you so).
Re: Latest date (or max date) for consumers [message #486893 is a reply to message #486806] Tue, 21 December 2010 02:15 Go to previous messageGo to next message
Asila
Messages: 4
Registered: December 2010
Junior Member
My apologies for the inconvenience Embarassed ,
I have the following situation (actually it's a simplification of what I have):

Let's say I have a table of scores per student and subject as follows:
ID	STUDENT	SUBJECT	SCORE	TEST_DATE
1	Bob	Geo	8	01/01/2010
2	Dan	Chm	7	02/02/2010
3	Roy	Geo	5	03/03/2010
4	Bob	Geo	6	04/04/2010
5	Bob	Geo	3	05/05/2010
I need a statement that
Gets the row with the latest score
that precedes a given score line (ID = 5)
for the same student and subject.

--Here is the definition
--(I denormalized for the sake of clarity):

create table DELETE_ME
(
  ID        NUMBER not null,
  STUDENT   VARCHAR2(5) not null,
  SUBJECT   NVARCHAR2(5) not null,
  SCORE     NUMBER not null,
  TEST_DATE DATE not null
);

--PK
alter table DELETE_ME
  add constraint DELETE_PK primary key (ID);

--Here are the insert statements
insert into DELETE_ME (ID, STUDENT, SUBJECT, SCORE, TEST_DATE)
values (1, 'Bob', 'Geo', 8, to_date('01-01-2010', 'dd-mm-yyyy'));
insert into DELETE_ME (ID, STUDENT, SUBJECT, SCORE, TEST_DATE)
values (2, 'Dan', 'Chm', 7, to_date('02-02-2010', 'dd-mm-yyyy'));
insert into DELETE_ME (ID, STUDENT, SUBJECT, SCORE, TEST_DATE)
values (3, 'Roy', 'Geo', 5, to_date('03-03-2010', 'dd-mm-yyyy'));
insert into DELETE_ME (ID, STUDENT, SUBJECT, SCORE, TEST_DATE)
values (4, 'Bob', 'Geo', 6, to_date('04-04-2010', 'dd-mm-yyyy'));
insert into DELETE_ME (ID, STUDENT, SUBJECT, SCORE, TEST_DATE)
values (5, 'Bob', 'Geo', 3, to_date('05-05-2010', 'dd-mm-yyyy'));

I have written the following statement:

SELECT * FROM delete_me d1, Delete_Me given
WHERE given.ID = 5 --This is the ID of the given row
AND   d1.test_date = 
   (SELECT MAX(d2.test_date)
	  FROM delete_me d2
		WHERE d2.student = given.student
		AND   d2.subject = given.subject
		AND d2.test_date < given.test_date
		)
AND d1.student = given.student
AND d1.subject = given.subject
AND d1.test_date < given.test_date;

It works. I don't like it.
I have too many statements of this sort.
Is there a trick to make it nicer?

[Updated on: Tue, 21 December 2010 02:19] by Moderator

Report message to a moderator

Re: Latest date (or max date) for consumers [message #486894 is a reply to message #486893] Tue, 21 December 2010 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't read and follow the guide to format your post. See how it looks now it is formatted, isn't it more readable?

Quote:
I need a statement that
Gets the row with the latest score
that precedes a given score line (ID = 5)
for the same student and subject.


SQL> with 
  2    data as (
  3      select id, student, subject,
  4             lag(id) over(partition by student, subject order by test_date) prev_id,
  5             lag(score) over(partition by student, subject order by test_date) prev_score,
  6             lag(test_date) over(partition by student, subject order by test_date) prev_test_date
  7      from delete_me
  8  )
  9  select prev_id id, student, subject, prev_score score, prev_test_date test_date
 10  from data
 11  where id = 5
 12  /
        ID STUDE SUBJE      SCORE TEST_DATE
---------- ----- ----- ---------- -------------------
         4 Bob   Geo            6 04/04/2010

Regards
Michel

[Updated on: Tue, 21 December 2010 02:30]

Report message to a moderator

Re: Latest date (or max date) for consumers [message #486895 is a reply to message #486893] Tue, 21 December 2010 02:27 Go to previous messageGo to next message
Asila
Messages: 4
Registered: December 2010
Junior Member
More specifically:
In the statement I wrote, I repeated the matching condition. Once to get the max date and another time to get the actual preceding line.

In reality, the matching condition is quite complex. It has decodes and joins and spans through 12 tables.

Is there a way to avoid repeating the condition?

Thank you
Re: Latest date (or max date) for consumers [message #486897 is a reply to message #486895] Tue, 21 December 2010 02:38 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Another option, somewhat uglier than Michel's:
SQL> select d1.*
  2  from (select d.id,
  3               d.student,
  4               d.subject,
  5               lag(d.id) over (partition by d.student, d.subject order by d.test_date) idl
  6        from delete_me d
  7       ) x,
  8       delete_me d1
  9  where d1.student = x.student
 10    and d1.subject = x.subject
 11    and d1.id = x.idl
 12    and X.id = 5;

        ID STUDE SUBJE      SCORE TEST_DATE
---------- ----- ----- ---------- ----------
         4 Bob   Geo            6 04.04.2010

SQL>
Re: Latest date (or max date) for consumers [message #486899 is a reply to message #486897] Tue, 21 December 2010 02:48 Go to previous messageGo to next message
Asila
Messages: 4
Registered: December 2010
Junior Member
Littlefoot,
Quite an improvement, thank you .

Mitchel,
I tried your statement in PL/SQL Developer it didn't return lines.

Regards
Asila
Re: Latest date (or max date) for consumers [message #486900 is a reply to message #486899] Tue, 21 December 2010 02:54 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried your statement in PL/SQL Developer it didn't return lines.

It should but as you didn't show what you did we can't help.

Regards
Michel
Previous Topic: Jobs not running
Next Topic: Solve queries
Goto Forum:
  


Current Time: Sat Dec 10 05:24:56 CST 2016

Total time taken to generate the page: 0.09640 seconds