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: [email protected] [mailto:[email protected]] Sent: 24 January 2019 08:16 To: [email protected] Subject: Re: [firebird-support] Consecutive values from different fields Den 2019-01-23 kl. 11:53, skrev 'Autoneer' [email protected] [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]
