You really need to provide some test data ie if 3 consequtive days have value 1 then does that count as 2 or 3? Anyway this might start you off (it assumes your dates are straight dates and don't have times) :
from MyTable t1, MyTable t2
where t1.date > START_DATE
and t1.date <= END_DATE
and t1.value = 1
and t2.date = DATE_ADD( t1.date, interval -1 day )
and t2.value = 1
i can give you all info: because my first question was simplyfied.
I am making a page for my footballteam. I want to take out info on the following: Number of victory after another,This means that kamp.malmsil (goals for my team) must be larger than kamp.malmot(goal against). I must also have the date when the vicroties started and when it stopped.
$query = "
INNER JOIN lag ON lag.lid = kamp.lag_lid
INNER JOIN sesong ON kamp.sesid = sesong.sesid
LEFT JOIN klubb ON lag.lid = klubb.klid;";
"number of victory after another" is called winning streak
generally, stats are often kept just for streak, so a value of 3L would mean that the team's last three results were losses, while 5W would mean the last five games were wins (in each case, the game before that was the opposite, a win or a loss)
regarding your table relationships...
1) how many lag.lid for each kamp.lag_lid?
2) how many sesong.sesid for each kamp.sesid?
3) how many klubb.klid for each lag.lid?
Hi, if there is 3 consequetive days, is will count as 3. The date also includes time
If you had a winning streak of 3 followed by another one of 4 would that be 7 for that time period? or are you looking for the longest winning streak ie 4?
PS1 Your field names read like Greek to me but I assume dato is the date
PS2 I just read your the part where you explain the fields
PS3 having now just finished a bottle of wine I feel incapable of understanding how many klubb.klid go with each lag.lid
I think this works but I can't test it on my side. What language are we working with here? Do I get a prize?
select max(streak) + sign( max(streak) )
select count(*) as streak
from kamp t1, kamp t2
where t1.dato > START_DATO
and t1.dato <= END_DATO
and t1.value = 1
and t1.malmsil > t1.malmot
and date( t2.dato ) = DATE_ADD( date( t1.dato ), interval -1 day )
and t2.malmsil > t2.malmot