possible with a query to do this?

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

CONDITION:  if ExpireDate > Yesterday, then display even the archived as well
arthurh88Asked:
Who is Participating?
 
PortletPaulfreelancerCommented:
thanks, for "no matter what" then I suggest:

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

Open in new window

0
 
appariCommented:
Select * from table1 where (status not like 'archived' or ExpireDate > getdate()-1) and active = 1
0
 
chaauCommented:
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))

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

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
deviprasadgCommented:
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

Open in new window

0
 
ValentinoVBI ConsultantCommented:
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' 

Open in new window

will become
status <> 'archived

Open in new window

0
 
awking00Commented:
select * from table1 where status = 'archived' and active = 1
union
select * from table1 where status <> 'archived' and ExpireDate > getdate()-1;
0
 
awking00Commented:
select * from table1 where status = 'archived' and active = 1
union
select * from table1 where status <> 'archived' and ExpireDate > getdate()-1;
0
 
awking00Commented:
Sorry about the duplicate submission.
0
 
PortletPaulfreelancerCommented:
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)
     )
   )

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

Open 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?
0
 
arthurh88Author Commented:
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.
0
 
awking00Commented:
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;
0
 
PortletPaulfreelancerCommented:
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.
0
 
PortletPaulfreelancerCommented:
Hi, has you data selection question been resolved now?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.