深圳智汇创想科技有限公司

根据如下SQL 作为判断依据查找未采集原因，并贴附在共享表中

SELECT
CONVERT(varchar(10),DATEADD(day,-1,GETDATE()),120) as '日期',
(

SELECT COUNT(DISTINCT SellerId + MarketplaceId ) 

FROM CONVERT(varchar(10),DATEADD(day,-1,GETDATE()),120) AND CrawlConfigId IN 

( SELECT id FROM CrawlConfig with(nolock)  WHERE   BusinessId = 'PaymentBalanceRunner' )   

and   SellerId + MarketplaceId in (
select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1,2)  and IsActive=1  and SaleAdminName not in('林映','聂小霞','李燕','叶巧盼')
)

and   SellerId + MarketplaceId in (
SELECT DISTINCT (SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  
WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='browser'
)
  ) as '紫鸟余额报告采集成功数',
  (

SELECT COUNT(DISTINCT SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='browser'   and   SellerId + MarketplaceId in

(select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1,2)  and IsActive=1
and SaleAdminName not in('林映','聂小霞','李燕','叶巧盼')
)

) as '紫鸟总账号数',

(

SELECT COUNT(DISTINCT SellerId + MarketplaceId ) 

FROM CONVERT(varchar(10),DATEADD(day,-1,GETDATE()),120)  AND CrawlConfigId IN ( SELECT id FROM CrawlConfig   with(nolock) WHERE   BusinessId = 'PaymentBalanceRunner' )

and   SellerId + MarketplaceId in (select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1,2)  and IsActive=1

and SaleAdminName not in('林映','聂小霞','李燕','叶巧盼')
)

and   SellerId + MarketplaceId in (SELECT DISTINCT (SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='browser')

) * 100

/ (

SELECT COUNT(DISTINCT SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus] with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='browser'

and   SellerId + MarketplaceId in (select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1,2)  and IsActive=1
and SaleAdminName not in('林映','聂小霞','李燕','叶巧盼')
)

)   as '紫鸟机子采集成功率%', 

CONVERT(varchar(10),DATEADD(day,-6,GETDATE()),120) as '日期',

(

SELECT COUNT(DISTINCT SellerId + MarketplaceId ) 

FROM CONVERT(varchar(10),DATEADD(day,-1,GETDATE()),120) AND CrawlConfigId IN 

( SELECT id FROM CrawlConfig with(nolock)  WHERE   BusinessId = 'PaymentBalanceRunner' )   

and   SellerId + MarketplaceId in (select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1)  and IsActive=1 
and SaleAdminName not in('林映','聂小霞','李燕')
)

and   SellerId + MarketplaceId in (SELECT DISTINCT (SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='service')



  ) as '远程机余额报告采集成功数',

  (

SELECT COUNT(DISTINCT SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='service'   and   SellerId + MarketplaceId in

(select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1)  and IsActive=1)

) as '远程机总账号数',

(

SELECT COUNT(DISTINCT SellerId + MarketplaceId ) 

FROM CONVERT(varchar(10),DATEADD(day,-3,GETDATE()),120)  AND CrawlConfigId IN ( SELECT id FROM CrawlConfig   with(nolock) WHERE   BusinessId = 'PaymentBalanceRunner' )

and   SellerId + MarketplaceId in (select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1)  and IsActive=1)

and   SellerId + MarketplaceId in (SELECT DISTINCT (SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='service')

) * 100

/ (

SELECT COUNT(DISTINCT SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus] with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='service'

and   SellerId + MarketplaceId in (select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1)  and IsActive=1)

)   as '远程机子采集成功率%'

根据如下SQL 作为判断依据查找未采集原因，并贴附在共享表中

