Solved

possible with a query to do this?

Posted on 2013-01-29
13
128 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
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 24

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
 
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 31

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 31

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 31

Expert Comment

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

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 48

Accepted Solution

by:
PortletPaul earned 500 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 31

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 48

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 48

Expert Comment

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now