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