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  |
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 #419996 is a reply to message #419922] |
Wed, 26 August 2009 07:13   |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
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   |
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 #420101 is a reply to message #420100] |
Thu, 27 August 2009 01:07   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
_jum
Messages: 577 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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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 #420113 is a reply to message #420103] |
Thu, 27 August 2009 02:00   |
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 #420274 is a reply to message #420260] |
Fri, 28 August 2009 00:59   |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
If even @Michel gives a full solution , 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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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  |
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
|
|
|
Goto Forum:
Current Time: Sat Feb 15 12:00:59 CST 2025
|