You could use a stored procedure
SET TERM ^ ;
CREATE OR ALTER PROCEDURE CONSECUTIVE_DAYS (ipITEMNUMBER CHAR(3))
RETURNS (opDATEFROM DATE, opDATETO DATE, opCONSECUTIVEDAYS INTEGER)
AS
DECLARE VARIABLE vItem CHAR(3);
DECLARE VARIABLE vDate DATE;
BEGIN
opCONSECUTIVEDAYS = 0;
-- get latest day used or pass in as parameter
SELECT FIRST 1 DATEUSED
FROM ITEM_USED
WHERE ITEMNR = :ipITEMNUMBER
ORDER BY DATEUSED DESC
INTO :opDATETO;
FOR
SELECT ITEMNR, DATEUSED
FROM ITEM_USED
WHERE ITEMNR = :ipITEMNUMBER
GROUP BY ITEMNR, DATEUSED
ORDER BY DATEUSED DESC
INTO :vItem, :vDate
DO
BEGIN
IF (vDate = opDATEFROM -1) THEN
opCONSECUTIVEDAYS = opCONSECUTIVEDAYS + 1;
IF (vDate < opDATEFROM -1) THEN
BREAK;
opDATEFROM = vDate;
END
IF ((opCONSECUTIVEDAYS > 0) OR ((opDATEFROM = opDATETO) AND (opDATEFROM
IS NOT NULL))) THEN
opCONSECUTIVEDAYS= opCONSECUTIVEDAYS + 1;
SUSPEND;
END ^
SET TERM ; ^
SELECT opDATEFROM, opDATETO, opCONSECUTIVEDAYS
FROM CONSECUTIVE_DAYS ('abc');
On 2 March 2017 at 17:36, 'Stef' [email protected] [firebird-support] <
[email protected]> wrote:
>
>
> Good day,
>
> I have a question for you SQL boffins, how can I query a table to get the
> count of consecutive days found ?
>
> A Table contains rows with dates where an item(s) has been added for every
> day this item was used.
>
> i.e.
>
> itemnr dateused
>
> abc 2017/02/01
>
> abc 2017/02/02
>
> abc 2017/02/25
>
> abc 2017/02/25
>
> abc 2017/02/25
>
> abc 2017/02/26
>
> abc 2017/02/27
>
> abc 2017/02/28
>
> abc 2017/03/01
>
> abc 2017/03/02
>
> The result from above sample should be 6 as there is a break between
> 2017/02/02 and 2017/02/25?
>
> Regards
>
> Stef
>
> [Non-text portions of this message have been removed]
>
>
>
