CODEWITHSUNDEEP
sqlsql-server
Sandro21
Sandro21

Reputation: 311

How do I check if a certain value exists?

I have a historization table called CUR_VALID. This table looks something like this:

ID  CUR_VALID
1   N
1   N
1   Y
2   N
2   Y
3   Y

For every ID there needs to be one Y. If there is no Y or multiple Y there is something wrong. The statment for checking if there are multiple Y I already got. Now I only need to check for every ID if there is one Y existing. Im just not sure how to do that. This is what I have so far. So how do I check if the Value 'Y' exists?

SELECT Count(1) [Number of N]
      ,MAX(CUR_VALID = 'N')
      ,[BILL_ID]
      ,[BILL_MONTH]
      ,[BILL_SRC_ID]
 FROM db.dbo.table
  GROUP BY [BILL_ID]
      ,[BILL_MONTH]
      ,[BILL_SRC_ID]
 Having MAX(CUR_VALID = 'N') > 1

Upvotes: 0

Views: 101

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Why are you fiddling with 'N' when you are interested in 'Y'?

Use conditional aggregation to get the count of the value your are interested in.

SELECT 
  COUNT(*) AS number_of_all,
  COUNT(CASE WHEN cur_valid = 'Y' THEN 1 END) AS number_of_y,
  COUNT(CASE WHEN cur_valid = 'N' THEN 1 END) AS number_of_n,
  bill_id,
  bill_month,
  bill_src_id,
FROM db.dbo.table
GROUP BY bill_id, bill_month, bill_src_id;

Add a HAVING clause in order to get only valid

HAVING COUNT(CASE WHEN cur_valid = 'Y' THEN 1 END) = 1

or invalid

HAVING COUNT(CASE WHEN cur_valid = 'Y' THEN 1 END) <> 1

bills.

Upvotes: 2

vishwarajanand
vishwarajanand

Reputation: 1071

As I understand, you want to get all the id for which your integrity check passes. And integrity check for you means, there is only one row with CUR_VALID value equal to Y in the CUR_VALID table.

This can be achieved by a group by clause:

select id from CUR_VALID 
where CUR_VALID.CUR_VALID = 'Y'
group by id
having count(CUR_VALID.CUR_VALID) = 1;

Upvotes: 1

Taha Paksu
Taha Paksu

Reputation: 15616

This query returns both not having at least one 'Y' value and more than one 'Y' value ID's.

First, sum up the Y values and relate to each id, then select not 1 ones from that table.

select * from (
    select ID, SUM(case when CUR_VALID = 'Y' then 1 else 0 end) as CNT 
    from table
    group by ID
) b where b.CNT <> 1

DBFiddle

Upvotes: 1

Allan
Allan

Reputation: 12456

The following query will give you the list of id for which your integrity condition is not met: For every ID there needs to be one Y. If there is no Y or multiple Y there is something wrong.

select T1.id from table T1 where (select count(*) from table T2 where T2.id=T1.id and T2.CUR_VALID='Y')!=1

Upvotes: 1

Related Questions