CODEWITHSUNDEEP
sqlsql-servert-sql
Chris Nevill
Chris Nevill

Reputation: 6151

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

+---------------+------------+
|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             |
+---------------+------------+------------------+

Upvotes: 0

Views: 360

Answers (3)

sticky bit
sticky bit

Reputation: 37472

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

Upvotes: 1

Tyron78
Tyron78

Reputation: 4187

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.

Upvotes: 1

Thom A
Thom A

Reputation: 95554

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);

Upvotes: 1

Related Questions