possible with a query to do this?

Select * from table1 where (status not like 'archived' or ExpireDate > getdate()-1) and active = 1

if active applies to both then the query will be:

Select * from table1 where active = 1 and
(status not like 'archived' or status like 'archived' AND ExpireDate > Convert(DATETIME(CONVERT(VARCHAR(10), DATEADD(dd, -1, GetDate()), 121) + ' 00:00:00'), 121))

Select allOpen in new window

If active applies to NOT ARCHIVED only, then:

Select * from table1 where active = 1 and status not like 'archived' or 
status like 'archived' AND ExpireDate > Convert(DATETIME(CONVERT(VARCHAR(10), DATEADD(dd, -1, GetDate()), 121) + ' 00:00:00'), 121)

Select allOpen in new window

Select * from table1 where active = 1
AND 1 = 
CASE WHEN ExpireDate > CAST(GETDATE()-1 AS DATE)
THEN  1
WHEN status not like 'archived' THEN 1
ELSE 0
END

Select allOpen in new window

There's no reason to use the LIKE operator when you don't use any wildcards in your search string.  Use = or <> instead, it will be more efficient when you have a lot of data.

For instance:

status not like 'archived' 

Select allOpen in new window

will become

status <> 'archived

Select allOpen in new window

select * from table1 where status = 'archived' and active = 1
union
select * from table1 where status <> 'archived' and ExpireDate > getdate()-1;

select * from table1 where status = 'archived' and active = 1
union
select * from table1 where status <> 'archived' and ExpireDate > getdate()-1;

Sorry about the duplicate submission.

Judging by the replies I'd say we don't fully understand the question.

part 1
Select * from table1 where status not like 'archived' and active = 1

as observed earlier, there is no point in using "like" without a wildcard

part 2
CONDITION:  if ExpireDate > Yesterday, then display even the archived as well

I assume "Yesterday" starts at 00:00:00
what is not clear is: should "active = 1", or, "active" does not matter

/* for active = 1 always */
select * 
from table1 
WHERE actve = 1
AND (
        status <> 'archived' 
   OR 
     (
       status = 'archived' 
     AND 
       ExpireDate > DATEADD(day,DATEDIFF(day,0,GETDATE())-1,0)
     )
   )

Select allOpen in new window

/* for option where active does not always need to = 1 */
select * 
from table1 
WHERE
     (
        status <> 'archived' 
      AND
        active = 1 
      )
   OR 
     (
       status = 'archived' 
     AND 
       ExpireDate > DATEADD(day,DATEDIFF(day,0,GETDATE())-1,0)
     )

Select allOpen in new window

Note, truncating a datetime value to "00:00:00" in MSSQL has many variants, see:

How can I truncate a datetime in SQL Server?

im sorry for not being clear on "active=1".   My hope was, that if ExpireDate > Yesterday at 00:00:00 that we will display the record no matter what.  The ExpireDate trumps all other conditions to display records.  If we have not expired, then we want all the conditions.

Since union will eliminate any duplicates and you want all records where ExpireDate is later than yesterday -
select * from table1 where ExpireDate > getdate()-1
union
select * from table1 where status = 'archived' and active = 1;

yes, that last proposed union would meet the need - but at the expense of more query cost I susppect - (2 table scans plus merge).... would be interesting to see which is more effective.

Hi, has you data selection question been resolved now?