Group or combine a long query when checking multiple values

Question

I have a query where I only want to show results where the data was recently updated (yesterday or sooner).

My query pulls data from 4 different tables, so I need to check the LAST_UPDATE_DATE column of each table.

If any of them have a LAST_UPDATE_DATE value that was yesterday or sooner, then I want to pull all that data.

So I added this to my query:

AND (a.LAST_UPDATE_DATE >= dateadd(day,datediff(day,1,GETDATE()),0)
 OR b.LAST_UPDATE_DATE >= dateadd(day,datediff(day,1,GETDATE()),0) 
 OR c.LAST_UPDATE_DATE >= dateadd(day,datediff(day,1,GETDATE()),0)
 OR d.LAST_UPDATE_DATE >= dateadd(day,datediff(day,1,GETDATE()),0)) 

I think it's working, but is there a way to shorten the statement because it is very long and confusing.

Answer 1

This could possibly be a use case for ANY/SOME. The ANY function returns true if any value in the subquery meets the predicate you apply to it. So in this case, you could use the predicate where getdate() - 1 > any (...dates)

Since there is no sample data to work with here, I'm completely guessing at what your query looks like, but this should give you an idea of how you could structure your predicate.

declare @DaysBack int = 5
;with a as
(
    select dt = getdate() - 10 union all
    select getdate() - 9
), b as
(
    select dt = getdate() - 8 union all
    select getdate() - 7
), c as
(
    select dt = getdate() - 6 union all
    select getdate() - 5
)
select *
from a
where getdate() - @DaysBack > any 
(
    select dt from a union all
    select dt from b union all
    select dt from c
)

Or with your columns:

....
where DATEADD(DAY,DATEDIFF(DAY,1,GETDATE()),0)) > any 
(
    select LAST_UPDATE_DATE from a union all
    select LAST_UPDATE_DATE from b union all
    select LAST_UPDATE_DATE from c
)

As a side note, I was dealing with a SO question yesterday which discussed ANY/SOME which is why it's fresh in my mind but I don't usually use these functions. However it does seem it might be a valid alternate syntax. If you want to know more on these functions, I'd recommend reading All, Any, and Some: The Three Stooges