coder - 1 year ago 34

SQL Question

I have table with two columns, one int and another varchar column

`SeqId status`

int varchar(50)

The status column has 10 statuses, lets say status1, status2, status3, ... status10

I want to write a query to find range of SeqId where status7, status8, status9 count(*) is zero.

Table data,

`SeqId Status`

1 status1

2 status2

3 status3

4 status4

5 status5

6 status6

7 status7

8 status8

9 status9

10 status10

11 status1

12 status2

13 status3

14 status4

15 status5

16 status9

17 status2

18 status7

19 status3

20 status5

......

1000 status6

My 1st desired output

When I use range between 15 and 20,

Incorrect query where right now zeroes are not included,

`Select status, count(*)`

from table1

where seqId between 15 and 20

group by status

`**Status Count**`

status1 0

status2 1

status3 1

status4 0

status5 2

status6 0

status7 1

status8 0

status9 1

status10 0

Next if possible, I want to find a range where all these 3 statuses, 8, 9, and 10, are zero.

Answer

You can use a `case`

statement to turn this into a boolean problem. So:

```
select t.*, (case when status in ('status8', 'status9', 'status10') then 0 else 1 end) as flag
from t;
```

You now want to find the longest sequence of zeros. This is a gaps-and-islands problem. One solution is a difference of row numbers to define groups:

```
select top 1 min(id), max(id), count(*) as length
from (select t.*,
(row_number() over (order by id) - row_number() over (partition by flag order by id)) as grp
from (select t.*, (case when status in ('status8', 'status9', 'status10') then 0 else 1 end) as flag
from t
) t
) t
where flag = 0
group by grp, flag
order by count(*) desc;
```

Source (Stackoverflow)