I need to build a query to check if there are records in the database of 6 consecutive 'Sundays'

SELECT DISTINCT ST1.DATAPU, ST1.NUMCAD, TO_CHAR(ST1.DATAPU, 'DAY') AS DIA
  FROM SENIOR.R066SIT ST1
 WHERE ST1.DATAPU BETWEEN '01/01/22' AND '23/11/22'
   AND ST1.NUMCAD = 10
   AND TO_CHAR(ST1.DATAPU, 'FMDAY') = 'DOMINGO' -->which is SUNDAY in English
 ORDER BY ST1.DATAPU ASC

With this query above, I get the result of the records as shown in the image below

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row pattern analysis:

SELECT *
FROM   (
  SELECT DISTINCT
         TRUNC(DATAPU) AS datapu,
         NUMCAD,
         TO_CHAR(DATAPU,'DAY') AS DIA
  FROM   SENIOR.R066SIT
  WHERE  DATAPU BETWEEN DATE '2022-01-01' AND DATE '2022-11-23'
  AND    NUMCAD = 10
  AND    TRUNC(DATAPU) - TRUNC(DATAPU, 'IW') = 6 -- Sunday
)
MATCH_RECOGNIZE(
  ORDER BY datapu
  ALL ROWS PER MATCH
  PATTERN (first_week consecutive_week{5,})
  DEFINE
    consecutive_week AS PREV(datapu)   INTERVAL '7' DAY = datapu
)

Which, for the sample data:

CREATE TABLE senior.r066sit(numcad, datapu) AS
SELECT 10, DATE '2022-01-01'   LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 5*7
UNION ALL
SELECT 10, DATE '2022-04-01'   LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 7*7
UNION ALL
SELECT 10, DATE '2022-08-01'   LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 7*7;

Outputs:

Before Oracle 12, you can use multiple analytic functions in nested sub-queries:

SELECT datapu, numcad,
       TO_CHAR(datapu, 'fmDAY') AS dia
FROM   (
  SELECT datapu, numcad,
         COUNT(*) OVER (PARTITION BY grp) AS grp_size
  FROM   (  
    SELECT datapu, numcad,
           SUM(consecutive) OVER (ORDER BY datapu) AS grp
    FROM   (
      SELECT datapu, numcad,
             CASE datapu - LAG(datapu) OVER (ORDER BY datapu)
             WHEN 7
             THEN 0
             ELSE 1
             END AS consecutive
      FROM   (
        SELECT DISTINCT
               TRUNC(DATAPU) AS datapu,
               NUMCAD
        FROM   SENIOR.R066SIT
        WHERE  DATAPU BETWEEN DATE '2022-01-01' AND DATE '2022-11-23'
        AND    NUMCAD = 10
        AND    TRUNC(DATAPU) - TRUNC(DATAPU, 'IW') = 6 -- Sunday
      )
    )
  )
)
WHERE  grp_size >= 6;

fiddle