SQL Select - bit field to represent checking all rows after condition returns true

Question

+---------------+------------+
|Processing_date|Contaminated|
+---------------+------------+
|2000/01/01     |   FALSE    |
|2000/01/02     |   TRUE     |
|2000/01/03     |   FALSE    |
|2000/01/04     |   FALSE    |
+---------------+------------+

I'm trying to figure out how to do a select where I have a column 'ToBeChecked' based on the contaminated column such that ToBeChecked is true for all following rows.

+---------------+------------+------------------+
|Processing_date|Contaminated|item_to_be_checked| 
+---------------+------------+------------------+
|2000/01/01     |   FALSE    |FALSE             |
|2000/01/02     |   TRUE     |TRUE              |
|2000/01/03     |   FALSE    |TRUE              |
|2000/01/04     |   FALSE    |TRUE              |
|2000/01/05     |   FALSE    |TRUE              |
+---------------+------------+------------------+

I figure I need to use something like Lag and/or Over

However I don't think I'm looking to partition the data and I can only get Lag to work on one row at present!

 SELECT Processing_date, LAG (Contaminated, 1, 0) OVER (ORDER BY processing_date ) as item_to_be_checked

Which yields:

+---------------+------------+------------------+
|Processing_date|Contaminated|item_to_be_checked| 
+---------------+------------+------------------+
|2000/01/01     |   FALSE    |FALSE             |
|2000/01/02     |   TRUE     |TRUE              |
|2000/01/03     |   FALSE    |TRUE              |
|2000/01/04     |   FALSE    |FALSE             |
|2000/01/05     |   FALSE    |FALSE             |
+---------------+------------+------------------+

What was the attempt you made, include it in the question.

Thom A
– Thom A ♦

2020-11-17 11:17:14 +00:00
Commented Nov 17, 2020 at 11:17 — Thom A
– Thom A ♦, Commented Nov 17, 2020 at 11:17
Now updated with lag / over attempt

Chris Nevill
– Chris Nevill

2020-11-17 11:22:22 +00:00
Commented Nov 17, 2020 at 11:22 — Chris Nevill
– Chris Nevill, Commented Nov 17, 2020 at 11:22

sticky bit · Accepted Answer · 2020-11-17 11:30:33Z

1

You can use the windowed version of max(). Unfortunately max() doesn't accept bits directly, so some casting to/from integers is needed.

SELECT processing_date,
       contaminated,
       convert(bit, max(convert(integer, contaminated)) OVER (ORDER BY processing_date)) item_to_be_checked
       FROM elbat;

db<>fiddle

answered Nov 17, 2020 at 11:30

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thom A · Accepted Answer · 2020-11-17 11:28:52Z

1

I would use a windowed COUNT, to check if there was a previous contamination:

SELECT V.Processing_date,
       V.Contaminated,
       CONVERT(bit,CASE WHEN COUNT(CASE Contaminated WHEN 1 THEN 1 END) OVER (ORDER BY Processing_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > 0 THEN 1 ELSE 0 END) AS item_to_be_checked
FROM (VALUES(CONVERT(date,'20000101'),CONVERT(bit,0)),
            (CONVERT(date,'20000102'),CONVERT(bit,1)),
            (CONVERT(date,'20000103'),CONVERT(bit,0)),
            (CONVERT(date,'20000104'),CONVERT(bit,0)))V(Processing_date,Contaminated);

answered Nov 17, 2020 at 11:28

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Comments

Tyron78 · Accepted Answer · 2020-11-17 11:29:54Z

So, if your decision if an item has to be rechecked, you might want to get the first conterminated date and then check wverything afterwards:

DECLARE @t TABLE(
  Processing_date DATE
 ,Contaminated BIT
)

INSERT INTO @t VALUES
('2000-01-01', 0)
,('2000-01-02', 1)
,('2000-01-03', 0)
,('2000-01-04', 0)


DECLARE @MinContermDate DATE = (SELECT MIN(Processing_date) FROM @t WHERE Contaminated = 1)

SELECT Processing_date
      ,CASE WHEN Contaminated = 1 THEN N'TRUE' ELSE N'FALSE' END AS Contaminated
      ,CASE WHEN t.Processing_date >= @MinContermDate THEN N'TRUE' ELSE N'FALSE' END AS item_to_be_checked
  FROM @t t

If you don't want to declare the variable at the beginning, you can put the corresponding query into a CTE and join to it.

Another possibility is UNBOUNDED PRECEDING:

SELECT Processing_date
      ,CASE WHEN Contaminated = 1 THEN N'TRUE' ELSE N'FALSE' END AS Contaminated
      ,CASE WHEN MAX(CASE WHEN Contaminated = 1 THEN 1 ELSE 0 END) OVER (ORDER BY t.Processing_date ROWS UNBOUNDED PRECEDING) = 1 THEN N'TRUE' ELSE N'FALSE' END AS item_to_be_checked
  FROM @t t

In this case you don`t need the CTE or the variable.

Collectives™ on Stack Overflow

SQL Select - bit field to represent checking all rows after condition returns true

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related