lyfz_repos/经典版源码/document/数据库维护脚本/监控查询.sql

declare @days int
set @days = 1

-- 1.查找已订单,但x天未拍照的订单;
select * from dindan where 
status <> 'OK' and				-- 拍照状态;
datediff(dd,time1,getdate()) >= @days

-- 2.查找已拍照,但x天未初修的订单;
select * from dindan where 
status = 'OK' and				-- 拍照状态;
[status4] <> 'OK' and			-- 初修状态;
datediff(dd,time1,getdate()) >= @days

-- 3.查找已初修,但x天未选片的订单;
select * from dindan where 
status = 'OK' and				-- 拍照状态; 
[status4] = 'OK' and			-- 初修状态; 
[status2] <> 'OK' and			-- 选片状态;  
datediff(dd,time1,getdate()) >= @days

-- 4.查找已选片,但x天未精修的订单;
select * from dindan where 
status = 'OK' and				-- 拍照状态;  
[status4] = 'OK' and			-- 初修状态;  
[status2] = 'OK' and			-- 选片状态;   
[status6] <> 'OK' and			-- 精修状态;   
datediff(dd,time1,getdate()) >= @days

-- 5.查找已精修,但x天未设计的订单;
select * from dindan where 
status = 'OK' and				-- 拍照状态;  
[status4] = 'OK' and			-- 初修状态;  
[status2] = 'OK' and			-- 选片状态;   
[status6] = 'OK' and			-- 精修状态;  
[status5] <> 'OK' and			-- 设计状态;  
datediff(dd,time1,getdate()) >= @days


-- 5.查找已发片,但x天未回件的订单;
select * from dindan where 
[status3] <> 'OK' and			-- 取件状态;  
datediff(dd,time1,getdate()) >= @days

-- 已发送的订单;
select * from dindansp where [status3] is not null and [status3]  <> ''

-- 已完成的订单;
select * from dindansp where [status1] = 'OK'

-- 已取件的商品;
select * from dindansp where [status2] = 'OK' and [status1] = 'OK'


-- 6.查看已回件,但x天未取件的订单;
select * from dindan where 
[status3] <> 'OK' and			-- 取件状态;  
datediff(dd,time1,getdate()) >= @days