Solved

possible with a query to do this?

Posted on 2013-01-29
13
131 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

920 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

16 Experts available now in Live!

Get 1:1 Help Now