Home » SQL & PL/SQL » SQL & PL/SQL » Count records by week (10g)
Count records by week [message #404793] Sun, 24 May 2009 00:19 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Is there any way i could count the number of ticket per week, below is my test data and expected results, any hints will be much appreciated.
create table ticket (
  id number,
  created_date date);
  
  
  insert into ticket values (1,'1-May-2009');
  insert into ticket values (2,'1-May-2009');
  insert into ticket values (3,'4-May-2009');
  insert into ticket values (4,'6-May-2009');
  insert into ticket values (5,'12-May-2009');
  insert into ticket values (6,'12-May-2009');
  insert into ticket values (7,'12-May-2009');
  insert into ticket values (8,'26-May-2009');
  
  commit;



Expected Results
date range               , count of tickets
1-May-2009 to 3-May-2009, 2
4-May-2009 to 10-May-2009, 1
11-May-2009 to 17-May-2009, 3
18-May-2009 to 24-May-2009, 0
25-May-2009 to 31-May-2009, 1

Re: Count records by week [message #404795 is a reply to message #404793] Sun, 24 May 2009 00:57 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue:
SQL> def month=5
SQL> def year=2009
SQL> with
  2    days as ( 
  3      select to_date('01/&month/&year','DD/MM/YYYY')+level-1 d 
  4      from dual 
  5      connect by level <= 
  6                   extract (day from (last_day(to_date('01/&month/&year','DD/MM/YYYY'))))
  7    )
  8  select to_number(to_char(d,'IW')) week,
  9         to_char(min(d),'DD-Mon-YYYY') first,
 10         to_char(max(d),'DD-Mon-YYYY') last
 11  from days
 12  group by to_number(to_char(d,'IW')) 
 13  order by 1
 14  /
      WEEK FIRST       LAST
---------- ----------- -----------
        18 01-May-2009 03-May-2009
        19 04-May-2009 10-May-2009
        20 11-May-2009 17-May-2009
        21 18-May-2009 24-May-2009
        22 25-May-2009 31-May-2009

5 rows selected.

Regards
Michel
Previous Topic: Delete 5 million records
Next Topic: validate the date data
Goto Forum:
  


Current Time: Thu Dec 08 22:19:49 CST 2016

Total time taken to generate the page: 0.11519 seconds