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

提供sql查询如下：

--查询亚马逊订单回款还没有下载回来的统计

--查询亚马逊平台订单付款后某段时间中没有回款的订单
--亚马逊结算时间
DECLARE @PostedDateStart DATETIME ='2022-02-01'
DECLARE @PostedDateEnd DATETIME ='2022-03-08'
--订单付款时间
DECLARE @PayDateStart DATETIME ='2022-01-15'
DECLARE @PayDateEnd DATETIME ='2022-03-01'

SELECT oif.OrderSourceID AS '店铺ID',MAX(os.OrderSourceName) AS '店铺名称',COUNT(oif.ID) AS '订单存在费用不存在'
,MAX(oif.OrderCode) AS '订单示例1',MIN(oif.OrderCode) AS '订单示例2' FROM  dbo.RB_OrderInfo oif (NOLOCK) 
INNER JOIN dbo.RB_OrderSource os (NOLOCK) ON oif.CustomerID=os.CustomerID AND oif.OrderSourceID=os.ID 
LEFT JOIN ( 
SELECT CustomerID,OrderSourceID,AmazonOrderId,COUNT(ID) AS ESCount FROM 
dbo.RB_Amazon_ListFinancialEvents  (NOLOCK)  
WHERE CustomerID=1 AND ISNULL(AmazonOrderId,'')<>''  AND PostedDate>=@PostedDateStart AND PostedDate<@PostedDateEnd
GROUP BY CustomerID,OrderSourceID,AmazonOrderId
) as es ON oif.CustomerID=es.CustomerID AND oif.OrderSourceID=es.OrderSourceID AND
oif.ClientOrderCode=es.AmazonOrderId 
WHERE oif.CustomerID=1 
AND oif.PayTime>=@PayDateStart
AND oif.PayTime<=@PayDateEnd
AND  oif.IsStockOrder<>1 
AND oif.PromotionType  LIKE  '%MarketplaceFacilitator%'
AND oif.DownLoadUpdateTime>'2000-01-01' AND os.OrderSourceType=1
AND isnull(es.ESCount,0)<=0
GROUP BY oif.OrderSourceID
ORDER BY COUNT(oif.ID) DESC