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

测试环境：

主干1商户

服务逻辑

1、查询Amazon回款管理页面，两个月内没有关联来源渠道的数据A

2、找到数据A的明细中时间最早且状态为Adjustment，取值total的金额B

3、用数据A的开发者ID查找Amazon回款管理相同的数据C，假如数据A的结束时间跟数据C的起始时间一样，且数据C的结余金额跟数据A明细中的total的金额B一样，则将数据C的订单来源渠道赋值到数据A

数据库sql相关

RB_Amazon_ListFinancialEventGroups---Amazon回款管理表

RB_Amazon_ListFinancialEvents---Amazon回款明细表

1、--取最近两个月没有关联来源渠道的账单---- A
SELECT id,CustomerID,
       DevIDSellerId,FinancialEventGroupId,
       FinancialEventGroupStart,
             AddTime
FROM RB_Amazon_ListFinancialEventGroups (NOLOCK)
WHERE OrderSourceID IS NULL
      AND AddOrderSourceID IS NULL
AND FinancialEventGroupStart IS NOT null
      AND AddTime
      BETWEEN DATEADD(MONTH, -2, GETDATE()) AND GETDATE()

2、--查找账单--A中的明细，时间最早且状态为Adjustment，取值total

SELECT CurrencyAmount
FROM RB_Amazon_ListFinancialEvents
WHERE  FinancesReportID ='lK3LH2DpYnaa5Et_RvglOiwR_emgy7vFO3WcI4vWV9s'  
and FinancialEventType = 'AdjustmentEvents'
and PostedDate in
(SELECT min(PostedDate)
from RB_Amazon_ListFinancialEvents  
WHERE  FinancesReportID ='lK3LH2DpYnaa5Et_RvglOiwR_emgy7vFO3WcI4vWV9s'  
and FinancialEventType = 'AdjustmentEvents')

3、--查找同一个开发者ID下结束时间=账单A的起始时间的数据，且结余金额=total的金额

select * from RB_Amazon_ListFinancialEventGroups(nolock) as a
WHERE DevIDSellerId
in (SELECT SellerId
FROM RB_Amazon_ListFinancialEvents
WHERE  FinancesReportID ='LRQol8fHk_yHBoYj3pWv_k2BpLhtC6ku8VKEV7nDpwk'  
and FinancialEventType = 'AdjustmentEvents'
and PostedDate in
(SELECT min(PostedDate)
from RB_Amazon_ListFinancialEvents  
WHERE  FinancesReportID ='LRQol8fHk_yHBoYj3pWv_k2BpLhtC6ku8VKEV7nDpwk'  
and FinancialEventType = 'AdjustmentEvents'))
and FinancialEventGroupEnd = FinancialEventGroupStart
and (SELECT CurrencyAmount
FROM RB_Amazon_ListFinancialEvents
WHERE  FinancesReportID ='LRQol8fHk_yHBoYj3pWv_k2BpLhtC6ku8VKEV7nDpwk'  
and FinancialEventType = 'AdjustmentEvents'
and PostedDate in
(SELECT min(PostedDate)
from RB_Amazon_ListFinancialEvents  
WHERE  FinancesReportID ='LRQol8fHk_yHBoYj3pWv_k2BpLhtC6ku8VKEV7nDpwk'  
and FinancialEventType = 'AdjustmentEvents'))  = EndOfMonthBalance

执行服务前后对比

执行前

执行后