Way to Find Data of a SQL Table With Same Status for Consecutive 3 Days

Way to find data of a sql table with same status for consecutive 3 days

with cte as
 (
   select *
      -- cumulative Max, returns 0 as long as there's no P status
     ,max(CASE WHEN DayStatus = 'P' THEN 1 ELSE 0 END)
      over (partition by code
            order by date desc) as mx
      -- status of the latest date
     ,first_value(DayStatus)
      over (partition by code
            order by date desc) as fv
   from Table1
 )
select code, name, count(*) as absentDays
from cte
where fv = 'A'   -- current status = 'A'
  and mx = 0     -- all rows before the 1st 'P'
group by code, name
having
   -- at least three days absent
   count(*) >= 3;

See fiddle

This can easily be modified to work when there's not a row per code/day calculating date difference between max and min(date)

Find out the periods when at least 3 consecutive days of occurrence (of values)

You can do this calculation by assigning a grouping id to the dates. A simple method is to subtract a sequence of numbers -- the difference is constant for a sequence of days. Then aggregate by this number:

select min(daterange), max(daterange), count(*) as numdays
from (select t.*,
             dateadd(day,
                     - row_number() over (order by daterange),
                     daterange) as grp
      from t
     ) t
group by grp
having count(*) >= 3;

Calculate Number of Consecutive Days Where a Condition Applies Across Two Columns

Note that any gaps between dates will be treated as consecutive days.

with data as (
    select id, date,
        case when min(balance) >= 0 then 0 else 1 end as tally,
        sum(case when min(balance) >= 0 then 1 else 0 end)
            over (partition by id order by date) as grp
    from t
    group by id, date
)
select id, date,
    sum(tally) over (partition by id, grp, tally order by date) as running_days
from data
order by id, date;

To treat missing dates as nonconsecutive try:

    sum(case when min(balance) >= 0 then 1 else 0 end)
        over (partition by id order by date) +
    datediff(day, min(date) over (partition by id), date) -
    row_number() over (partition by id order by date) + 1 as grp

https://rextester.com/NKBZG48737

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

Find the people who are login 3 consecutive dates

Based on the specific sample data provided, you could use analytic min and max to get the first and last date for each name, count the difference in days and the number of logins which must be 3 with 2 days between first and last date.

You haven't specific a RDBMS so the date functions may need amending as appropriate, however all RDBMS support the same functionality.

select date, name
from (
    select *, 
      DateDiff(day,Min(date) over(partition by name),  
      Max(date) over(partition by name))diff, 
      Count(*) over(partition by name) qty
    from t
)t
where diff=2 and qty=3
order by date;

SQL query to find any consecutive integer in my amount field

Using modulo you can strip away any digits above the 10k position, then check the values are in an accepted list.

WHERE
  (amount % 10000) IN (1111.11, 2222.22, 3333.33, 4444.44, 5555.55, 6666.66, 7777.77, 8888.88, 9999.99)

Or...

WHERE
  (amount % 10000) / 1111.11 IN (1,2,3,4,5,6,7,8,9)

These avoid turning numbers in to strings, which is generally neither necessary nor prudent.

Count consecutive values once in TSQL

…

declare @t table(DeliveryId int, DeliveryDate date, Status varchar(10));

insert into @t(DeliveryId, DeliveryDate, Status)
values
(10, '2021-04-14', 'Success'),
(20, '2021-04-15', 'Failure'),
(30, '2021-04-16', 'Success'),
(40, '2021-04-17', 'Success'),
(70, '2021-04-18', 'Failure'),
(60, '2021-04-19', 'Failure'),
(50, '2021-04-20', 'Failure'),
(80, '2021-04-21', 'Success');


select *, 
case when Status='Failure' then min(grpDeliveryId) over(partition by grp) end as FailedDeliveryId,
case when Status='Failure' then datediff(minute, min(DeliveryDate) over(partition by grp), min(grpSuccessDate) over(partition by grp) ) end as MinutesDiffFailSuccess
from
(
select *, 
sum(addorcountme) over(order by DeliveryDate) as grp
from
(
select *, 
case when Status='Failure' and lag(Status) over(order by DeliveryDate)='Failure' then null else 1 end as addorcountme,
case when Status='Failure' and lag(Status) over(order by DeliveryDate)='Failure' then null else DeliveryId end as grpDeliveryId,
case when Status='Failure' and lead(Status) over(order by DeliveryDate)='Success' then lead(DeliveryDate) over(order by DeliveryDate) end as grpSuccessDate
from @t
) as t
) as g;



/*
select *, case when Status='Failure' then min(DeliveryId) over(partition by grp) end as FailedDeliveryId
from
(
select *, 
sum(sumorcountme) over(order by DeliveryDate) as grp
from
(
select *, case when Status='failure' and lag(Status) over(order by DeliveryDate)='Failure' then null else 1 end as sumorcountme
from @t
) as t
) as g; */

---

Related Topics