| Subject | Consecutive values from different fields |
|---|---|
| Author | Autoneer |
| Post date | 2019-01-23T18:55:38Z |
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