>I have a question if you can help me PLEASE. > >Using a Firebird SQL query is it possible to count the consecutive number of matching values in different fields? >I need to determine the LAST consecutive days a driver has been working. > >DPID WEEKDATE DRIVERNR DRIVER D1 D2 D3 D4 D5 D6 D7 >83145 12.11.2018 697 JOHN SMITH DRIVING >83290 19.11.2018 697 JOHN SMITH DRIVING LEAVE LEAVE LEAVE LEAVE LEAVE >83435 26.11.2018 697 JOHN SMITH DRIVING >84160 31.12.2018 697 JOHN SMITH DRIVING DRIVING DRIVING DRIVING DRIVING DRIVING >84305 07.01.2019 697 JOHN SMITH DRIVING AWOL >84450 14.01.2019 697 JOHN SMITH DRIVING DRIVING >84595 21.01.2019 697 JOHN SMITH DRIVING DRIVING DRIVING DRIVING > >Using the data above my result should be 5
Sorry for replying a bit late. I fully agree with Lester. Normally, things are simpler if you have one occurrance per record rather than seven occurances per record. For any human it is easy to understand the concept of week and that D2 follows D1, D3 D2 etc, but that at the same time D1 of week2 follows D7 of week1. It is considerably harder to tell a computer the same thing. Anyway, your question also involves the consept of having to think of missing days (and weeks), something that is not straight forward in SQL regardless of whether your table is arranged in a way appropriate for spreadsheets (your suggestion) or databases (Lesters suggestion). Returning data that doesn't exist in the table is troublesome anyway. Hence, I'd go for an EXECUTE BLOCK (the 'on the fly' alternative to writing a stored procedure). This is at least an easy concept to understand. My suggestion below covers your spreadsheet way of having the table, it would have been considerably shorter if you'd used Lesters suggestion: execute block ( drivernr integer = :drivernr ) returns ( consecutivedays integer ) as declare variable d7 integer; declare variable d6 integer; declare variable d5 integer; declare variable d4 integer; declare variable d3 integer; declare variable d2 integer; declare variable d1 integer; declare variable wd1 date; declare variable wd2 date; declare variable started integer; begin started = 0; consecutivedays = 0; for select iif( d7 = 'DRIVING', 1, 0 ), iif( d6 = 'DRIVING', 1, 0 ), iif( d5 = 'DRIVING', 1, 0 ), iif( d4 = 'DRIVING', 1, 0 ), iif( d3 = 'DRIVING', 1, 0 ), iif( d2 = 'DRIVING', 1, 0 ), iif( d1 = 'DRIVING', 1, 0 ), "WEEKDATE" from StefvanderMerweTable where drivernr = :drivernr order by "WEEKDATE" desc into :d7, :d6, :d5, :d4, :d3, :d2, :d1, :wd2 do begin if ( ( started = 0 ) or ( wd2 = wd1 + 7 ) ) then begin wd1 = wd2 end else begin -- previous week without record for driver suspend; exit; end if ( d7 = 1 ) then begin consecutivedays = consecutivedays + 1; started = 1; end else if ( started = 1 ) then begin suspend; exit; end if ( d6 = 1 ) then begin consecutivedays = consecutivedays + 1; started = 1; end else if ( started = 1 ) then begin suspend; exit; end if ( d5 = 1 ) then begin consecutivedays = consecutivedays + 1; started = 1; end else if ( started = 1 ) then begin suspend; exit; end if ( d4 = 1 ) then begin consecutivedays = consecutivedays + 1; started = 1; end else if ( started = 1 ) then begin suspend; exit; end if ( d3 = 1 ) then begin consecutivedays = consecutivedays + 1; started = 1; end else if ( started = 1 ) then begin suspend; exit; end if ( d2 = 1 ) then begin consecutivedays = consecutivedays + 1; started = 1; end else if ( started = 1 ) then begin suspend; exit; end if ( d1 = 1 ) then begin consecutivedays = consecutivedays + 1; started = 1; end else if ( started = 1 ) then begin suspend; exit; end end suspend; --This suspend is probably only reached for fresh drivers that haven't yet worked after their first day off. end I wrote the execute block using Notepad which generally accepts syntax errors. Hence, there may be some errors for you to correct. HTH, Set
