Re: [firebird-support] Consecutive values from different fields
Thank you already did that much appreciated
Re: [firebird-support] Consecutive values from different fields
Sure, just use: execute block returns ( consecutivedays integer ) as ... where drivernr = 697 Den man. 28. jan. 2019 kl. 08:54 skrev 'Autoneer' myauton...@gmail.com [firebird-support] : > > > Thank you Lester, Omacht and Set > > > > Sorry a simple oversight on my side it now works 100%. > > > > Can this be run without having the end user input the variable i.e. > :drivernr? > > > > I want to programmatically provide the driver nr as I only need the result > to populate a field in another table ? > > > > Regards > > > > Stef > > > > *From:* firebird-support@yahoogroups.com [mailto: > firebird-support@yahoogroups.com] > *Sent:* 27 January 2019 11:50 > *To:* firebird-support@yahoogroups.com > *Subject:* Re: [firebird-support] Consecutive values from different fields > > > > > > On 27/01/2019 08:03, 'Autoneer' myauton...@gmail.com [firebird-support] > wrote: > > Thank you much appreciated, I would agree with Lester’s approach however > > this an inherited DB. > > You need a ';' after the wd2 ... I think > > Before we have CTE functions it was common to create temporary tables to > reformat the data and create triggers to copy data from the 'legacy' > tables to ones that make reporting easier. I'm still using some of the > same legacy stuff 20 years on ;) Creating the SQL for a CTE query is > often easier if one 'designs' the table one actually needs and then work > out how to build it much like the execute block is doing ... > > -- > Lester Caine - G8HFL > - > Contact - https://lsces.co.uk/wiki/?page=contact > L.S.Caine Electronic Services - https://lsces.co.uk > EnquirySolve - https://enquirysolve.com/ > Model Engineers Digital Workshop - https://medw.co.uk > Rainbow Digital Media - https://rainbowdigitalmedia.co.uk > > > > >
RE: [firebird-support] Consecutive values from different fields
Thank you Lester, Omacht and Set Sorry a simple oversight on my side it now works 100%. Can this be run without having the end user input the variable i.e. :drivernr? I want to programmatically provide the driver nr as I only need the result to populate a field in another table ? Regards Stef From: firebird-support@yahoogroups.com [mailto:firebird-supp...@yahoogroups..com] Sent: 27 January 2019 11:50 To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Consecutive values from different fields On 27/01/2019 08:03, 'Autoneer' myauton...@gmail.com [firebird-support] wrote: > Thank you much appreciated, I would agree with Lester’s approach however > this an inherited DB. You need a ';' after the wd2 ... I think Before we have CTE functions it was common to create temporary tables to reformat the data and create triggers to copy data from the 'legacy' tables to ones that make reporting easier. I'm still using some of the same legacy stuff 20 years on ;) Creating the SQL for a CTE query is often easier if one 'designs' the table one actually needs and then work out how to build it much like the execute block is doing ... -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk
Válasz: [firebird-support] Consecutive values from different fields
Edit previous line, add semicolon at the end: wd1 = wd2; András Eredeti üzenet Feladó: "'Autoneer' myauton...@gmail.com [firebird-support]" Dátum: 2019. 01. 27. 9:04 (GMT+01:00) Címzett: firebird-support@yahoogroups.com Tárgy: RE: [firebird-support] Consecutive values from different fields 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-supp...@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 writ
RE: [firebird-support] Consecutive values from different fields
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-supp...@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 consi
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
Re: [firebird-support] Consecutive values from different fields
On 24/01/2019 07:50, 'Autoneer' myauton...@gmail.com [firebird-support] wrote: > I have never worked with a recursive cte before and have no idea on how > to go about this. Personally I'd start by reordering the data to a single field ... It's easy enough to DISPLAY a week at a time working from a table that has single day records. The one can more easily scan the data for 'consecutive days' and also make calculations on 'hours' so giving the right breaks between driving sessions. -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Consecutive values from different fields
On 24/01/2019 07:50, 'Autoneer' myauton...@gmail.com [firebird-support] wrote: > I have never worked with a recursive cte before and have no idea on how > to go about this. Personally I'd start by reordering the data to a single field ... It's easy enough to DISPLAY a week at a time working from a table that has single day records. The one can more easily scan the data for 'consecutive days' and also make calculations on 'hours' so giving the right breaks between driving sessions. -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] Consecutive values from different fields
Kjell The following is stated in the firebird documentation (https://firebirdsql.org/refdocs/langrefupd21-select.html) Aggregates (DISTINCT, GROUP BY, HAVING) and aggregate functions (SUM, COUNT, MAX etc) are not allowed in recursive union members. I have never worked with a recursive cte before and have no idea on how to go about this. Regards Stef From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 24 January 2019 08:16 To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Consecutive values from different fields Den 2019-01-23 kl. 11:53, skrev 'Autoneer' myauton...@gmail.com [firebird-support]: > > Good day all > > 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. > > i.e. my table > > *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 > Recursive query that first selects the last day and then keeps selecting the day before, and count, until a non-driving day is found? Regards, Kjell [Non-text portions of this message have been removed]
Re: [firebird-support] Consecutive values from different fields
Den 2019-01-23 kl. 11:53, skrev 'Autoneer' myauton...@gmail.com [firebird-support]: > > Good day all > > 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. > > i.e. my table > > *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 > Recursive query that first selects the last day and then keeps selecting the day before, and count, until a non-driving day is found? Regards, Kjell [Non-text portions of this message have been removed]
[firebird-support] Consecutive values from different fields
Good day all 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. i.e. my table 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 I thank you in advance. Regards from a sunny South Africa Stef van der Merwe