CODEWITHSUNDEEP
sql
bob
bob

Reputation: 31

check data sequence in SQL

I have weekly data in columns. I need to exclude records where there are continuous null values for four week.

TempID Week1 Week 2 Week3 Week4 Week5 Week6 Week7
123 1 NULL NULL NULL NULL 4 4
234 3 5 5 6 NULL 4 4
333 4 NULL 4 8 NULL 4 4

For this example I need to exclude temp id 123. And rest need to be included.Basically exclude records where there is four continuous null records in columns.

How I can check the order I described here?

Upvotes: 0

Views: 416

Answers (2)

BobC
BobC

Reputation: 4424

Since you are dealing with four columns out of seven, there are really only 4 combinations to test for:

Select * from table
Where 
(week1 is null and week2 is null and week3 is null and week4 is null)
Or 
(week2 is null and week3 is null and week4 is null and week5 is null)
Or
(week3 is null and week4 is null and week5 is null and week6 is null)
Or
(week4 is null and week5 is null and week6 is null and week7 is null)

Upvotes: 0

astentx
astentx

Reputation: 6751

You may just repeat all the possible sequences in the where condition wrapped withcoalesce function: coalesce returns first non-null value, so if it returns null, then all the values are null.

select *
from yourtable
where coalesce(week1, week2, week3, week4) is null
  or coalesce(week2, week3, week4, week5) is null
  or coalesce(week3, week4, week5 week6) is null
  or coalesce(week4, week5 week6 week7) is null

Upvotes: 0

Related Questions