[步骤]

content:[10:11:13]  -------------------------------------------------------------

CODE:N101113203

Error:16_8134_1_-2146232060

SQL:-- 产品打包率
-- 原报表名称：销单产品的打包比例分析
/*
需求说明：
1、新增表格【销单产品的打包比例分析】
2、选择时间为【销单时间】，可选时间段；
3、导出字段已按需求新增上架时间、上架天数、是否一年内终审
4、新增采购单打包状态、销单打包状态，按具体每次销单明细数据展示
5、1688链接匹配数量：产品界面默认供应商对应的链接1688产品数量
*/

 --DECLARE @CustomerID INT = 1
 --DECLARE @StartTime DATETIME = '2023-09-01'
 --DECLARE @EndTime DATETIME = GETDATE() -- '2023-09-30 23:59:59'


DROP TABLE IF EXISTS #temp
SELECT -- TOP 1
CONVERT(VARCHAR(10), @StartTime, 126) AS '开始时间',
CONVERT(VARCHAR(10),@EndTime, 126) AS '结束时间',
pdbd.SKU AS 'SKU',
pdbd.ListNo 销单号,
pl.P_Code 采购单号,
CASE ISNULL(pd.NeedToPackType,p.NeedToPackType)
WHEN 0 THEN '不需打包'
WHEN 1 THEN '需供应商打包'
WHEN 2 THEN '加工打包'
WHEN 3 THEN '销单打包'
END 采购单打包状态,
b.Single_NO AS '下单号',
CASE WHEN pl.IsSample = 1 THEN '是' ELSE '否' END AS '是否样品单',
CASE p.DevelopType 
WHEN 1 THEN '自主开发' 
WHEN 2 THEN '供应商后台' 
WHEN 3 THEN '指定开发' 
WHEN 4 THEN '智能选品' 
WHEN 5 THEN '销售提报_FBA新品' 
WHEN 6 THEN '智选转FBA提报'
END AS 开发类型,
wh.WareHouseName AS '仓库名称',
pdbd.SupplierName AS '供应商名称',
ad.UserName AS '采购员',
dp.DepartmentName AS '组别',
ad2.UserName AS '开发员',
dp2.DepartmentName AS '小组',
CASE WHEN ISNULL(pdbd.NeedToPackType,p.NeedToPackType) IN (2,3) THEN '是' ELSE '否' END '是否打包',
CASE ISNULL(pdbd.NeedToPackType,p.NeedToPackType)
WHEN 0 THEN '不需打包'
WHEN 1 THEN '需供应商打包'
WHEN 2 THEN '加工打包'
WHEN 3 THEN '销单打包'
END 销单打包状态,
pa.OnlineTime AS '上架时间',
pdbd.LastBuyTime AS '销单时间',
DATEDIFF(d,pa.OnlineTime,pdbd.LastBuyTime) AS '上架天数',
CASE WHEN DATEDIFF(d,pa.OnlineTime,pdbd.LastBuyTime) <=365 THEN '是' ELSE '否' END AS '是否一年内终审',
CASE WHEN ISNULL(pdbd.NeedToPackType,p.NeedToPackType) IN (2,3) THEN pdbd.ActualBuyNum ELSE 0 END AS '打包产品数',
pdbd.ActualBuyNum AS '销单产品数',
CAST(psa.SpecIdQuantity AS FLOAT) '1688链接匹配数量',
CAST((CASE WHEN ISNULL(pdbd.NeedToPackType,p.NeedToPackType) IN (2,3) THEN pdbd.ActualBuyNum ELSE 0 END) AS FLOAT)*CAST(ISNULL(psa.SpecIdQuantity,1) AS float) AS '打包产品数(匹配链接数量)',
CAST(pdbd.ActualBuyNum AS FLOAT)*CAST(ISNULL(psa.SpecIdQuantity,1) AS FLOAT) AS '销单产品数(匹配链接数量)',
(CASE WHEN ISNULL(pdbd.NeedToPackType,p.NeedToPackType) IN (2,3) THEN pdbd.ActualBuyNum ELSE 0 END)*1.0 / pdbd.ActualBuyNum as '产品数量占比',
CONVERT(VARCHAR(7),pdbd.LastBuyTime,20) AS '月份'
INTO #temp
FROM RB_Procurement_Detail_BuyList_Detail (nolock) pdbd
INNER JOIN RB_Product (nolock) p on pdbd.CustomerID = p.CustomerID and pdbd.SKU = p.SKU
LEFT JOIN RB_Procurement_Detail_SingleList_Detail(NOLOCK) AS a ON pdbd.CustomerID = a.CustomerID AND pdbd.SingleDetailID = a.ID
LEFT JOIN RB_Procurement_Detail_SingleList(NOLOCK) AS b ON pdbd.CustomerID = b.CustomerID AND a.ListID = b.ID
INNER JOIN RB_Procurement_List (nolock) pl on pdbd.CustomerID = pl.CustomerID and pdbd.P_Code = pl.P_Code
LEFT JOIN dbo.RB_Procurement_Detail(NOLOCK) pd ON pd.CustomerID = pl.CustomerID and pd.P_Code = pl.P_Code AND a.DetailID=pd.ID
LEFT JOIN RB_WareHouse (nolock) wh on pl.CustomerID = wh.CustomerID AND pl.WareHouseID = wh.ID

