Subject RE: [firebird-support] Consecutive values from different fields
Author Autoneer

Hi Set

 

Thank you much appreciated, I would agree with Lester’s approach however this an inherited DB.

 

I tried your SQL and get the error below.

 

“Invalid token.

Dynamic SQL Error.

SQL error code = -104.

Token unknown - line 28, column 5.

end.”

 

 

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 DRIVERPLAN

  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    -- ERROR HERE

    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;

 

I have tried SQLFIDDLE and SQLTEST.NET but the only allow select statements

 

Any idea why this error would occur?

 

Regards

 

Stef

 

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 26 January 2019 16:58
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Consecutive values from different fields

 

 

>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