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 Go to next message
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 #660653 is a reply to message #660652] Thu, 23 February 2017 12:35 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
gspandian wrote on Thu, 23 February 2017 10:13
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
We can not provide you any SQL since we don't know table name or column names.


Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

If you provide CREATE TABLE statement & INSERT statments for sample data & then explain what should be the results based upon sample data, the SQL will soon follow thereafter.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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 Go to previous messageGo to next message
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 #660656 is a reply to message #660655] Thu, 23 February 2017 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Where are the CREATE TABLE and INSERT statements?
Where is your Oracle version?
Where is the result you want?
What do you mean by "week"?

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 Go to previous messageGo to next message
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 #660658 is a reply to message #660657] Thu, 23 February 2017 13:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> create table temps(MemberID number(10), AccessDate date)
  2
SQL> /

Table created.

SQL> insert into temps values(1234, '1/1/2017')
  2  /

1 row created.

SQL> insert into temps values(1234, '1/8/2017');

1 row created.

SQL> insert into temps values(1234, '1/15/2017');
insert into temps values(1234, '1/15/2017')
                               *
ERROR at line 1:
ORA-01843: not a valid month

'1/8/2017' is a string NOT a date, is this 1st August or 8th January?
How could Oracle know it if you don't tell him?
This is the reason of TO_DATE with a format.


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 Go to previous messageGo to next message
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 #660661 is a reply to message #660660] Thu, 23 February 2017 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> insert into temps values(5555, to_date('12-FEB-17', 'DD-MON-YY'));
insert into temps values(5555, to_date('12-FEB-17', 'DD-MON-YY'))
                                       *
ERROR at line 1:
ORA-01843: not a valid month
We don't all talk the same language, either use digits or specify the language in the third parameter.

You still didn't specify what you mean by "week".
Week of the year? Week of the month? ISO week?
Clarify!

Re: How to find No of Entries per week For consecutive week [message #660662 is a reply to message #660661] Thu, 23 February 2017 14:02 Go to previous messageGo to next message
gspandian
Messages: 59
Registered: February 2005
Location: India
Member
Sorry it is inserted to my system
Dont know what is language in the third param. will chk

it is week of the month.
Re: How to find No of Entries per week For consecutive week [message #660663 is a reply to message #660662] Thu, 23 February 2017 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What language do you think FEB is an abbreviation of a month?

Week of the month start with the 1st day and ends with the 7th one and so on.
So, for the current month, weeks of the month start a Wednesday and end a Tuesday, is this what you want?

Re: How to find No of Entries per week For consecutive week [message #660665 is a reply to message #660663] Thu, 23 February 2017 14:26 Go to previous messageGo to next message
gspandian
Messages: 59
Registered: February 2005
Location: India
Member
it inserted in oracle in my system Shocked

we expects the start of the week from Sunday - Saturday and not bound to a month. means 4 consecutive it can overlap with other month.
Re: How to find No of Entries per week For consecutive week [message #660666 is a reply to message #660665] Thu, 23 February 2017 14:31 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
gspandian wrote on Thu, 23 February 2017 21:26
it inserted in oracle in my system Shocked
...
As I said, we don't speak the same language.


Re: How to find No of Entries per week For consecutive week [message #660667 is a reply to message #660666] Thu, 23 February 2017 14:40 Go to previous messageGo to next message
gspandian
Messages: 59
Registered: February 2005
Location: India
Member
Sorry,

Anything more needed.? it is feb 12th 2017
Re: How to find No of Entries per week For consecutive week [message #660668 is a reply to message #660667] Thu, 23 February 2017 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Some statements I can execute on my system.

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
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 #660671 is a reply to message #660670] Thu, 23 February 2017 19:08 Go to previous messageGo to next message
gspandian
Messages: 59
Registered: February 2005
Location: India
Member
Thanks for the reply and help,

The date will come any day in a week also we will have multiple entries in a week.

Will be helpful if i get modified script.

Thanks once again
Re: How to find No of Entries per week For consecutive week [message #660672 is a reply to message #660671] Thu, 23 February 2017 19:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
Registered: November 2002
Location: California, USA
Senior Member
gspandian wrote on Thu, 23 February 2017 17:08
Thanks for the reply and help,

The date will come any day in a week also we will have multiple entries in a week.

Will be helpful if i get modified script.

Thanks once again

The query that I already provided should work under those conditions.
Re: How to find No of Entries per week For consecutive week [message #660690 is a reply to message #660672] Fri, 24 February 2017 09:43 Go to previous messageGo to next message
gspandian
Messages: 59
Registered: February 2005
Location: India
Member
Hi,
Thanks it is working i am testing with data.

Will there any way we can tune further to work with larger data?
Re: How to find No of Entries per week For consecutive week [message #660693 is a reply to message #660690] Fri, 24 February 2017 10:38 Go to previous messageGo to next message
gspandian
Messages: 59
Registered: February 2005
Location: India
Member
Thanks Barbara Boehmer

I found the issue of slow, there is a mistake in my query when i plug in to your query.

Thanks a lot for your timely help
Re: How to find No of Entries per week For consecutive week [message #660694 is a reply to message #660672] Fri, 24 February 2017 12:52 Go to previous messageGo to next message
gspandian
Messages: 59
Registered: February 2005
Location: India
Member
HI
Is there any way we can get the max date on which the member achieved the 4 consecutive date?
Re: How to find No of Entries per week For consecutive week [message #660697 is a reply to message #660694] Fri, 24 February 2017 13:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> select memberid,
  2  	    -- added line below
  3  	    max (accessdate) keep (dense_rank last order by consecutive_weeks) accessdate
  4  from   (select memberid,
  5  		    -- added line below
  6  		    accessdate,
  7  		   (select count (*)
  8  		    from   temps t2
  9  		    start with t2.memberid = t1.memberid
 10  			    and t2.accessdate = t1.accessdate
 11  		    connect by prior memberid = memberid
 12  			   -- changed last - 7 to + 7 in line below
 13  			   and prior next_day(accessdate-7, 'sun') = next_day(accessdate-7, 'sun') + 7)
 14  		    as consecutive_weeks
 15  	     from   temps t1)
 16  group  by memberid
 17  having max (consecutive_weeks) >= 4
 18  order  by memberid
 19  /

  MEMBERID ACCESSDATE
---------- ---------------
      1234 Sun 22-Jan-2017
      5555 Sun 12-Feb-2017

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
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 #660705 is a reply to message #660704] Fri, 24 February 2017 16:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
Registered: November 2002
Location: California, USA
Senior Member
Solomon's method is probably better in that it uses 'ww' which is not dependent on nls_date_language, instead of 'sun' which is dependent on nls_date_language. He also used <= 4 within the connect by, eliminating any additional unnecessary levels.

[Updated on: Fri, 24 February 2017 16:23]

Report message to a moderator

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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 Go to previous message
Solomon Yakobson
Messages: 2697
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.
Previous Topic: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Next Topic: Why difference between count(*) and count(*)
Goto Forum:
  


Current Time: Sat Jan 20 05:59:25 CST 2018

Total time taken to generate the page: 0.06440 seconds