CODEWITHSUNDEEP
sqlsql-server
Ayman
Ayman

Reputation: 1463

Filter SQL Server records

I have a table called table1 in SQL Server 2008

It has the following data:

id    refId    Date          IsActive
=====================================
1     2        2014-03-01           1
2     2        2014-03-01           1
3     2        2014-04-15           0 <
4     2        2014-04-15           0 <
5     2        2014-05-20           1
6     2        2014-05-20           1
7     4        2014-03-01           1
8     4        2014-03-01           1
9     4        2014-04-15           1 <
10    4        2014-05-20           1

EDIT

refId refers to a person in another table. So I want the persons whose records does not have Date = 2014-04-15 OR They have Date = 2014-04-15 but the IsActive = 0

So according to the top, the output should be:

refId
=====
2

I can do this via MySQL using this query (EDIT 2):

SELECT refId 
FROM table1 
GROUP BY refId 
/*Check if there is no value with this date*/
HAVING MAX(Date='2014-04-15') = 0 
/*Check if the date exists but the IsActive flag is off*/
OR MAX(Date='2014-04-15' AND IsActive=0) = 1

but the problem is, SQL Server does not accept condition in the MAX() function.

Upvotes: 0

Views: 73

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If this is the query in MySQL:

SELECT refId 
FROM table1 
GROUP BY refId 
HAVING MAX(Date='2014-04-15') = 0 OR
       MAX(Date='2014-04-15' AND IsActive=0) = 1;

You can readily translate this to SQL Server/ANSI SQL syntax by using the case statement:

SELECT refId 
FROM table1 
GROUP BY refId 
HAVING MAX(CASE WHEN Date = '2014-04-15' THEN 1 ELSE 0 END) = 0 OR
       MAX(CASE WHEN Date = '2014-04-15' AND IsActive = 0 THEN 1 ELSE 0 END) = 1;

This query will also work in MySQL.

Upvotes: 1

dsanatomy
dsanatomy

Reputation: 533

SELECT DISTINCT refId 
FROM table1 


WHERE (Date!='2014-04-15') 

OR (Date='2014-04-15' AND IsActive=0)

Output:

        refId
        =====
        2

Upvotes: 0

attila
attila

Reputation: 2229

if you need to only include the refIds that meet your requirements, then this should work

select refId
from table1 as t
group by refId
having exists(
  select refId 
  from table1 as t2 
  where [Date]<>'2014-04-15' 
    or ([Date]='2014-04-15' and IsActive=0) 
  group by refId 
  having t.refId=t2.refId 
-- this next line is where we make sure we are only matching 
-- where the total number of records of refId is equal to the total match count
    and count(t.refId)=count(t2.refId)     )

Upvotes: 1

Related Questions