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

Question

I have a SQL table like Below

Code Name    DayStatus  Date
101  John    A          20-May-2018
101  John    A          19-May-2018
101  John    A          18-May-2018
102  Karl    A          20-May-2018
102  Karl    A          19-May-2018
102  Karl    P          18-May-2018
103  Lorem   P          20-May-2018
103  Lorem   A          19-May-2018
103  Lorem   A          18-May-2018
104  Ipsum   A          20-May-2018
104  Ipsum   P          19-May-2018
104  Ipsum   A          18-May-2018
105  Milton  A          20-May-2018
105  Milton  A          19-May-2018
105  Milton  A          18-May-2018
107  Saleh   A          20-May-2018
107  Saleh   A          19-May-2018
107  Saleh   W          18-May-2018
107  Saleh   A          17-May-2018
108  Virat   A          20-May-2018
108  Virat   H          19-May-2018
108  Virat   A          18-May-2018
108  Virat   A          17-May-2018

Here "A" stands for Absent, "P" stands for the present, "H" is stand for Holiday, "W" stands for the weak off.

From the table, I need to figure out the employees who are consecutively absent for 3 days and It only calculable when employees today's day status is A(absent).

For virat and saleh it will also count as consecutive Absent.But if P(present) will appear in between consecutive day's then it not count as consecutive absent.

The expected output should be---

Code Name    
101  John          
105  Milton          
107  Saleh             
108  Virat       

Answer 1

Try a query from this demo

SELECT code, name, count(*) absent_days
FROM (
  SELECT *,
        sum( xx ) over (partition by code order by date ) ss
  FROM (
    SELECT *,
          case when DayStatus = lag(DayStatus) over(partition by code order by Date)
               then 0 else 1
          end as xx
    FROM table1
    WHERE DayStatus not in( 'W','H')
  ) x
) y
WHERE DayStatus = 'A'
GROUP BY code, name, ss
HAVING count(*) >=3
order by code

---

| code |   name |     absent_days |
|------|--------|-----------------|
|  101 |   John |               3 |
|  105 | Milton |               3 |
|  107 |  Saleh |               3 |
|  108 |  Virat |               3 |

---

This version gives a count of days and a beginning date of each period

    SELECT code, name, count(*) absent_days, min(date) from_date
    FROM (
      SELECT *,
            sum( xx ) over (partition by code order by date ) ss
      FROM (
        SELECT *,
              case when DayStatus = lag(DayStatus) over(partition by code order by Date)
                   then 0 else 1
              end as xx
        FROM table1
        WHERE DayStatus not in( 'W','H')
      ) x
    ) y
    WHERE DayStatus = 'A'
    GROUP BY code, name, ss
    HAVING count(*) >=3
    order by code

| code |   name | absent_days |            from_date |
|------|--------|-------------|----------------------|
|  101 |   John |           3 | 2018-05-18T00:00:00Z |
|  105 | Milton |           3 | 2018-05-18T00:00:00Z |
|  107 |  Saleh |           3 | 2018-05-17T00:00:00Z |
|  108 |  Virat |           3 | 2018-05-17T00:00:00Z |