Subject Re: [firebird-support] QUERY QUESTION
Author Si Carter
Post date 2017-03-04T07:37:04Z
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' stef@... [firebird-support] <firebird-support@yahoogroups. com> 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]