-- 1.在每个月不同的人在enddate之前5天的未交房租记录.(也就是该交房的住户)
select *
from [Contract] a
where not exists
(select 1
from [Pay] b
where b.ContractID=a.ContractID
and left(convert(varchar,b.indate,112),6)=left(convert(varchar,getdate(),112),6))
and convert(varchar,getdate(),112)>=
convert(varchar,dateadd(d,-5,left(convert(varchar,getdate(),23),8)+right(convert(varchar,a.enddate,112),2)),112)-- 2.查出不同的人过了每月该交房租但未交的记录?(逾期记录)
select *
from [Contract] a
where not exists
(select 1
from [Pay] b
where b.ContractID=a.ContractID
and left(convert(varchar,b.indate,112),6)=left(convert(varchar,getdate(),112),6))
and convert(varchar,getdate(),112)>=
convert(varchar,left(convert(varchar,getdate(),23),8)+right(convert(varchar,a.enddate,112),2),112)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货