We help IT Professionals succeed at work.

and / or logic in where clause

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

Test your restores, not your backups...
Expert of the Year 2019
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

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 PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Welcome.


»bp