?
Solved

possible with a query to do this?

Posted on 2013-01-29
13
Medium Priority
?
136 Views
Last Modified: 2013-02-21
Select * from table1 where status not like 'archived' and active = 1

CONDITION:  if ExpireDate > Yesterday, then display even the archived as well
0
Comment
Question by:arthurh88
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 39

Expert Comment

by:appari
ID: 38833838
Select * from table1 where (status not like 'archived' or ExpireDate > getdate()-1) and active = 1
0
 
LVL 25

Expert Comment

by:chaau
ID: 38833844
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
 
LVL 10

Expert Comment

by:deviprasadg
ID: 38833845
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 38834617
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
 
LVL 32

Expert Comment

by:awking00
ID: 38836436
select * from table1 where status = 'archived' and active = 1
union
select * from table1 where status <> 'archived' and ExpireDate > getdate()-1;
0
 
LVL 32

Expert Comment

by:awking00
ID: 38836437
select * from table1 where status = 'archived' and active = 1
union
select * from table1 where status <> 'archived' and ExpireDate > getdate()-1;
0
 
LVL 32

Expert Comment

by:awking00
ID: 38836445
Sorry about the duplicate submission.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 38843214
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
 

Author Comment

by:arthurh88
ID: 38849106
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 38849373
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
 
LVL 32

Expert Comment

by:awking00
ID: 38851487
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 38854563
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 38866967
Hi, has you data selection question been resolved now?
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question