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

Based on the limited information you provided in your question:

DECLARE @TargetDate AS DATETIME = dateadd(day,datediff(day,1,GETDATE()),0)

/* The rest of your query here */
AND (
    a.LAST_UPDATE_DATE >= @TargetDate
    OR b.LAST_UPDATE_DATE >= @TargetDate 
    OR c.LAST_UPDATE_DATE >= @TargetDate
    OR d.LAST_UPDATE_DATE >= @TargetDate
) 

Answer 2

The only alternative I can think of would be something like an EXISTS where you unpivot the data. Though, as I mention in the comments this probably isn't less characters. As for performance, I don't have a good scenario in my environment to test this on, and someone else might look at this and find it more confusing.

Any way, if you wanted to use an EXISTS instead, it would look something like this:

AND EXISTS (SELECT 1
            FROM (VALUES(a.LAST_UPDATE_DATE),
                        (b.LAST_UPDATE_DATE),
                        (c.LAST_UPDATE_DATE),
                        (d.LAST_UPDATE_DATE))V(LAST_UPDATE_DATE)
            WHERE V.LAST_UPDATE_DATE > DATEADD(DAY,DATEDIFF(DAY,1,GETDATE()),0))

If your concern is the expression, DATEADD(DAY,DATEDIFF(DAY,-1,GETDATE()),0), then I agree that's a little confusing. I think DATEADD(DAY, -1, CONVERT(date,GETDATE())) would be more obvious to others what it is doing.

Answer 3

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

Answer 4

You could avoid repeating the date expression:

from ... cross apply
    (values (dateadd(day, datediff(day, 1, GETDATE()), 0))) as v(start_of_yesterday)
where ... and (
        a.LAST_UPDATE_DATE >= start_of_yesterday
    or  b.LAST_UPDATE_DATE >= start_of_yesterday
    or  c.LAST_UPDATE_DATE >= start_of_yesterday
    or  d.LAST_UPDATE_DATE >= start_of_yesterday
)