How to Find N Consecutive Records in a Table Using Sql

How to find N Consecutive records in a table using SQL

You need to match your table against itself, as if there where 2 tables. So you use two aliases, o1 and o2 to refer to your table:

SELECT DISTINCT o1.customer, o1.product, o1.datum, o1.sale
  FROM one o1, one o2
  WHERE (o1.datum = o2.datum-1 OR o1.datum = o2.datum +1)
  AND o1.sale = 'NO' 
  AND o2.sale = 'NO'; 
 customer | product |   datum    | sale 
----------+---------+------------+------
 X        | A       | 2010-01-03 | NO
 X        | A       | 2010-01-04 | NO
 X        | A       | 2010-01-06 | NO
 X        | A       | 2010-01-07 | NO
 X        | A       | 2010-01-08 | NO

Note that I performed the query on an postgresql database - maybe the syntax differs on ms-sql-server, maybe at the alias 'FROM one AS o1' perhaps, and maybe you cannot add/substract in that way.

How to find recent N consecutive records in a table using SQL

The problem could be divided into three parts and could be solved progressively.

1 - Get most recent success result dates for each job_name, so that jobs after that dates could be fetched.

SELECT j2.job_name, max(j2.created_on) mdate 
FROM jobs j2 
WHERE j2.result = 'success' 
GROUP BY j2.job_name

2 - Get all rows that have failed status and whose created_by is after last successful job.

WITH last_success_date AS (
  SELECT j2.job_name, max(j2.created_on) mdate FROM jobs j2
    WHERE j2.result = 'success' 
    GROUP BY j2.job_name
) 
SELECT j.* FROM jobs j
LEFT JOIN last_success_date lsd ON j.job_name = lsd.job_name
WHERE j.result = 'fail' AND j.created_on > lsd.mdate

3- Get only results that have more than n successive failed results

WITH failed_jobs AS (
  WITH last_success_date AS (
    SELECT j2.job_name, max(j2.created_on) mdate FROM jobs j2
      WHERE j2.result = 'success' 
      GROUP BY j2.job_name
  ) 
  SELECT j.* FROM jobs j
  LEFT JOIN last_success_date lsd ON j.job_name = lsd.job_name
  WHERE j.result = 'fail' AND j.created_on > lsd.mdate
) 
SELECT fj.* FROM failed_jobs fj
LEFT JOIN (
  SELECT fj2.job_name, count(fj2.id) fail_count FROM failed_jobs fj2
  GROUP BY  fj2.job_name
) AS with_counts on with_counts.job_name = fj.job_name 
WHERE fail_count >= 2 ORDER BY fj.created_on DESC

I used n=2 in above example.

db fiddle link

How to find/select consecutive records (occurring at least twice) in a table using SQL

Check the previous sold is 'N' and the previous date is from yesterday:

with cte as
 (
   select
      product
     ,date
     ,sold
     -- previous "sold"
     ,lag(sold) over (partition by product order by date) as prev_sold
     -- previous "date" truncating the time portion
     ,cast(lag(date) over (partition by product order by date) as date) as prev_date
   from myTable
 )
select *
from cte
where sold = 'N'
  and prev_sold = 'N'
  and cast(date as date)= dateadd(day, 1, prev_date)

SQL: Check if n consecutive records are greater than some value

You can do this using lag:

select t.*
from (select t.*,
             lag(val, 1) over (order by id) as val_1,
             lag(val, 2) over (order by id) as val_2
      from t
     ) t
where val > 20 and val_1 > 20 and val_2 > 20;

This returns the first row that is part of each three-some. If you just want true/false:

select (case when count(*) > 0 then 'true' else 'false' end)
from (select t.*,
             lag(val, 1) over (order by id) as val_1,
             lag(val, 2) over (order by id) as val_2
      from t
     ) t
where val > 20 and val_1 > 20 and val_2 > 20;

EDIT:

I missed the part about not wanting more than 3. So, you can enhance this:

select (case when count(*) > 0 then 'true' else 'false' end)
from (select t.*,
             lag(val, 1) over (order by id) as val_1,
             lag(val, 2) over (order by id) as val_2,
             lag(val, 3) over (order by id) as val_3,
             lead(val, 1) over (order by id) as val_next_1
      from t
     ) t
