Home » SQL & PL/SQL » SQL & PL/SQL » How to find No of Entries per week For consecutive week
| How to find No of Entries per week For consecutive week [message #660652] |
Thu, 23 February 2017 12:13  |
gspandian
Messages: 59
Registered: February 2005
Location: India
|
Member |
|
|
HI
I am hving a requiredment of finding a records are finishing criteria
We have to get the records/members having one record in each week for 4 weeks consecutively. If any gaps then it should not consider.
Any idea how we can achivee in oracle, will be helpful.
Thanks
|
|
|
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660654 is a reply to message #660652] |
Thu, 23 February 2017 12:41  |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
In the end, feedback to your topics to know if the problem is solved, help future readers with the solution you ended, thanks people who spent time to help you.
|
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660655 is a reply to message #660653] |
Thu, 23 February 2017 13:10 |
gspandian
Messages: 59
Registered: February 2005
Location: India
|
Member |
|
|
Sorry , below given the sample
Table
MemberID Date
1234 1/1/2017
1234 1/8/2017
1234 1/15/2017
1234 1/22/2017
2222 1/1/2017
2222 1/15/2017
2222 1/22/2017
5555 1/1/2017
5555 1/8/2017
5555 1/22/2017
5555 1/29/2017
5555 2/5/2017
5555 2/12/2017
In the above sample, the member 1234 has consecutive entries in a week for 4 weeks and eligible and another mbr 2222 does not has entries for 1 week and hence not eligible and mebr 5555 is eligible since has consecutive entry from 1/22 to 2/12
We need eligibile member for the period of 1/1/17 - 12/31/17 if any 4 consecutive weeks with 1 entry per week is eligible. Thanks
|
|
|
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660657 is a reply to message #660656] |
Thu, 23 February 2017 13:25 |
gspandian
Messages: 59
Registered: February 2005
Location: India
|
Member |
|
|
Oracle version
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Script
create table temps(MemberID number(10), AccessDate date)
insert into temps values(1234, '1/1/2017')
insert into temps values(1234, '1/8/2017')
insert into temps values(1234, '1/15/2017')
insert into temps values(1234, '1/22/2017')
insert into temps values(2222, '1/1/2017')
insert into temps values(2222, '1/15/2017')
insert into temps values(2222, '1/22/2017')
insert into temps values(5555, '1/1/2017')
insert into temps values(5555, '1/8/2017')
insert into temps values(5555, '1/22/2017')
insert into temps values(5555, '1/29/2017')
insert into temps values(5555, '2/5/2017')
insert into temps values(5555, '2/12/2017')
Need the output as
MemberID
1234
5555
|
|
|
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660660 is a reply to message #660658] |
Thu, 23 February 2017 13:48 |
gspandian
Messages: 59
Registered: February 2005
Location: India
|
Member |
|
|
Hi,
I given teh corrected script
insert into temps values(1234, to_date('01-JAN-17', 'DD-MON-YY'))
insert into temps values(1234, to_date('08-JAN-17', 'DD-MON-YY'))
insert into temps values(1234, to_date('15-JAN-17', 'DD-MON-YY'))
insert into temps values(1234, to_date('22-JAN-17', 'DD-MON-YY'))
insert into temps values(2222, to_date('1-JAN-17', 'DD-MON-YY'))
insert into temps values(2222, to_date('15-JAN-17', 'DD-MON-YY'))
insert into temps values(2222, to_date('22-JAN-17', 'DD-MON-YY'))
insert into temps values(5555, to_date('1-JAN-17', 'DD-MON-YY'))
insert into temps values(5555, to_date('8-JAN-17', 'DD-MON-YY'))
insert into temps values(5555, to_date('22-JAN-17', 'DD-MON-YY'))
insert into temps values(5555, to_date('29-JAN-17', 'DD-MON-YY'))
insert into temps values(5555, to_date('5-FEB-17', 'DD-MON-YY'))
insert into temps values(5555, to_date('12-FEB-17', 'DD-MON-YY')) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660670 is a reply to message #660667] |
Thu, 23 February 2017 18:03 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- find the value that your nls_date_language parameter is set to:
SCOTT@orcl_12.1.0.2.0> select value from v$nls_parameters where parameter = 'NLS_DATE_LANGUAGE'
2 /
VALUE
----------------------------------------------------------------
AMERICAN
1 row selected.
-- create table:
SCOTT@orcl_12.1.0.2.0> create table temps(MemberID number(10), AccessDate date)
2 /
Table created.
-- insert records, specifying the nls_date_language parameter
-- (this third parameter is only necessary when entering data into a system where the parameter is different
-- and your are using characters like JAN and FEB to specify values instead of numbers like 1 and 2;
-- otherwise you could just use to_date('01-JAN-17','DD-MON-YY') or to_date('01-01-17','DD-MM-YY').
-- Also, it is generally better to make the year four digits such as 2017 and YYYY.
SCOTT@orcl_12.1.0.2.0> insert all
2 into temps values(1234, to_date('01-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
3 into temps values(1234, to_date('08-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
4 into temps values(1234, to_date('15-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
5 into temps values(1234, to_date('22-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
6 into temps values(2222, to_date('1-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
7 into temps values(2222, to_date('15-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
8 into temps values(2222, to_date('22-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
9 into temps values(5555, to_date('1-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
10 into temps values(5555, to_date('8-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
11 into temps values(5555, to_date('22-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
12 into temps values(5555, to_date('29-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
13 into temps values(5555, to_date('5-FEB-17', 'DD-MON-YY', 'nls_date_language=american'))
14 into temps values(5555, to_date('12-FEB-17', 'DD-MON-YY', 'nls_date_language=american'))
15 select * from dual
16 /
13 rows created.
-- The following query uses 'sun' or whatever value is appropriate for Sunday according to your nls_date_language
-- to determine the first sunday of each week.
-- If all of your values are Sundays, as in your sample data, then you could just use those values,
-- instead of deriving the first Sunday from them.
-- The query below calculates the first Sudays,
-- then calcuclates the number of consecutives Sundays starting from each one for each memberid,
-- then calcuclates the maximum number of consecutive Sundays for each memberid,
-- then limits the result to those memberid's having a maximum number of consecutive sundays >= 4
SCOTT@orcl_12.1.0.2.0> select memberid
2 from (select memberid,
3 (select count (*)
4 from temps t2
5 start with t2.memberid = t1.memberid
6 and t2.accessdate = t1.accessdate
7 connect by prior memberid = memberid
8 and prior next_day(accessdate-7, 'sun') = next_day(accessdate-7, 'sun') - 7)
9 as consecutive_weeks
10 from temps t1)
11 group by memberid
12 having max (consecutive_weeks) >= 4
13 order by memberid
14 /
MEMBERID
----------
1234
5555
2 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660700 is a reply to message #660697] |
Fri, 24 February 2017 14:28 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Or, if I am not missing something:
select distinct memberid,
to_char(trunc(accessdate,'ww'),'Dy DD-Mon-YYYY') accessdate
from temps
where level = 4
connect by prior memberid = memberid
and trunc(accessdate,'ww') = prior trunc(accessdate,'ww') + 7
and level <= 4
order by memberid
/
MEMBERID ACCESSDATE
---------- ---------------
1234 Sun 22-Jan-2017
5555 Sun 12-Feb-2017
SQL>
SY.
[Updated on: Fri, 24 February 2017 14:29] Report message to a moderator |
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660701 is a reply to message #660697] |
Fri, 24 February 2017 14:41 |
gspandian
Messages: 59
Registered: February 2005
Location: India
|
Member |
|
|
Thanks
I used the below dates it returns the 2/17/2017 as reached date not the 1/23/17
did urs also returns the same? Will be hlpfl if you can help me. Thanks in advance
insert all
into temps2 values(8850099, to_date('02-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('12-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('16-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('17-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('20-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('23-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('30-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('31-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('06-FEB-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('08-FEB-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('13-FEB-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('17-FEB-17', 'DD-MON-YY', 'nls_date_language=american'))
select * from dual |
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660702 is a reply to message #660700] |
Fri, 24 February 2017 14:56 |
gspandian
Messages: 59
Registered: February 2005
Location: India
|
Member |
|
|
Hi,
If you use the below it returns 4 rows of all sundays what we expects it
1/23/17 the member has consecutive 4 weeks of activities without any gap
Will be hlpfl if you can help me. Thanks in advance
insert all
into temps2 values(8850099, to_date('02-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('12-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('16-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('17-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('20-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('23-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('30-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('31-JAN-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('06-FEB-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('08-FEB-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('13-FEB-17', 'DD-MON-YY', 'nls_date_language=american'))
into temps2 values(8850099, to_date('17-FEB-17', 'DD-MON-YY', 'nls_date_language=american'))
select * from dual
|
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660703 is a reply to message #660702] |
Fri, 24 February 2017 15:26 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You previously wrote,
Quote:
Is there any way we can get the max date on which the member achieved the 4 consecutive date?
I interpreted that to mean the greatest date in the most recent set of at least 1 accessdate in four consecutive weeks for each member.
It now looks like, based on the results that you want, that you mean the first date at which there was at least 1 accessdate in four consecutive weeks for each member. In that case, you can use the query below, which is similar to what Solomon offered in that it checks for level = 4 instead of count >= 4.
-- new test data, including display of the first day of the week, in order to make it easier to see consecutive weeks:
SCOTT@orcl_12.1.0.2.0> select memberid, accessdate, next_day(accessdate-7, 'sun')
2 from temps order by memberid, accessdate
3 /
MEMBERID ACCESSDATE NEXT_DAY(ACCESS
---------- --------------- ---------------
8850099 Mon 02-Jan-2017 Sun 01-Jan-2017
8850099 Thu 12-Jan-2017 Sun 08-Jan-2017
8850099 Mon 16-Jan-2017 Sun 15-Jan-2017
8850099 Tue 17-Jan-2017 Sun 15-Jan-2017
8850099 Fri 20-Jan-2017 Sun 15-Jan-2017
8850099 Mon 23-Jan-2017 Sun 22-Jan-2017
8850099 Mon 30-Jan-2017 Sun 29-Jan-2017
8850099 Tue 31-Jan-2017 Sun 29-Jan-2017
8850099 Mon 06-Feb-2017 Sun 05-Feb-2017
8850099 Wed 08-Feb-2017 Sun 05-Feb-2017
8850099 Mon 13-Feb-2017 Sun 12-Feb-2017
8850099 Fri 17-Feb-2017 Sun 12-Feb-2017
12 rows selected.
-- new query:
SCOTT@orcl_12.1.0.2.0> select memberid, min (accessdate) accessdate
2 from temps
3 where level = 4
4 connect by prior memberid = memberid
5 and prior next_day(accessdate-7, 'sun') = next_day(accessdate-7, 'sun') - 7
6 group by memberid
7 order by memberid
8 /
MEMBERID ACCESSDATE
---------- ---------------
8850099 Mon 23-Jan-2017
1 row selected.
|
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660704 is a reply to message #660703] |
Fri, 24 February 2017 16:08 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
select memberid,
to_char(min(accessdate),'Dy DD-Mon-YYYY') accessdate
from temps2
where level = 4
connect by prior memberid = memberid
and trunc(accessdate,'ww') = prior trunc(accessdate,'ww') + 7
and level <= 4
group by memberid
order by memberid
/
MEMBERID ACCESSDATE
---------- ---------------
8850099 Mon 23-Jan-2017
SQL>
SY.
|
|
|
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660706 is a reply to message #660704] |
Fri, 24 February 2017 16:29 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Ah, I overlooked OP wants week to start Sunday - Saturday regardless of client:
select memberid,
to_char(min(accessdate),'Dy DD-Mon-YYYY') accessdate
from temps2
where level = 4
connect by prior memberid = memberid
and trunc(accessdate + 1,'iw') = prior trunc(accessdate + 1,'iw') + 7
and level <= 4
group by memberid
order by memberid
/
MEMBERID ACCESSDATE
---------- ----------------
8850099 Mon 23-Jan-2017
SQL>
Barbara's solution, even though it ensures week starts Sunday, is NLS dependent and will fail for non-english client:
SQL> alter session set nls_language=french;
Session altered.
SQL> select memberid, min (accessdate) accessdate
2 from temps2
3 where level = 4
4 connect by prior memberid = memberid
5 and prior next_day(accessdate-7, 'sun') = next_day(accessdate-7, 'sun') - 7
6 group by memberid
7 order by memberid
8 /
and prior next_day(accessdate-7, 'sun') = next_day(accessdate-7, 'sun') - 7
*
ERROR at line 5:
ORA-01846: ce n'est pas un jour de semaine valide
SQL> select memberid,
2 to_char(min(accessdate),'Dy DD-Mon-YYYY') accessdate
3 from temps2
4 where level = 4
5 connect by prior memberid = memberid
6 and trunc(accessdate,'ww') = prior trunc(accessdate,'ww') + 7
7 and level <= 4
8 group by memberid
9 order by memberid
10 /
MEMBERID ACCESSDATE
---------- ---------------------------------------------
8850099 Lun. 23-Janv.-2017
SQL>
SY.
|
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660707 is a reply to message #660706] |
Sat, 25 February 2017 01:13 |
|
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> alter session set nls_date_format='DD-MON-YYYY' nls_date_language=american;
Session altered.
SQL> select to_char(trunc(to_date('13/11/2016','DD/MM/YYYY'),'ww'),'Day') from dual;
TO_CHAR(T
---------
Friday
SQL> select to_char(trunc(to_date('13/11/2016','DD/MM/YYYY'),'iw'),'Day') from dual;
TO_CHAR(T
---------
Monday[Updated on: Sat, 25 February 2017 01:14] Report message to a moderator |
|
|
|
| Re: How to find No of Entries per week For consecutive week [message #660721 is a reply to message #660707] |
Sat, 25 February 2017 05:59 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Oops, I pasted wrong code. It should be:
SQL> alter session set nls_language=french;
Session altered.
SQL> select memberid,
2 to_char(min(accessdate),'Dy DD-Mon-YYYY') accessdate
3 from temps2
4 where level = 4
5 connect by prior memberid = memberid
6 and trunc(accessdate + 1,'iw') = prior trunc(accessdate + 1,'iw') + 7
7 and level <= 4
8 group by memberid
9 order by memberid
10 /
MEMBERID ACCESSDATE
---------- ---------------------------------------------
8850099 Lun. 23-Janv.-2017
SQL>
trunc(accessdate,'iw') is beginning of the week accessdate falls into when week starts Monday. And trunc(accessdate + 1,'iw') - 1 is beginning of the week accessdate falls into when week starts Sunday. So full CONNECT BY condition would be:
trunc(accessdate + 1,'iw') + 1 = prior trunc(accessdate + 1,'iw') + 1 + 7
or, after simplification:
trunc(accessdate + 1,'iw') = prior trunc(accessdate + 1,'iw') + 7
SY.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 17:10:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
