CODEWITHSUNDEEP
sqlsql-serverazurefilter
ami_27
ami_27

Reputation: 15

SQL get data containing 1 type only

I want to get data if R1 has all 1 or R2 has all 1 by date.

Input:

Date R1 R2
26-7-2021 1 1
26-7-2021 0 1
26-7-2021 1 0
27-7-2021 1 0
27-7-2021 1 0
28-7-2021 0 1
28-7-2021 0 1

Result:

Date R1 R2
27-7-2021 1 0
27-7-2021 1 0
28-7-2021 0 1
28-7-2021 0 1

Upvotes: 1

Views: 108

Answers (3)

forpas
forpas

Reputation: 164174

Use MIN() window function to get the rows that have only 1s in either R1 or R2 for a specific date:

SELECT Date, R1, R2 
FROM (
  SELECT *,
         MIN(R1) OVER (PARTITION BY Date) min_r1,
         MIN(R2) OVER (PARTITION BY Date) min_r2
  FROM tablename
) t
WHERE 1 IN (min_r1, min_r2)

See the demo.

Upvotes: 0

Charlieface
Charlieface

Reputation: 72238

You can use a windowed conditional count:

SELECT
  t.Date,
  t.R1,
  t.R2
FROM (
    SELECT *,
        CountZeroR1 = COUNT(CASE WHEN t.R1 = 0 THEN 1 END)
             OVER (PARTITION BY CAST(t.Date AS date)),
        CountZeroR2 = COUNT(CASE WHEN t.R2 = 0 THEN 1 END)
             OVER (PARTITION BY CAST(t.Date AS date))
    FROM YourTable t
) t
WHERE t.CountZeroR1 = 0 OR t.CountZeroR2 = 0;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270793

Why would you return 4 rows? Why not just return the dates where this occurs?

select date, min(r1), min(r2)
from t
group by date
having min(r1) = max(r1) and min(r2) = max(r2);

Upvotes: 1

Related Questions