| Subject | RE: [firebird-support] Consecutive values from different fields |
|---|---|
| Author | Autoneer |
| Post date | 2019-01-24T07:50:37Z |
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' myautoneer@...
[firebird-support]:
>Recursive query that first selects the last day and then keeps selecting
> 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
>
the day before, and count, until a non-driving day is found?
Regards,
Kjell
[Non-text portions of this message have been removed]