SELECT
CONVERT(varchar(10),DATEADD(day,-1,GETDATE()),120) as '日期',
(

SELECT COUNT(DISTINCT SellerId + MarketplaceId ) 

FROM CrawlConfigDetail with(nolock)  WHERE Status = 1 and [Create] > CONVERT(varchar(10),DATEADD(day,-1,GETDATE()),120) AND CrawlConfigId IN 

( SELECT id FROM CrawlConfig with(nolock)  WHERE   BusinessId = 'PaymentBalanceRunner' )   

and   SellerId + MarketplaceId in (
select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1,2)  and IsActive=1  and SaleAdminName not in('林映','聂小霞','李燕','叶巧盼')
)

and   SellerId + MarketplaceId in (
SELECT DISTINCT (SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  
WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='browser'
)
  ) as '紫鸟余额报告采集成功数',
  (

SELECT COUNT(DISTINCT SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='browser'   and   SellerId + MarketplaceId in

(select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1,2)  and IsActive=1
and SaleAdminName not in('林映','聂小霞','李燕','叶巧盼')
)

) as '紫鸟总账号数',

(

SELECT COUNT(DISTINCT SellerId + MarketplaceId ) 

FROM CrawlConfigDetail WHERE Status = 1 AND [Create] > CONVERT(varchar(10),DATEADD(day,-1,GETDATE()),120)  AND CrawlConfigId IN ( SELECT id FROM CrawlConfig   with(nolock) WHERE   BusinessId = 'PaymentBalanceRunner' )

and   SellerId + MarketplaceId in (select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1,2)  and IsActive=1

and SaleAdminName not in('林映','聂小霞','李燕','叶巧盼')
)

and   SellerId + MarketplaceId in (SELECT DISTINCT (SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='browser')

) * 100

/ (

SELECT COUNT(DISTINCT SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus] with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='browser'

and   SellerId + MarketplaceId in (select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1,2)  and IsActive=1
and SaleAdminName not in('林映','聂小霞','李燕','叶巧盼')
)

)   as '紫鸟机子采集成功率%', 

CONVERT(varchar(10),DATEADD(day,-6,GETDATE()),120) as '日期',

(

SELECT COUNT(DISTINCT SellerId + MarketplaceId ) 

FROM CrawlConfigDetail with(nolock)  WHERE Status = 1 and [Create] > CONVERT(varchar(10),DATEADD(day,-1,GETDATE()),120) AND CrawlConfigId IN 

( SELECT id FROM CrawlConfig with(nolock)  WHERE   BusinessId = 'PaymentBalanceRunner' )   

and   SellerId + MarketplaceId in (select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1)  and IsActive=1 
and SaleAdminName not in('林映','聂小霞','李燕')
)

and   SellerId + MarketplaceId in (SELECT DISTINCT (SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='service')



  ) as '远程机余额报告采集成功数',

  (

SELECT COUNT(DISTINCT SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='service'   and   SellerId + MarketplaceId in

(select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1)  and IsActive=1)

) as '远程机总账号数',

(

SELECT COUNT(DISTINCT SellerId + MarketplaceId ) 

FROM CrawlConfigDetail WHERE Status = 1 AND [Create] > CONVERT(varchar(10),DATEADD(day,-3,GETDATE()),120)  AND CrawlConfigId IN ( SELECT id FROM CrawlConfig   with(nolock) WHERE   BusinessId = 'PaymentBalanceRunner' )

and   SellerId + MarketplaceId in (select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1)  and IsActive=1)

and   SellerId + MarketplaceId in (SELECT DISTINCT (SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus]  with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='service')

) * 100

/ (

SELECT COUNT(DISTINCT SellerId + MarketplaceId )  FROM [dbo].[PaymentOnlineStatus] with(nolock)  WHERE SellerId IS NOT NULL AND MarketplaceId IS NOT NULL and Source='service'

and   SellerId + MarketplaceId in (select  Sellerid+Marketplaceid from  Ordersource with(nolock)  where  CollectionAccountStatus in (0,1)  and IsActive=1)

)   as '远程机子采集成功率%'