Home » SQL & PL/SQL » SQL & PL/SQL » Data between two dates in a 12-month period
icon6.gif  Data between two dates in a 12-month period [message #248939] Mon, 02 July 2007 15:12 Go to next message
ramisy2k
Messages: 150
Registered: April 2005
Senior Member
Hello Gentlemen

I am facing a problem hope you would solve it.


I have a table
CREATE TABLE MATCHES
(MT_ID	NUMBER(4) CONSTRAINT MT_ID_PK PRIMARY KEY,
START_DATE	DATE,
END_DATE	DATE,
TEAM_A	NUMBER(2),
TEAM_B	NUMBER(2),
SERIES_ID	NUMBER(3))

INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(1,'1 Apr 1993', '5 Apr 1993',1,2,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(2,'21 Apr 1993', '25 Apr 1993',2,1,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(3,'30 Mar 1994', '3 Apr 1994',4,2,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(4,'1 May 1994', '5 May 1994',8,1,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(5,'24 Nov 1994', '28 Nov 1994',5,7,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(6,'31 Mar 1999', '4 Apr 1999',3,6,1);


select * from matches order by 1

MT_ID	START_DATE	END_DATE	TEAM_A	TEAM_B	SERIES_ID
1	01-APR-93	05-APR-93	1	2	1
2	21-APR-93	25-APR-93	2	1	1
3	30-MAR-94	03-APR-94	4	2	1
4	01-MAY-94	05-MAY-94	8	1	1
5	24-NOV-94	28-NOV-94	5	7	1
6	31-MAR-99	04-APR-99	3	6	1



Where
MT_ID is Match ID which is primary key
stat_date is the start date of the match and end_date is the date on which the match ended.
Each match has two teams i.e Team A and Team B. Each team has its unique ID. So in the above data there are eight teams form

ID 1 to 8.

Duration of match can be taken out if we minus the start date from the end date. For example:

Select END_DATE-START_DATE +1
From MATCHES
/

END_DATE-START_DATE+1 
5 
5 
5 
5 
5 
5 


1 is added to include the start day too.


My requirment is as follows:

For each of the 8 Teams, show
1. How many matches they played in each of the 7 twelve month periods from 1 April 1993 to 31 March 2000? If a match overlaped a 12 month period, then it would be treated as one match each on either side on the 12 month period.

For example, if a match started on 30 March 1994 and ended on 3 Apr 1994 then for the 12-month year starting from 1 Apr 1993 to 31 March 1994 it would be treated as one match and for the 12-month period starting from 1 Apr 1994 to 31 March 1995 it would be treated as another match. Becuase the match was split into 2 + 3 days on either side of the 12-month period.

2. How many days each team was playing a match in each of the 7 twelve month periods from 1 April 1993 to 31 March 2000?

For example, a team played 3 matches between Apr 1, 1993 and Mar 31, 1994. The Duration of each of the matches was 5+5+5 = 15 days. But the third match overlaped the 12-month period which started on 30 Mar 1994 and ended on 3 Apr 1994. So the correct number of playing days for that team would be 5 + 5 + 2 = 12 days from Apr 1, 1993 and Mar 31, 1994.

A want an efficient query that gets me both of the above answers



My desired output is like this:

For Team ID = 1       

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    2    10     
1-Apr-1994 to 31-Mar-1995    1    5
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


For Team ID = 2       

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    3    12     
1-Apr-1994 to 31-Mar-1995    1    3
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


For Team ID = 3       

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    0    0     
1-Apr-1994 to 31-Mar-1995    0    0
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    1    1
1-Apr-1999 to 31-Mar-2000    1    4


For Team ID = 4       

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    1    2     
1-Apr-1994 to 31-Mar-1995    1    3
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


For Team ID = 5      

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    0    0     
1-Apr-1994 to 31-Mar-1995    1    5
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


For Team ID = 6      

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    0    0     
1-Apr-1994 to 31-Mar-1995    0    0
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    1    1
1-Apr-1999 to 31-Mar-2000    1    4


For Team ID = 7      

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    0    0     
1-Apr-1994 to 31-Mar-1995    1    5
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


For Team ID = 8      

Period                       MT   Playing Days
1-Apr-1993 to 31-Mar-1994    0    0     
1-Apr-1994 to 31-Mar-1995    1    5
1-Apr-1995 to 31-Mar-1996    0    0
1-Apr-1996 to 31-Mar-1997    0    0  
1-Apr-1997 to 31-Mar-1998    0    0
1-Apr-1998 to 31-Mar-1999    0    0
1-Apr-1999 to 31-Mar-2000    0    0


I would appreciate a prompt reply in this regard.

Thanks in advance
Ramis Shah
Re: Data between two dates in a 12-month period [message #248940 is a reply to message #248939] Mon, 02 July 2007 15:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We would appreciate you don't see us as your homework solution provider and you post what you already tried.

Regards
Michel
Re: Data between two dates in a 12-month period [message #248946 is a reply to message #248940] Mon, 02 July 2007 15:37 Go to previous messageGo to next message
ramisy2k
Messages: 150
Registered: April 2005
Senior Member
Dear Michel

this is not any homework. its for my own personal programme. For this particular query I had no idea of how to handle it.

I would appreciate if some one could assist me to solve this.

Kind Regards
Ramis
Re: Data between two dates in a 12-month period [message #248948 is a reply to message #248946] Mon, 02 July 2007 15:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you think you can't do it and you didn't try?
you have to count, sum and group by year.
Year can be extract from a date using the extract function.
Yes, your year does not begin on January 1st but if you shift your dates to 3 months before, then you have standard years.

You are not a beginner, you should be able to at least start.
Try and post what do not work.

Regards
Michel
Re: Data between two dates in a 12-month period [message #249129 is a reply to message #248939] Tue, 03 July 2007 07:58 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
ramisy2k wrote on Mon, 02 July 2007 16:12

INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,SERIES_ID) VALUES(1,'1 Apr 1993', '5 Apr 1993',1,2,1);



Not a good method to INSERT DATEs. Character strings, yes, DATEs, no.
Re: Data between two dates in a 12-month period [message #249238 is a reply to message #248939] Tue, 03 July 2007 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey Ramis Shah,

You also posted it on AskTom (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:96012348060#339921900346054449).
Tom gives you an answer.
Why didn't you post it here to share it with us?

Regards
Michel


Re: Data between two dates in a 12-month period [message #249247 is a reply to message #249238] Tue, 03 July 2007 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tom's solution is the following one:
SQL> with data
  2  as
  3  (
  4   select sdate, add_months(sdate,12)-1 edate
  5     from
  6   (
  7   select add_months( to_date('01-apr-1993','dd-mon-yyyy'), (level-1)*12 ) sdate
  8     from dual
  9     connect by level <= 7
 10   )
 11  ),
 12  two_rows
 13  as
 14  (select 1 r from dual union all select 2 r from dual)
 15  select team, sdate, edate, count(*) mt, sum(days) days
 16    from (
 17  select data.sdate, data.edate,
 18         matches.start_date, matches.end_date,
 19             least( data.edate, matches.end_date )-greatest( data.sdate, matches.start_date )+1 days,
 20              decode( two_rows.r, 1, team_a, team_b ) team
 21    from data, two_rows, matches
 22   where data.edate >= matches.start_date
 23     and data.sdate <= matches.end_date
 24         )
 25   group by team, sdate, edate
 26   order by team, sdate
 27  /
      TEAM SDATE       EDATE               MT       DAYS
---------- ----------- ----------- ---------- ----------
         1 01-APR-1993 31-MAR-1994          2         10
           01-APR-1994 31-MAR-1995          1          5

         2 01-APR-1993 31-MAR-1994          3         12
           01-APR-1994 31-MAR-1995          1          3

         3 01-APR-1998 31-MAR-1999          1          1
           01-APR-1999 31-MAR-2000          1          4

         4 01-APR-1993 31-MAR-1994          1          2
           01-APR-1994 31-MAR-1995          1          3

         5 01-APR-1994 31-MAR-1995          1          5

         6 01-APR-1998 31-MAR-1999          1          1
           01-APR-1999 31-MAR-2000          1          4

         7 01-APR-1994 31-MAR-1995          1          5

         8 01-APR-1994 31-MAR-1995          1          5


13 rows selected.

Here's mine:
SQL> with 
  2    data as (
  3      select decode(trunc(rn/2),0,team_a,team_b) team,
  4             decode(mod(rn,2),
  5                    0,start_date,
  6                    decode(trunc(months_between(start_date,to_date('01/04/1993','DD/MM/YYYY'))/12),
  7                           trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12),
  8                           start_date,
  9                           add_months(to_date('01/04/1993','DD/MM/YYYY'),
 10                                      12*trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12))
 11                          )
 12                   ) start_date,
 13             decode(mod(rn,2),
 14                    1,end_date,
 15                    decode(trunc(months_between(start_date,to_date('01/04/1993','DD/MM/YYYY'))/12),
 16                           trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12),
 17                           end_date,
 18                           add_months(to_date('01/04/1993','DD/MM/YYYY'),
 19                                      12*trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12))
 20                             - 1)
 21                   ) end_date,
 22             decode(mod(rn,2),
 23                    0, trunc(months_between(start_date,to_date('01/04/1993','DD/MM/YYYY'))/12),
 24                       trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12)
 25                   ) period         
 26      from matches, 
 27           (select level-1 rn from dual connect by level <= 4)
 28      where mod(rn,2) = 0
 29         or trunc(months_between(start_date,to_date('01/04/1993','DD/MM/YYYY'))/12)
 30            != trunc(months_between(end_date,to_date('01/04/1993','DD/MM/YYYY'))/12)
 31    )
 32  select team, 
 33         add_months(to_date('01/04/1993','DD/MM/YYYY'),12*period) start_period,
 34         add_months(to_date('01/04/1993','DD/MM/YYYY'),12*(period+1))-1 end_period,
 35         count(*) mt,
 36         sum(end_date-start_date+1) days
 37  from data
 38  group by team, period
 39  order by team, start_period
 40  /
      TEAM START_PERIO END_PERIOD          MT       DAYS
---------- ----------- ----------- ---------- ----------
         1 01-APR-1993 31-MAR-1994          2         10
           01-APR-1994 31-MAR-1995          1          5

         2 01-APR-1993 31-MAR-1994          3         12
           01-APR-1994 31-MAR-1995          1          3

         3 01-APR-1998 31-MAR-1999          1          1
           01-APR-1999 31-MAR-2000          1          4

         4 01-APR-1993 31-MAR-1994          1          2
           01-APR-1994 31-MAR-1995          1          3

         5 01-APR-1994 31-MAR-1995          1          5

         6 01-APR-1998 31-MAR-1999          1          1
           01-APR-1999 31-MAR-2000          1          4

         7 01-APR-1994 31-MAR-1995          1          5

         8 01-APR-1994 31-MAR-1995          1          5


13 rows selected.

Regards
Michel
icon12.gif  Re: Data between two dates in a 12-month period [message #250156 is a reply to message #248939] Sun, 08 July 2007 16:39 Go to previous message
ramisy2k
Messages: 150
Registered: April 2005
Senior Member
Dear Michel Cadot

Many thanks for your reply to my query.

In another partly similar scenario I was trying to extract number of free days between two consecutive days action each team involved in and the frequency with which each team goes at least six weeks (42 free days) between consecutive days action' and the average length of such breaks when they occur.

For example, a team played a match from 1 Apr 1993 to 5 April 1993. Then played another match from 10 June 1993 to 14 June 1993. The number of free days between the two consecutive days action in this situation are 70 days (April 6 to June 9). Converting 70 days into weeks would mean 10 weeks.

Just to make sure my question is clear and understandable, I elaborate it here:

I have a table

CREATE TABLE MATCHES
(MT_ID	NUMBER(4) CONSTRAINT MT_ID_PK PRIMARY KEY,
START_DATE	DATE,
END_DATE	DATE,
TEAM_A	NUMBER(2),
TEAM_B	NUMBER(2))

INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,RUNS) VALUES(1,'1 Apr 1993', '5 Apr 1993',1,2);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,RUNS) VALUES(2,'21 Apr 1993', '25 Apr 1993',2,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,RUNS) VALUES(3,'30 Mar 1994', '3 Apr 1994',4,2);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,RUNS) VALUES(4,'1 May 1994', '5 May 1994',8,1);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,RUNS) VALUES(5,'24 Nov 1994', '28 Nov 1994',5,7);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,RUNS) VALUES(6,'31 Mar 1999', '4 Apr 1999',3,6);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,RUNS) VALUES(7,'1 Apr 1999', '5 Apr 1999',7,8);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,RUNS) VALUES(8,'29 Mar 2000', '2 Apr 2000',3,6);
INSERT INTO MATCHES (MT_ID,START_DATE,END_DATE,TEAM_A,TEAM_B,RUNS) VALUES(9,'2 Jun 2000', '6 Jun 2000',4,5);

SELECT * FROM MATCHES
/

MT_ID  START_DATE    END_DATE   TEAM_A   TEAM_B
1      01-APR-93     05-APR-93       1        2

2      21-APR-93     25-APR-93       2        1
3      30-MAR-94     03-APR-94       4        2
4      01-MAY-94     05-MAY-94       8        1
5      24-NOV-94     28-NOV-94       5        7
6      31-MAR-99     04-APR-99       3        6
7      01-APR-99     05-APR-99       7        8
8      29-MAR-00     02-APR-00       3        6
9      02-JUN-00     06-JUN-00       4        5


Now, in order to extract the number of free days (when no match was in progress I wrote a query which showed me the "start of free days" and and "end of free days"
before/between each Match along with no. of free days between those two dates and converting those to weeks by divding the figure by 7.


My query is:

SELECT MT_ID, 
START_OF_FREE_DAYS, START_DATE-1 END_OF_FREE_DAYS, START_DATE, END_DATE, TEAM_A, TEAM_B, 
FREE_DAYS, to_char( trunc(FREE_DAYS/7)+mod(FREE_DAYS,7)/10,'990.0') WEEKS1
FROM 
(select MT_ID, lag(END_DATE, 1) over (order by MT_ID)+1 START_OF_FREE_DAYS,
 START_DATE, 
END_DATE, 
TEAM_A, TEAM_B, 
CASE 
WHEN DECODE(START_DATE-lag(START_DATE, 1) over (order by MT_ID)-1,-1,0,
            START_DATE-lag(START_DATE, 1) over (order by MT_ID)-1) <= 4 
  THEN 0 
WHEN DECODE(START_DATE-lag(START_DATE, 1) over (order by MT_ID)-1,-1,0,
            START_DATE-lag(START_DATE, 1) over (order by MT_ID)-1) > 4 
  THEN START_DATE-lag(END_DATE, 1) over (order by MT_ID)-1 END FREE_DAYS
FROM
MATCHES)
/


MT_ID  START_OF_FREE_DAYS  END_OF_FREE_DAYS  START_DATE END_DATE   TEAM_A  TEAM_B  FREE_DAYS  WEEKS1
1      -                   31-MAR-93         01-APR-93  05-APR-93  1       2       -          - 
2      06-APR-93           20-APR-93         21-APR-93  25-APR-93  2       1       15         2.1
3      26-APR-93           29-MAR-94         30-MAR-94  03-APR-94  4       2       338        48.2
4      04-APR-94           30-APR-94         01-MAY-94  05-MAY-94  8       1       27         3.6
5      06-MAY-94           23-NOV-94         24-NOV-94  28-NOV-94  5       7       202        28.6
6      29-NOV-94           30-MAR-99         31-MAR-99  04-APR-99  3       6       1583       226.1
7      05-APR-99           31-MAR-99         01-APR-99  05-APR-99  7       8       0          0.0
8      06-APR-99           28-MAR-00         29-MAR-00  02-APR-00  3       6       358        51.1
9      03-APR-00           01-JUN-00         02-JUN-00  06-JUN-00  4       5       60         8.4


This result shows the free days before the start of each match. Since there was no preceeding match before MT_ID = 1 so it did not showed any free days before this match becuase the free days are calculated by taking the difference between the "End date" of the preceding match and the "Start Date" of the current match. This is done by the "CASE statment" in my above query. For example, MT_ID = 1 ended on 05-APR-93 and the MT_ID = 2 started on 21-APR-93 so the 15 free days started from 6-APR-93 and ended 20-APR-93. If a match gets started when the previous match is still in progress then there would not be any free days between the two matches. That is why the free days column in MT_ID = 7 show 0 free days because the preious match (MT_ID = 6) was still in progress when the match 7 started on 01-Apr-99. This scenario is also handled by "CASE statment" in my query. But each team would only be involve in one match at a time and its next match would only start when the previous is finished.

The last column weeks1 converts the number of free days into weeks in the way that the figure after decimal shows the number of days. For exmaple, 2.1 weeks means two weeks and 1 days (15 days) and so on.

Now, the above query shows the free days gaps between each match by all teams. If I limit it to say TEAM = 7 and run my query:

SELECT MT_ID, 
START_OF_FREE_DAYS,
 
START_DATE-1 END_OF_FREE_DAYS, START_DATE, END_DATE, TEAM_A,
 TEAM_B,
FREE_DAYS, to_char( trunc(FREE_DAYS/7)+mod(FREE_DAYS,7)/10,'990.0') WEEKS1
FROM 
(select MT_ID, lag(END_DATE, 1) over (order by MT_ID)+1 START_OF_FREE_DAYS,
 START_DATE, 
END_DATE, 
TEAM_A, TEAM_B, 
CASE 
WHEN DECODE(START_DATE-lag(START_DATE, 1) over (order by MT_ID)-1,-1,0,
            START_DATE-lag(START_DATE, 1) over (order by MT_ID)-1) <= 4 
  THEN 0 
WHEN DECODE(START_DATE-lag(START_DATE, 1) over (order by MT_ID)-1,-1,0,
            START_DATE-lag(START_DATE, 1) over (order by MT_ID)-1) > 4 
  THEN START_DATE-lag(END_DATE, 1) over (order by MT_ID)-1 END FREE_DAYS
FROM
MATCHES
WHERE TEAM_A = 7 or TEAM_B = 7)
/

MT_ID  START_OF_FREE_DAYS  END_OF_FREE_DAYS  START_DATE END_DATE   TEAM_A  TEAM_B  FREE_DAYS  WEEKS1
5      -                   23-NOV-94         24-NOV-94  28-NOV-94  5       7       -          - 
7      29-NOV-94           31-MAR-99         01-APR-99  05-APR-99  7       8       1584       226.2


TEAM = 7 played only two matches and both matches have a gap of 1584 free days that is 226.2 weeks (226 weeks and 2 days). So interms

Now, I come to my desired reuirement:

I want to break these free days into the same 12-month periods, which you did in answer to my original question in this post last week. Also for each 12-month period from April 1, 1993 to March 31, 2001, I want to have the sum of total number of free days between each match played by each team in that period and the frequency with which each team goes at least six weeks (42 days free) between any two matches in each period.

If the free days overlaped a 12-month period then the number of free days that came till the end of the period break point would come into the preceding period and the remaining number of free days would come in the next 12-month period. For exmaple, a team played a match from 01-MAY-94 to 05-MAY-1994 and then played its next match on 01-APR-1999 to 05-APR-1999. So the number of free days between 06-MAY-1994 TO 31-MAR-1999 are 1791 days. The break up of 1791 days should be like this:

Period                       FREE_Days    Frequency of atleast six-weeks gap
1-Apr-1994 to 31-Mar-1995    330          1  
1-Apr-1995 to 31-Mar-1996    366          0
1-Apr-1996 to 31-Mar-1997    365          0
1-Apr-1997 to 31-Mar-1998    365          0
1-Apr-1998 to 31-Mar-1999    365          0
1-Apr-1999 to 31-Mar-2000    0            0
1-Apr-2000 to 31-Mar-2001    0            0


Secondly, in all scenarios, the frequency of atleast six-weeks between the two matches would only come in the period from where the six-week gap started, as in the example above.

So in view of the above, my desired output is as follows:

For Team ID = 1       

Period                       FREE_Days    Frequency of atleast six-weeks gap
1-Apr-1992 to 31-Mar-1993    0            0
1-Apr-1993 to 31-Mar-1994    355          1
1-Apr-1994 to 31-Mar-1995    30           0
1-Apr-1995 to 31-Mar-1996    0            0
1-Apr-1996 to 31-Mar-1997    0            0  
1-Apr-1997 to 31-Mar-1998    0            0
1-Apr-1998 to 31-Mar-1999    0            0
1-Apr-1999 to 31-Mar-2000    0            0
1-Apr-2000 to 31-Mar-2001    0            0
TOTAL                        385          1



For Team ID = 2       

Period                       FREE_Days    Frequency of atleast six-weeks gap
1-Apr-1992 to 31-Mar-1993    0            0     
1-Apr-1993 to 31-Mar-1994    353          1
1-Apr-1994 to 31-Mar-1995    0            0
1-Apr-1995 to 31-Mar-1996    0            0
1-Apr-1996 to 31-Mar-1997    0            0  
1-Apr-1997 to 31-Mar-1998    0            0
1-Apr-1998 to 31-Mar-1999    0            0
1-Apr-1999 to 31-Mar-2000    0            0
1-Apr-2000 to 31-Mar-2001    0            0
TOTAL                        353          1


For Team ID = 3       

Period                       FREE_Days    Frequency of atleast six-weeks gap
1-Apr-1992 to 31-Mar-1993    0            0     
1-Apr-1993 to 31-Mar-1994    0            0
1-Apr-1994 to 31-Mar-1995    0            0
1-Apr-1995 to 31-Mar-1996    0            0
1-Apr-1996 to 31-Mar-1997    0            0  
1-Apr-1997 to 31-Mar-1998    0            0
1-Apr-1998 to 31-Mar-1999    0            0
1-Apr-1999 to 31-Mar-2000    359          1
1-Apr-2000 to 31-Mar-2001    0            0
TOTAL                        359          1



For Team ID = 4       

Period                       FREE_Days    Frequency of atleast six-weeks gap
1-Apr-1992 to 31-Mar-1993    0            0     
1-Apr-1993 to 31-Mar-1994    0            0
1-Apr-1994 to 31-Mar-1995    362          1
1-Apr-1995 to 31-Mar-1996    366          0
1-Apr-1996 to 31-Mar-1997    365          0  
1-Apr-1997 to 31-Mar-1998    365          0
1-Apr-1998 to 31-Mar-1999    365          0
1-Apr-1999 to 31-Mar-2000    366          0
1-Apr-2000 to 31-Mar-2001    62           0
TOTAL                        2251        1


For Team ID = 5      

Period                       FREE_Days    Frequency of atleast six-weeks gap
1-Apr-1992 to 31-Mar-1993    0            0    
1-Apr-1993 to 31-Mar-1994    0            0
1-Apr-1994 to 31-Mar-1995    123          1
1-Apr-1995 to 31-Mar-1996    366          0
1-Apr-1996 to 31-Mar-1997    365          0  
1-Apr-1997 to 31-Mar-1998    365          0
1-Apr-1998 to 31-Mar-1999    365          0
1-Apr-1999 to 31-Mar-2000    366          0
1-Apr-2000 to 31-Mar-2001    62           0
TOTAL                        2012         1



For Team ID = 6      

Period                       FREE_Days    Frequency of atleast six-weeks gap
1-Apr-1992 to 31-Mar-1993    0            0     
1-Apr-1993 to 31-Mar-1994    0            0
1-Apr-1994 to 31-Mar-1995    0            0
1-Apr-1995 to 31-Mar-1996    0            0
1-Apr-1996 to 31-Mar-1997    0            0
1-Apr-1997 to 31-Mar-1998    0            0
1-Apr-1998 to 31-Mar-1999    0            0
1-Apr-1999 to 31-Mar-2000    359          1
1-Apr-2000 to 31-Mar-2001    0            0
TOTAL                        359          1


For Team ID = 7      

Period                       FREE_Days    Frequency of atleast six-weeks gap
1-Apr-1992 to 31-Mar-1993    0            0    
1-Apr-1993 to 31-Mar-1994    0            0
1-Apr-1994 to 31-Mar-1995    123          1
1-Apr-1995 to 31-Mar-1996    366          0
1-Apr-1996 to 31-Mar-1997    365          0  
1-Apr-1997 to 31-Mar-1998    365          0
1-Apr-1998 to 31-Mar-1999    365          0
1-Apr-1999 to 31-Mar-2000    0            0
1-Apr-2000 to 31-Mar-2001    0            0
TOTAL                        1584         1


For Team ID = 8      

Period                       FREE_Days    Frequency of atleast six-weeks gap
1-Apr-1992 to 31-Mar-1993    0            0    
1-Apr-1993 to 31-Mar-1994    0            0
1-Apr-1994 to 31-Mar-1995    330          1
1-Apr-1995 to 31-Mar-1996    366          0
1-Apr-1996 to 31-Mar-1997    365          0  
1-Apr-1997 to 31-Mar-1998    365          0
1-Apr-1998 to 31-Mar-1999    365          0
1-Apr-1999 to 31-Mar-2000    0            0
1-Apr-2000 to 31-Mar-2001    0            0
TOTAL                        1791         1


I hope my requirement would be clear and I would highly appreciate if you could write and an efficient query to achieve this. I want the output in the same format as I show here, including the 0 lines.

Kind Regards
Ramis

[Updated on: Sun, 08 July 2007 23:20] by Moderator

Report message to a moderator

Previous Topic: How many execution plans Oracle generates for a query?
Next Topic: Armstrong numbers
Goto Forum:
  


Current Time: Fri Dec 09 07:44:37 CST 2016

Total time taken to generate the page: 0.08590 seconds