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

目前已核对完业务提供的所有店铺后台交易明细--后台下载报告

验证场景：

通过脚本

1、根据店铺和月份对比采集库对应的月份

不区分订单和非订单

Type、        类型
調整         ADJUSTMENT
訂單 SALE
凍結 冻结
發款 發款
付款糾紛 纠纷
其他費用 NON SALE CHARGE
收費 TRANSFER
索償 REFUND
退款 REFUND
郵寄標籤 SHIPPING LABEL




---对应表格
SELECT SUM( CAST ( NetAmount AS FLOAT ) ) AS '净额'
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%mymalldealshop%' 
AND TransactioncreationDate >= '2023-07-01' 
AND TransactioncreationDate <= '2023-07-31' 
AND NetAmount != '--' 


---对应表格
SELECT 
SUM ( CAST ( GrossTransactionAmount AS FLOAT ) ) AS '交易总额'
FROM
[dbo].[ShFinReport_20230601] 
WHERE
__LoginUserName LIKE '%vocalnamall%' 
AND TransactioncreationDate >= '2023-05-01' 
AND TransactioncreationDate <= '2023-05-31' 
AND GrossTransactionAmount != '--' 



SELECT 
SUM ( CAST ( ItemSubtotal AS FLOAT ) ) AS '物品小计'
FROM
[dbo].[ShFinReport_20230601] 
WHERE
__LoginUserName LIKE '%vocalnamall%' 
AND TransactioncreationDate >= '2023-05-01' 
AND TransactioncreationDate <= '2023-05-31' 
AND ItemSubtotal != '--' 


SELECT 
SUM ( CAST ( EbayCollecteTax AS FLOAT ) ) AS 'eBay 收取的稅款'
FROM
[dbo].[ShFinReport_20230601] 
WHERE
__LoginUserName LIKE '%vocalnamall%' 
AND TransactioncreationDate >= '2023-05-01' 
AND TransactioncreationDate <= '2023-05-31' 
AND EbayCollecteTax != '--' 



SELECT SUM(ColumnName1) AS '佣金(成交費 — 固定\成交費 — 因類別而異\非常高「物品與說明不符」率的費用\表現低於標準的費用\跨國交易費
)' from (
SELECT SUM
( CAST ( FinalValueFeeFixed AS FLOAT ) ) AS ColumnName1 
FROM
[dbo].[ShFinReport_20230601] 
WHERE
__LoginUserName LIKE '%vocalnamall%' 
AND TransactioncreationDate >= '2023-05-01' 
AND TransactioncreationDate <= '2023-05-31' 
AND FinalValueFeeFixed != '--' 
UNION ALL
SELECT SUM
( CAST ( FinalValueFeeVariable AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230601] 
WHERE
__LoginUserName LIKE '%vocalnamall%' 
AND TransactioncreationDate >= '2023-05-01' 
AND TransactioncreationDate <= '2023-05-31' 
AND FinalValueFeeVariable != '--' 
UNION ALL
SELECT 
SUM ( CAST ( VeryHighItemNotAsDescribedFee AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230601] 
WHERE
__LoginUserName LIKE '%vocalnamall%' 
AND TransactioncreationDate >= '2023-05-01' 
AND TransactioncreationDate <= '2023-05-31' 
AND VeryHighItemNotAsDescribedFee != '--'
UNION ALL
SELECT 
SUM ( CAST ( InternationalFee AS FLOAT ) )  AS ColumnName1
FROM
[dbo].[ShFinReport_20230601] 
WHERE
__LoginUserName LIKE '%vocalnamall%' 
AND TransactioncreationDate >= '2023-05-01' 
AND TransactioncreationDate <= '2023-05-31' 
AND InternationalFee != '--' 
UNION ALL
SELECT  
SUM ( CAST ( BelowStandardPerformanceFee AS FLOAT ) )  AS ColumnName1
FROM
[dbo].[ShFinReport_20230601] 
WHERE
__LoginUserName LIKE '%vocalnamall%' 
AND TransactioncreationDate >= '2023-05-01' 
AND TransactioncreationDate <= '2023-05-31' 
AND BelowStandardPerformanceFee != '--' 
)AS UnionTable;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---对应表格
SELECT SUM( CAST ( NetAmount AS FLOAT ) ) AS '净额'
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%wonderoneyoustore%' 
AND TransactioncreationDate >= '2023-07-01' 
AND TransactioncreationDate <= '2023-07-31' 
AND NetAmount != '--' 



SELECT SUM( CAST (replace(NetAmount,',','') AS FLOAT ) ) AS '净额'
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%mymalldealshop%' 
AND TransactioncreationDate >= '2023-07-01' 
AND TransactioncreationDate <= '2023-07-31' 
AND NetAmount != '--' 


---对应表格
SELECT 
SUM ( CAST ( GrossTransactionAmount AS FLOAT ) ) AS '交易总额'
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%wonderoneyoustore%' 
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31' 
AND GrossTransactionAmount != '--' 



SELECT 
SUM ( CAST ( ItemSubtotal AS FLOAT ) ) AS '物品小计'
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%wonderoneyoustore%' 
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31' 
AND ItemSubtotal != '--' 


SELECT 
SUM ( CAST ( EbayCollecteTax AS FLOAT ) ) AS 'eBay 收取的稅款'
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%wonderoneyoustore%' 
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31' 
AND EbayCollecteTax != '--' 



SELECT SUM(ColumnName1) AS '佣金(成交費 — 固定\成交費 — 因類別而異\非常高「物品與說明不符」率的費用\表現低於標準的費用\跨國交易費
)' from (
SELECT SUM
( CAST ( FinalValueFeeFixed AS FLOAT ) ) AS ColumnName1 
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%wonderoneyoustore%' 
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31' 
AND FinalValueFeeFixed != '--' 
UNION ALL
SELECT SUM
( CAST ( FinalValueFeeVariable AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%wonderoneyoustore%' 
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31' 
AND FinalValueFeeVariable != '--' 
UNION ALL
SELECT 
SUM ( CAST ( VeryHighItemNotAsDescribedFee AS FLOAT ) ) AS ColumnName1
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%wonderoneyoustore%' 
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31' 
AND VeryHighItemNotAsDescribedFee != '--'
UNION ALL
SELECT 
SUM ( CAST ( InternationalFee AS FLOAT ) )  AS ColumnName1
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%wonderoneyoustore%' 
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31' 
AND InternationalFee != '--' 
UNION ALL
SELECT  
SUM ( CAST ( BelowStandardPerformanceFee AS FLOAT ) )  AS ColumnName1
FROM
[dbo].[ShFinReport_20230801] 
WHERE
__LoginUserName LIKE '%wonderoneyoustore%' 
AND TransactioncreationDate >= '2023-07-01'
AND TransactioncreationDate <= '2023-07-31' 
AND BelowStandardPerformanceFee != '--' 
)AS UnionTable;

存在业务下载的地区时间与采集时间不一致，导致数据校验不通过

只要是业务下载的地区时间是香港的，因为采集库也是通过香港时间去下载数据，经校验，各个项的总额统计是一致的