>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