where (val_3 <= 20 or val_3 is null) and
      (val_2 > 20 and val_1 > 20 and val > 20) and
      (val_next_1 <= 20 or val_next_1 is null);

It is a little tricky because the values can be at the beginning or end of the rows.

Count Number of Consecutive Occurrence of values in Table

One approach is the difference of row numbers:

select name, count(*) 
from (select t.*,
             (row_number() over (order by id) -
              row_number() over (partition by name order by id)
             ) as grp
      from t
     ) t
group by grp, name;

The logic is easiest to understand if you run the subquery and look at the values of each row number separately and then look at the difference.

how to write a query to get records with n consecutive dates

With EXISTS:

select t.* from tablename t
where t.[Hours] = 0
and exists (
  select 1 from tablename
  where [Job Id] = t.[Job Id] and [Hours] = 0 
  and abs(datediff(day, [WeekEndingDate], t.[WeekEndingDate])) = 7
)

See the demo.

Results:

> Job Id | Hours | WeekEndingDate     
> -----: | ----: | :----------
>      3 |     0 | 25/10/2019 
>      3 |     0 | 18/10/2019 

MySQL - return all records that have n amount of consecutive records after that match criteria

I'm sure there's a way to do what you want with SQL. I have an example of stored procedure you could use.

DELIMITER //

DROP PROCEDURE IF EXISTS GetTimeSlots;

CREATE PROCEDURE GetTimeSlots(NoOfSlots int)
BEGIN

    declare finished bool default false;

    -- stores id, slot and available from the cursor
    declare v_id int;
    declare v_slot text;
    declare v_available int;

    -- stores how many slots were found consecutively
    declare v_slotsfound int;

    -- loop through all available records
    declare c cursor for
    select id, slot, available from test where available = 1;

    declare continue handler for not found
    set finished = true;

    -- create empty table to store slots we find
    drop temporary table if exists tmp;
    create temporary table tmp
    select * from test where 1=2;

    open c;
    looper: loop
        fetch c into v_id, v_slot, v_available;
        if finished then
            leave looper;
        end if;

        select sum(case
                   when available then 1
                   else 0
                   end) into v_slotsfound
        from (
            -- list out as many slots you need
            -- outer query will total up the # of available slots
            select available from test where id >= v_id
            order by id
            limit NoOfSlots
        ) t;

        -- if # of available slots equals how many you want, store data
        -- in temporary table
        if v_slotsfound = NoOfSlots then
            insert into tmp values (v_id, v_slot, v_available);
        end if;

    end loop looper;
    close c;

    -- output the results
    select * from tmp;
    drop temporary table if exists tmp;

END //

DELIMITER ;

Run it

mysql> call gettimeslots(3);
+------+--------+-----------+
| id   | slot   | available |
+------+--------+-----------+
|    0 | 5.00pm |         1 |
|    4 | 5.40pm |         1 |
|    5 | 5.50pm |         1 |
+------+--------+-----------+

If you want 2 consecutive slots:

mysql> call gettimeslots(2);
+------+--------+-----------+
| id   | slot   | available |
+------+--------+-----------+
|    0 | 5.00pm |         1 |
|    1 | 5.10pm |         1 |
|    4 | 5.40pm |         1 |
|    5 | 5.50pm |         1 |
|    6 | 6.00pm |         1 |
+------+--------+-----------+

If you want 4 consecutive slots:

mysql> call gettimeslots(4);
+------+--------+-----------+
| id   | slot   | available |
+------+--------+-----------+
|    4 | 5.40pm |         1 |
+------+--------+-----------+

Data used

create table test (id int, slot text, avaialable int);
insert into test values
(0,    '5.00pm', 1)
,(1,    '5.10pm',  1)   
,(2,    '5.20pm',  1)
,(3,    '5.30pm',  0)
,(4,    '5.40pm',  1)
,(5,    '5.50pm',  1)
,(6,    '6.00pm',  1)
,(7,    '6.10pm',  1);

---

Related Topics