--LEFT JOIN RB_Procurement_PackageList (nolock) ppl on pdbd.CustomerID = ppl.CustomerID and pdbd.ListNo = ppl.ListNo and pdbd.SKU = ppl.SKU
LEFT JOIN RB_Product_Admin (nolock) pa ON p.CustomerID = pa.CustomerID AND pa.SKU = p.SKU
LEFT JOIN RB_Admin  (NOLOCK)ad ON  pl.BuyerID = ad.ID AND ad.CustomerID = pl.CustomerID
LEFT JOIN dbo.RB_Department (NOLOCK)dp ON dp.ID = ad.DepartmentID AND dp.CustomerID = ad.CustomerID
LEFT JOIN RB_Admin  (NOLOCK)ad2 ON  pa.DevelopAdminID = ad2.ID AND ad2.CustomerID = pa.CustomerID
LEFT JOIN dbo.RB_Department (NOLOCK)dp2 ON dp2.ID = ad2.DepartmentID AND dp2.CustomerID = ad2.CustomerID
LEFT JOIN dbo.RB_Product_SupplierPrice(NOLOCK) ps ON ps.SKU = p.SKU AND ps.CustomerID = p.CustomerID AND ps.IsDefault = 1
LEFT JOIN dbo.RB_Product_Supplier_1688_ProductSkuSpecId (NOLOCK)psa ON psa.CustomerId = ps.CustomerID AND psa.SupplierId = ps.SupplierID AND psa.SKU = ps.SKU
WHERE
pdbd.CustomerID = @CustomerID
and pdbd.LastBuyTime >= @StartTime
and pdbd.LastBuyTime <= @EndTime
AND ISNULL(p.ProductType, 0) = 0
--AND b.Single_NO='12307589'

--SELECT * FROM #temp

DROP TABLE IF EXISTS #temp_detail
SELECT
t.开始时间,
    t.结束时间,
t.月份,
    t.SKU,
    t.销单号,
    t.采购单号,
    t.采购单打包状态,
    t.下单号,
    t.是否样品单,
    t.开发类型,
    t.仓库名称,
    t.供应商名称,
SUM(t.[1688链接匹配数量]) '1688链接匹配数量',
    t.采购员,
    t.组别,
    t.开发员,
    t.小组,
    t.是否打包,
    t.销单打包状态,
    t.上架时间,
    t.销单时间,
    t.上架天数,
    t.是否一年内终审,
    SUM(t.[打包产品数(匹配链接数量)]) '打包产品数(匹配链接数量)',
    SUM(t.[销单产品数(匹配链接数量)]) '销单产品数(匹配链接数量)',
    t.产品数量占比,
t.打包产品数,
t.销单产品数
INTO #temp_detail
FROM #temp AS t
GROUP BY
t.开始时间,
    t.结束时间,
t.月份,
    t.SKU,
    t.销单号,
    t.采购单号,
    t.采购单打包状态,
    t.下单号,
    t.是否样品单,
    t.开发类型,
    t.仓库名称,
    t.供应商名称,
    t.采购员,
    t.组别,
    t.开发员,
    t.小组,
    t.是否打包,
    t.销单打包状态,
    t.上架时间,
    t.销单时间,
    t.上架天数,
    t.是否一年内终审,
    t.产品数量占比,
