有没有一种方法可以在SQL中找到超过n天的最新日期

Question

我正在努力寻找过去90天内没有借款的资产。逻辑应该类似

IF latest date of an asset returned_date > 90 days
more than 90 days
ELIF created_date > 90 days
more than 90 days
ELSE
not more than 90 days

如何将所有这些写入一个查询

贷款

loan_id   asset_id   returned_date
1         1          2019-12-14 12:00:00.000
2         1          2019-12-10 12:00:00.000
3         2          2020-11-10 12:00:00.000

资产

asset_id   created_date
1          2019-12-05 12:00:00.000
2          2019-12-05 12:00:00.000
3          2019-12-05 12:00:00.000

Answer 1

如果我理解正确，这只是一个not exists查询。有两种情况：

• 该资产至少在90天前创建
• 在过去的90天里没有任何回报

这将是：

select a.*
from asset a
where a.create_date < dateadd(day, -90, getdate()) and
not exists (select 1
from loan l
where l.asset_id = a.asset_id and
l.return_date >= dateadd(day, -90, getdate())
);

Answer 2

下面的查询只返回90天内未借入的资产。

注：

• 如果资产根本没有被借用，则其创建日期将用于计算(代码中用**标记(
• 如果一项资产被多次借用，则计算中会使用最近的资产(代码中用***标记(

select * from(
select
a.asset_id,
l.loan_id,
isnull(l.return_date,a.create_date) as return_date, -- **
rank() over(partition by a.asset_id order by l.return_date desc) as rnk -- ***
from asset a
left join loan l on a.asset_id=l.asset_id
)x
where
rnk=1 -- ***
and datediff(day,return_date, getdate())>=90

Answer 3

您可以使用CASE语句和DATEADD(此处dd表示天数(来对借用与否进行分类(解决方案1(。然后，如果您希望显示其中一个，您可以将检查条件移动到WHERE子句中(解决方案2(。

解决方案1

select
t1.asset_id,
t2.returned_date,
case when t2.returned_date > dateadd(dd,90,t1.created_date) then 'more than 90 days'
else 'not more than 90 days'
end as 'borrow window'
from asset t1
join loan t2
on t2.asset_id = t1.asset_id

解决方案2

select t1.*, t2.returned_date 
from asset t1
join loan t2
on t2.asset_id = t1.asset_id
where t2.returned_date > dateadd(dd, 90, t1.created_date) -- only > 90