22,210
社区成员
发帖
与我相关
我的任务
分享
--创建一个作业,每天执行或者每月执行
insert into 客户流失预警(客户编号,客户名称,最后时间)
select a.cusid,a.cusname,b.orderdate
from 客户表 a inner join 订单表 b on a.cusid=b.cusid
where datediff(m,b.orderdate,getdate())>6 and not exists(
select 1 from 订单表 where curid=b.cusid and orderdate>a.orderdate)
and not exists(select 1 from 客户流失预警 where 客户编号=a.a.cusid and 客户名称=a.cusname and 最后时间=b.b.orderdate)
--创建一个作业,每天执行或者每月执行
insert into 客户流失预警(客户编号,客户名称,最后时间)
select a.cusid,a.cusname,b.orderdate
from 客户表 a inner join 订单表 b on a.cusid=b.cusid
where datediff(m,b.orderdate,getdate())>6 and not exists(
select 1 from 订单表 where curid=b.cusid and orderdate>a.orderdate)
select * from customer t where not exists(select 1 from orders where cusID=t.cusID and datediff(mm,OrderDate,getdate())>6)
select * from Customer as a
where not exists(select 1 from Orders
where CusID=a.CusID and dateadd(m,datediff(m,OrderDate,getdate()),OrderDate)<getdate())--按天数计算时
select * from Customer as a where not exists(select 1 from Orders where CusID=a.CusID and datediff(m,OrderDate,getdate())>6)