t.打包产品数,
t.销单产品数

SELECT * FROM #temp_detail

-- 开发人员匹配链接数量汇总表
SELECT
t.月份,
t.开发员,
t.小组 AS '组别',
SUM(t.[打包产品数(匹配链接数量)]) AS '打包产品数',
SUM(t.[销单产品数(匹配链接数量)]) AS '销单产品数',
CONCAT(CONVERT(VARCHAR(10),CONVERT(DECIMAL(18,2),SUM(t.[打包产品数(匹配链接数量)])*1.0/SUM(t.[销单产品数(匹配链接数量)])*100)),'%') 产品数量占比
FROM #temp_detail AS t
WHERE t.上架天数 <= 365 AND t.是否样品单 = '否'
group BY
t.月份,
t.开发员,
t.小组;


-- 采购人员匹配链接数量汇总表
  SELECT
t.月份,
t.采购员,
t.组别,
SUM(t.[打包产品数(匹配链接数量)]) AS '打包产品数',
SUM(t.[销单产品数(匹配链接数量)]) AS '销单产品数',
CONCAT(CONVERT(VARCHAR(10),CONVERT(DECIMAL(18,2),SUM(t.[打包产品数(匹配链接数量)])*1.0/SUM(t.[销单产品数(匹配链接数量)])*100)),'%') 产品数量占比
from  #temp_detail AS t WHERE t.是否样品单 = '否'
group BY
t.月份,
t.采购员,
t.组别;

-- 开发人员汇总
SELECT
t.月份,
t.开发员,
t.小组 AS '组别',
SUM(t.打包产品数) AS '打包产品数',
SUM(t.销单产品数) AS '销单产品数',
CONCAT(CONVERT(VARCHAR(10),CONVERT(DECIMAL(18,2),SUM(t.打包产品数)*1.0/SUM(t.销单产品数)*100)),'%') 产品数量占比
FROM #temp_detail AS t
WHERE t.上架天数 <= 365 AND t.是否样品单 = '否'
group BY
t.月份,
t.开发员,
t.小组;


-- 采购人员汇总
  SELECT
t.月份,
t.采购员,
t.组别,
SUM(t.打包产品数) AS '打包产品数',
SUM(t.销单产品数) AS '销单产品数',
CONCAT(CONVERT(VARCHAR(10),CONVERT(DECIMAL(18,2),SUM(t.打包产品数)*1.0/SUM(t.销单产品数)*100)),'%') 产品数量占比
from  #temp_detail AS t WHERE t.是否样品单 = '否'
group BY
t.月份,
t.采购员,
t.组别;

PARAMS:@StartTime = 2024-08-01 ,@EndTime = 2024-08-19 23:59:59 ,@CustomerId = 1 ,

MESSAGE:Divide by zero error encountered.

The statement has been terminated.

TRACE:   在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

   在 System.Data.SqlClient.SqlDataReader.get_MetaData()

   在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)

   在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

   在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

   在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

   在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

   在 RB.DAL.DBOperator.ExecuteReader(String SQLString, SqlParameter[] cmdParms, Boolean IsWriteDB, Int32 Timeout)   在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms)

   在 RB.DAL.DBOperator.ExecuteReader(String SQLString, SqlParameter[] cmdParms, Boolean IsWriteDB, Int32 Timeout)

   在 Biz.Irobotbox.ReportCentre.CustomReportService.ExportXlsxByDr(String firstFileFullPath, RB_CustomReportTask customReportTask, Dictionary`2 paramList, List`1 fields, Int32 timeOut)

   在 Biz.Irobotbox.ReportCentre.CustomReportService.ExportXlsxBySteam(RB_CustomReportTask customReportTask, Dictionary`2 paramValues, List`1 fieldsList, Int32 timeOut)

   在 Biz.Irobotbox.ReportCentre.CustomReportService.ExecuteCustomReportTask(List`1 customReportList)

   在 System.Threading.Tasks.Task.Execute()

   在 System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   在 System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)

   在 System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecution)

   在 System.Threading.Tasks.Schedulers.LimitedConcurrencyLevelTaskScheduler.<NotifyThreadPoolOfPendingWork>b__6_0(Object _)

   在 System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()

   在 System.Threading.ThreadPoolWorkQueue.Dispatch()

[结果]

[期望]