SYCW_API/文档/Sql/sql_20190430...

/****** Script for SelectTopNRows command from SSMS  ******/
--创建量，接单量，未处理量，异常量，已处理量
--创建量
  select CreateUser,COUNT(1) from T_Wo_WorkOrder where 1=1 group by CreateUser

--接单量
  select  F_NextUser,COUNT(1) from (
  select  F_WoID,F_NextUser from T_Wo_WorkOrderItem_New where F_ItemType=1 and F_WoState=1
  and F_WoID in (select ID FROM T_Wo_WorkOrder where 1=1 )
  group by F_WoID,F_NextUser) t group by F_NextUser
--未处理量
  select  F_CreateUser,COUNT(1) from (
  select  F_WoID,F_CreateUser from T_Wo_WorkOrderItem_New where F_ItemType=1 and F_WoState <10
  and F_WoID in (select ID FROM T_Wo_WorkOrder where 1=1 )
  group by F_WoID,F_CreateUser) t group by F_CreateUser
--异常量
  select  F_CreateUser,COUNT(1) from (
  select  F_WoID,F_CreateUser from T_Wo_WorkOrderItem_New where F_ItemType=1 and F_WoState =5
  and F_WoID in (select ID FROM T_Wo_WorkOrder where 1=1 )
  group by F_WoID,F_CreateUser) t group by F_CreateUser
--处理量
  select LastDealUser,COUNT(1) from T_Wo_WorkOrder where 1=1 group by LastDealUser



SELECT *
  FROM [CallCenter_SanYuanCW].[dbo].[T_Wo_WorkOrder]
  order by CreateTime desc
  
  select *  FROM T_Wo_WorkOrder 
  where IsDel=0 and ID in (select F_WoID from T_Wo_WorkOrderItem_New where F_ItemType=1 and F_WoState=1)
  

  
  select *  FROM T_Wo_WorkOrder 
  where 1=1
   and isdel=0  and State in(4,2,3) and T_Wo_WorkOrder.ID in ( select F_WoID from T_Wo_WorkOrderItem_New where F_ItemType=1 and ((F_WoState in(4,2) and F_CreateUser='8000')  or (F_WoState=3 and F_NextUser='8000')) and isnull(F_IsUsed,'0')='0' )  and IsAudit = 1 
   
   select * from T_Wo_WorkOrderItem_New 
   where F_ItemType=1 
	and (
		(F_WoState in(4,2) and F_CreateUser='8000')  
		or (F_WoState=3 and F_NextUser='8000')
	) 
	and isnull(F_IsUsed,'0')='0' 
   
   --and isdel=0  and State =1 and T_Wo_WorkOrder.ID in ( select F_WoID from T_Wo_WorkOrderItem_New where F_ItemType=1 and F_WoState=1 and isnull(F_IsUsed,'0')='0' and F_NextUser='8000' )  and IsAudit = 1 
   
  -- select * from T_Wo_WorkOrderItem_New where F_ItemType=1 and F_WoState=1 and isnull(F_IsUsed,'0')='0' and F_NextUser='8000'
   
   
   
   --where  and State =1 and T_Wo_WorkOrder.ID in ( select F_WoID from T_Wo_WorkOrderItem_New where Type=1 and F_WoState=1 and isnull(F_IsUsed,'0')='0' and F_NextUser='8000' )  and IsDel=0  and State =1 and IsAudit = 1