Home » SQL & PL/SQL » SQL & PL/SQL » year comparison - pls help (merged)
year comparison - pls help (merged) [message #419913] Wed, 26 August 2009 02:43 Go to next message
chrisraj
Messages: 9
Registered: August 2009
Junior Member
Hi,

I have a scenario like this; I shoud'nt select the record if the member has visited the club twice in the same year (eg. Harry visited the club in May/2002 and July/2002). However, it is required to select the member (John) who had visited in June 2002 and June 2003, i.e. different years). Please help with the SQL query. Thanks.
MEMBER_ID         MEMBER_NAME
1003              DAVID
1002              JOHN
1001              HARRY

MEMBER_ID          VISIT_DATE
1001               5/10/2002
1001               7/25/2002
1002               6/18/2002
1002               6/23/2003

List MEMBER_ID, MEMBER_NAME who had visited the club in 2 or more times in different years; (use only YEAR component for
comparison)

Expected result is = 1002, "JOHN"

Create table scripts and Insert statements for this scenario as below;
CREATE TABLE MEMBER
(
  MEMBER_ID    NUMBER(12),
  MEMBER_NAME  VARCHAR2(15 BYTE)
)

Insert into MEMBER
   (MEMBER_ID, MEMBER_NAME)
 Values
   (1003, 'DAVID');
Insert into MEMBER
   (MEMBER_ID, MEMBER_NAME)
 Values
   (1002, 'JOHN');
Insert into MEMBER
   (MEMBER_ID, MEMBER_NAME)
 Values
   (1001, 'HARRY');
COMMIT;


CREATE TABLE MEMBER_VISIT
(
  MEMBER_ID   NUMBER(12),
  VISIT_DATE  DATE
)

Insert into MEMBER_VISIT
   (MEMBER_ID, VISIT_DATE)
 Values
   (1001, TO_DATE('05/10/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MEMBER_VISIT
   (MEMBER_ID, VISIT_DATE)
 Values
   (1001, TO_DATE('07/25/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MEMBER_VISIT
   (MEMBER_ID, VISIT_DATE)
 Values
   (1002, TO_DATE('06/18/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MEMBER_VISIT
   (MEMBER_ID, VISIT_DATE)
 Values
   (1002, TO_DATE('06/23/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;


Edited to add code tags JI

[Updated on: Wed, 26 August 2009 06:27] by Moderator

Report message to a moderator

Re: year comparison - pls help [message #419916 is a reply to message #419913] Wed, 26 August 2009 03:17 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Before getting help, you have to show that what you have tried.
And read guidelines how to post your question.

regards,
Delna
Re: year comparison - pls help [message #419922 is a reply to message #419913] Wed, 26 August 2009 03:37 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
SELECT *
  FROM MEMBER
 WHERE member_id IN (SELECT   member_id
                         FROM member_visit
                     GROUP BY member_id, TO_CHAR (visit_date, 'YYYY')
                     HAVING COUNT (*) < 2);
Re: year comparison - pls help [message #419973 is a reply to message #419913] Wed, 26 August 2009 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you want all rows that are alone in a year for a member?
Is this true?

Clue:
SQL> select extract(year from sysdate) y from dual;
         Y
----------
      2009

1 row selected.

Regards
Michel

Re: year comparison - pls help [message #419996 is a reply to message #419922] Wed, 26 August 2009 07:13 Go to previous messageGo to next message
chrisraj
Messages: 9
Registered: August 2009
Junior Member
Thanks Cherry. It was a perfect answer for that data set. The same queryt will not work, If I add another record in MEMBER_VISIT table as single visit to the club.

MEMBER_ID VISIT_DATE
1003 27-AUG-2004

I don't get right result when I use the following query;

SELECT *
FROM MEMBER
WHERE member_id IN (SELECT member_id
FROM member_visit
GROUP BY member_id, TO_CHAR (visit_date, 'YYYY')
HAVING COUNT (*) > 1);

Please help.
Re: year comparison - pls help [message #419998 is a reply to message #419996] Wed, 26 August 2009 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please help.

Please precise your requirements and answer my questions.

How does your query could answer your question as you said: "I shoud'nt select the record if the member has visited the club twice in the same year" and you just select those that have more than 2 visits in a year.

What are the exact requirements?

Also 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 (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 26 August 2009 07:26]

Report message to a moderator

Please help - SQL query [message #420071 is a reply to message #419913] Wed, 26 August 2009 22:51 Go to previous messageGo to next message
chrisraj
Messages: 9
Registered: August 2009
Junior Member
Hi,

Question:
List the MEMBER_ID, MEMBER_NAME who had visited the club in TWO or more times in different years; (need to use only YEAR component for comparison)

Following SQL query doesn't give expected result. PleaseThanks.

SELECT *
FROM MEMBER
WHERE member_id IN (SELECT member_id
FROM MEMBER_VISIT
GROUP BY member_id, TO_CHAR (visit_date, 'YYYY')
HAVING COUNT (*) >= 2)


MEMBER_ID MEMBER_NAME
1003 DAVID
1002 JOHN
1001 HARRY

MEMBER_ID VISIT_DATE
1001 5/10/2002
1001 7/25/2002
1002 6/18/2002
1002 6/23/2003
1003 8/24/2004

Expected result is = 1002, "JOHN"

Because he had visited the club in June 2002 and June 2003, i.e. two visits in different years.

Below are the Create tables and Insert statements for this scenario;

CREATE TABLE MEMBER
(
MEMBER_ID NUMBER(12),
MEMBER_NAME VARCHAR2(15 BYTE)
)

Insert into MEMBER
(MEMBER_ID, MEMBER_NAME)
Values
(1003, 'DAVID');
Insert into MEMBER
(MEMBER_ID, MEMBER_NAME)
Values
(1002, 'JOHN');
Insert into MEMBER
(MEMBER_ID, MEMBER_NAME)
Values
(1001, 'HARRY');
COMMIT;


CREATE TABLE MEMBER_VISIT
(
MEMBER_ID NUMBER(12),
VISIT_DATE DATE
)

Insert into MEMBER_VISIT
(MEMBER_ID, VISIT_DATE)
Values
(1001, TO_DATE('05/10/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MEMBER_VISIT
(MEMBER_ID, VISIT_DATE)
Values
(1001, TO_DATE('07/25/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MEMBER_VISIT
(MEMBER_ID, VISIT_DATE)
Values
(1002, TO_DATE('06/18/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MEMBER_VISIT
(MEMBER_ID, VISIT_DATE)
Values
(1002, TO_DATE('06/23/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MEMBER_VISIT
(MEMBER_ID, VISIT_DATE)
Values
(1003, TO_DATE('08/24/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;


Re: Please help - SQL query [message #420072 is a reply to message #420071] Wed, 26 August 2009 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/419913/136107/#msg_419913

Do not cross/multi-post

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Please help - SQL query [message #420090 is a reply to message #420071] Thu, 27 August 2009 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of reposting the same question, post what we asked you.

Regards
Michel
Re: Please help - SQL query [message #420100 is a reply to message #420072] Thu, 27 August 2009 01:04 Go to previous messageGo to next message
chrisraj
Messages: 9
Registered: August 2009
Junior Member
Hi,

I have not re-posted it again. As solution given by Cherry, the query is not producing the expected result for the new data set posted. Thanks.
Re: Please help - SQL query [message #420101 is a reply to message #420100] Thu, 27 August 2009 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 26 August 2009 14:23

...
Also 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 (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...

Why you query does not return the expected result?

Regards
Michel

[Updated on: Thu, 27 August 2009 01:09]

Report message to a moderator

Re: Please help - SQL query [message #420103 is a reply to message #420071] Thu, 27 August 2009 01:14 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
You could begin with a a self join:
SELECT m1.member_id, m1.visit_date
  FROM member_visit m1, member_visit m2
 WHERE m1.member_id  =  m2.member_id 
   AND m1.visit_date <> m2.visit_date

And then add the EXTRACT- function as @michel showed You.
Re: Please help - SQL query [message #420104 is a reply to message #420071] Thu, 27 August 2009 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
List the MEMBER_ID, MEMBER_NAME who had visited the club in TWO or more times in different years;

What does this mean exactly?
Is this
- at least one visit in at least 2 different years?
- one and only one visit in at least 2 different years?

Regards
Michel

[Updated on: Thu, 27 August 2009 01:15]

Report message to a moderator

Re: Please help - SQL query [message #420108 is a reply to message #420104] Thu, 27 August 2009 01:36 Go to previous messageGo to next message
chrisraj
Messages: 9
Registered: August 2009
Junior Member
Hi Michel,

What I meant was;

1) ignore the selection, if the memeber had visited the club, twice or more times in the same year (eg. 1001, HARRY from the data set)

2) ignore the selection, if the memeber had visited the club, just only once (eg. 1003, DAVID from the data set)

3) select the members if the COUNT >= 2 i.e their TOTAL club visit in different years (eg. 1002, JOHN from the data set. John has visited the club in the year 2002 and 2003 and his total visit count = 2) --> Expected result.

Hope this explantion helps. Thanks.
Re: Please help - SQL query [message #420112 is a reply to message #420108] Thu, 27 August 2009 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
3) select the members if the COUNT >= 2 i.e their TOTAL club visit in different years (eg. 1002, JOHN from the data set. John has visited the club in the year 2002 and 2003 and his total visit count = 2) --> Expected result.

But just ONE in each year. Please confirm this.

Regards
Michel
Re: Please help - SQL query [message #420113 is a reply to message #420103] Thu, 27 August 2009 02:00 Go to previous messageGo to next message
chrisraj
Messages: 9
Registered: August 2009
Junior Member
Hi Jum,

SELECT m1.member_id, m1.visit_date
FROM member_visit m1, member_visit m2
WHERE m1.member_id = m2.member_id
AND m1.visit_date <> m2.visit_date
AND m1.member_id IN (SELECT member_id
FROM member_visit
GROUP BY member_id, TO_CHAR (visit_date, 'YYYY')
HAVING COUNT (*) = 1)

The result is correct;
MEMBER_ID VISIT_DATE
1002 6/23/2003
1002 6/18/2002

But, I need to display the output only Member_Name from MEMBER table. How do I join MEMBER table to get only Member_Name column as output?

Re: Please help - SQL query [message #420114 is a reply to message #420112] Thu, 27 August 2009 02:04 Go to previous messageGo to next message
chrisraj
Messages: 9
Registered: August 2009
Junior Member
Yes Michel. Just one visit in each year, but total visit count must be >= 2.
Re: Please help - SQL query [message #420115 is a reply to message #420114] Thu, 27 August 2009 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    visits as (
  3      select member_id, 
  4             count(*) nb_visits, 
  5             count(distinct extract(year from visit_date)) nb_years
  6      from member_visit
  7      group by member_id
  8    )
  9  select * 
 10  from member
 11  where member_id in 
 12          ( select member_id from visits where nb_visits = nb_years and nb_years > 1 )
 13  /
 MEMBER_ID MEMBER_NAME
---------- ---------------
      1002 JOHN

1 row selected.

Regards
Michel
Re: Please help - SQL query [message #420119 is a reply to message #420115] Thu, 27 August 2009 02:46 Go to previous messageGo to next message
chrisraj
Messages: 9
Registered: August 2009
Junior Member
Fantastic and amazing query. It worked. Thanks Michel for the great help and patience.
Re: Please help - SQL query [message #420260 is a reply to message #420115] Thu, 27 August 2009 22:50 Go to previous messageGo to next message
chrisraj
Messages: 9
Registered: August 2009
Junior Member
Hi Michel,

Any other simplified SQL possible for that scenario? I mean without using the key word "WITH". Thank you.
Re: year comparison - pls help (merged) [message #420262 is a reply to message #419913] Thu, 27 August 2009 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Any other simplified SQL possible for that scenario?
>I mean without using the key word "WITH".
As in you did not follow Posting Guidelines.
http://www.orafaq.com/forum/t/88153/0/
It clearly state posters should state Oracle version
so they don't get solutions they can not use
as in your case here.
Re: Please help - SQL query [message #420271 is a reply to message #420260] Fri, 28 August 2009 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use the equivalent but I think less readable one:
SQL> select * 
  2  from member
  3  where member_id in 
  4          ( select member_id 
  5            from member_visit
  6            group by member_id
  7            having count(*) = count(distinct extract(year from visit_date)) 
  8               and count(distinct extract(year from visit_date)) > 1
  9          )
 10  /

 MEMBER_ID MEMBER_NAME
---------- ---------------
      1002 JOHN

1 row selected.

Regards
Michel
Re: Please help - SQL query [message #420274 is a reply to message #420260] Fri, 28 August 2009 00:59 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
If even @Michel gives a full solution Cool , here is another simple one:
SELECT m1.member_id, mb.member_name
  FROM member_visit m1, member_visit m2, member mb
 WHERE m1.member_id = m2.member_id AND m1.member_id = mb.member_id
   AND EXTRACT(YEAR FROM m1.visit_date) > EXTRACT(YEAR FROM m2.visit_date)

MEMBER_ID MEMBER_NAME    
---------- ---------------
      1002 JOHN           

1 row selected.

[Updated on: Fri, 28 August 2009 01:02]

Report message to a moderator

Re: Please help - SQL query [message #420276 is a reply to message #420274] Fri, 28 August 2009 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You query does not fit the requirements:
SQL> insert into member_visit values (1001, to_date('23/06/2003','DD/MM/YYYY'));

1 row created.

SQL> SELECT m1.member_id, mb.member_name
  2    FROM member_visit m1, member_visit m2, member mb
  3   WHERE m1.member_id = m2.member_id AND m1.member_id = mb.member_id
  4     AND EXTRACT(YEAR FROM m1.visit_date) > EXTRACT(YEAR FROM m2.visit_date)
  5  /

 MEMBER_ID MEMBER_NAME
---------- ---------------
      1002 JOHN
      1001 HARRY
      1001 HARRY

3 rows selected.

Should be the same result than previously.

Regards
Michel

[Updated on: Fri, 28 August 2009 01:04]

Report message to a moderator

Re: Please help - SQL query [message #420279 is a reply to message #420260] Fri, 28 August 2009 01:45 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
Any other simplified SQL possible for that scenario? I mean without using the key word "WITH". Thank you.


You can use the other query that Michel suggested for this or you can use one the following queries.
select mem.* 
from member mem, 
(select member_id, 
        count(*) nb_visits, 
        count(distinct extract(year from visit_date)) nb_years
  from member_visit
  group by member_id
having count(*) = count(distinct extract(year from visit_date)) 
        and count(distinct extract(year from visit_date)) > 1
) mem_visit

where mem.member_id = mem_visit.member_id



select mem.* 
from member mem, 
(select member_id, 
        count(*) nb_visits, 
        count(distinct extract(year from visit_date)) nb_years
  from member_visit
  group by member_id
) mem_visit
where mem.member_id = mem_visit.member_id
and mem_visit.nb_visits = mem_visit.nb_years
and mem_visit.nb_years > 1




Kindly note that I have not tested both of them though.

[Updated on: Fri, 28 August 2009 01:52]

Report message to a moderator

Previous Topic: Get the records with alternate workflow value
Next Topic: Problem in Execute Immediate
Goto Forum:
  


Current Time: Sun Dec 04 20:53:55 CST 2016

Total time taken to generate the page: 0.08125 seconds