Hi - I have a database containing football results I want to do various things with. One is to create a sequence number for each set of football games in the calendar. A sequence will contain all matches played on 1. Friday, Saturday, Sunday or 2. Tuesday or Wednesday. In matchdate order the sequence should increment throughout the season. Other days can be ignored for the purpose of what I'm trying to achieve. The sql I'm running is:
select TO_CHAR(matchdate, 'DY') as DAY,matchdate,league,count(*)
FROM results
where league in ('E1','E2','E3','E4')
and matchdate between TO_DATE('01/07/2015', 'DD/MM/YYYY')
AND TO_DATE('15/06/2016', 'DD/MM/YYYY')
group by matchdate,league
order by matchdate
Which produces output similar to
DAY | MATCHDATE | LEAGUE | COUNT(*) |
---|---|---|---|
FRI | 07/08/2015 | E1 | 1 |
SAT | 08/08/2015 | E1 | 10 |
SAT | 08/08/2015 | E2 | 12 |
SAT | 08/08/2015 | E3 | 12 |
SUN | 09/08/2015 | E1 | 1 |
FRI | 14/08/2015 | E3 | 1 |
SAT | 15/08/2015 | E1 | 10 |
SAT | 15/08/2015 | E2 | 11 |
SAT | 15/08/2015 | E3 | 11 |
SUN | 16/08/2015 | E1 | 2 |
SUN | 16/08/2015 | E2 | 1 |
TUE | 18/08/2015 | E1 | 7 |
TUE | 18/08/2015 | E2 | 10 |
TUE | 18/08/2015 | E3 | 12 |
WED | 19/08/2015 | E1 | 4 |
WED | 19/08/2015 | E2 | 2 |
FRI | 21/08/2015 | E1 | 1 |
I would like it do contain the sequence number as follows:
Sequence | DAY | MATCHDATE | LEAGUE | COUNT(*) |
---|---|---|---|---|
1 | FRI | 07/08/2015 | E1 | 1 |
1 | SAT | 08/08/2015 | E1 | 10 |
1 | SAT | 08/08/2015 | E2 | 12 |
1 | SAT | 08/08/2015 | E3 | 12 |
1 | SUN | 09/08/2015 | E1 | 1 |
2 | FRI | 14/08/2015 | E3 | 1 |
2 | SAT | 15/08/2015 | E1 | 10 |
2 | SAT | 15/08/2015 | E2 | 11 |
2 | SAT | 15/08/2015 | E3 | 11 |
2 | SUN | 16/08/2015 | E1 | 2 |
2 | SUN | 16/08/2015 | E2 | 1 |
3 | TUE | 18/08/2015 | E1 | 7 |
3 | TUE | 18/08/2015 | E2 | 10 |
3 | TUE | 18/08/2015 | E3 | 12 |
3 | WED | 19/08/2015 | E1 | 4 |
3 | WED | 19/08/2015 | E2 | 2 |
4 | FRI | 21/08/2015 | E1 | 1 |
4 | SAT | 22/08/2015 | E1 | 11 |
4 | SAT | 22/08/2015 | E2 | 12 |
4 | SAT | 22/08/2015 | E3 | 12 |
5 | FRI | 28/08/2015 | E1 | 1 |
Hope that makes sense, any help appreciated or happy to provide more information if any questions
Thanks