and / or logic in where clause

vbnetcoder
vbnetcoder used Ask the Experts™
on
how would I update the code to grab: Effective or Term within the last 7 days OR last update within last 7 days with effective or term date prior to the last 7 days?

WHERE ek.OrderId = 'rty5465464'

      AND (
              ek.termdate
      BETWEEN GETDATE() - 7 AND GETDATE()
              OR ek.effdate
      BETWEEN GETDATE() - 7 AND GETDATE()
	  OR
	          ek.lastupdate
      BETWEEN GETDATE() - 7 AND GETDATE()

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT / Software Engineering Consultant
Top Expert 2016
Commented:
Sounds like this would be it.  Be aware though that BETWEEN can be tricky, unless you know your date fields don't have time info in them?

WHERE  ek.orderid = 'rty5465464' 
       AND ( ( ek.termdate BETWEEN Getdate() - 7 AND Getdate() 
                OR ek.effdate BETWEEN Getdate() - 7 AND Getdate() ) 
              OR ( ek.lastupdate BETWEEN Getdate() - 7 AND Getdate() 
                   AND ( ek.termdate < Getdate() - 7 
                          OR ek.effdate < Getdate() - 7) ) ) 

Open in new window


»bp

Author

Commented:
There are dates that look like this:
2017-11-01 00:00:00

Author

Commented:
It looks like your code is returning values where term and effective date is null
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
actually not. I was wrong about the nulls
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
can you pls provide few input rows and the expected output.

Author

Commented:
ty
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Welcome.


»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial