CODEWITHSUNDEEP
sqlsql-servert-sql
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3362

Select Max Date or Highest Date Available

I have 2 tables. Table_A contains date values from 2020-07-01 - 2021-06-30 Table_B contains dates values from 2021-07-01 to whatever todays date is.

I want to select the highest date value from Table_A that is at least 365 days older than the higher date value in Table_B.

For example:

If the highest date value in Table_B is 2021-08-16, then I want 2020-08-16 from Table_A.

If 2020-08-16 is not available in Table_A due to some software problem we were having at the time, network issue, etc, I want the next highest available.

Here is the code I am using so far:

select top 10000000
    a.[Report Date]
from
    [Table_A] as a
where
    a.[Report Date] <= (select max(b.[Report Date]) from [Table_B] as b) AND
    cast((select max(b.[Report Date]) from [Table_B] as b) as date) - cast(a.[Report Date] as date) >= 365
group by
    a.[Report Date]
order by
    a.[Report Date]

The error message I'm getting is

Operand Data Type date is invalid for subtract operator.

I don't even know if this code will work, was just testing since I'm still developing.

Any suggestions?

Upvotes: 0

Views: 1016

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

It seems you think to complicated. Simply select the maximum a.date where a.date <= max(b.date) - 365 days:

select
  max(a.[Report Date])
from [Table_A] as a
where a.[Report Date] <=
        (select dateadd(day, -365, max(b.[Report Date])) from [Table_B] as b);

Upvotes: 1

Related Questions