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

[步骤]

[15:25:47]  -------------------------------------------------------------
CODE:N15254765
Error:15_156_1_-2146232060
SQL:-- declare @StartTime datetime= '2022-09-01' ,@EndTime datetime= '2022-09-30 23:59:59' ,@CustomerId int= 1 
SELECT tt1.SKU
,tt1.ActualProductPrice '对比下单价'
,aad.UserName '采购员'
,dn.DepartmentName '采购组'
,tt2.AddTime '对比下单时间'
,rank()over(ORDER BY tt2.AddTime) 'rank'
INTO #tt
FROM RB_Procurement_Detail_SingleList_Detail(NOLOCK) AS tt1
LEFT JOIN dbo.RB_Procurement_Detail_SingleList ( NOLOCK ) tt2 ON tt1.CustomerID = tt2.CustomerID AND tt1.ListID = tt2.ID 
LEFT JOIN dbo.RB_Admin ( NOLOCK ) aad ON tt2.BuyerID=aad.ID
LEFT JOIN RB_Department(NOLOCK) AS dn ON aad.DepartmentID = dn.ID
WHERE aad.DepartmentID = SELECT item FROM dbo.CharSplitForIn(@DepartmentID,',') 
;

SELECT t2.AddTime '下单时间'
,t2.Single_NO AS '下单号'
,b.SKU 
,t1.ActualProductPrice '下单价'
,t1.ActualBuyNum AS '下单数量'
,t1.ActualProductPrice*t1.ActualBuyNum '产品总额'
,t2.PayTotalAmount '下单总额' -- (含运费)
,t2.ShipAmount '下单运费'
,CASE WHEN ISNULL(yf.下单号产品总数,0) = 0 THEN 0
ELSE ROUND(t2.ShipAmount/yf.下单号产品总数*t1.ActualBuyNum,2)
END 'SKU均摊运费'
,ad.UserName '采购员'
,dn.DepartmentName '采购组'
,c2.PaymentName '账期类型'
INTO #main
FROM RB_Procurement_Detail_SingleList(NOLOCK) AS t2 -- 采购下单产品表
LEFT JOIN  RB_Procurement_Detail(NOLOCK) AS b ON t2.CustomerID = b.CustomerID AND b.P_Code = t2.P_Code 
LEFT JOIN RB_Procurement_List(NOLOCK) AS b2 ON b.CustomerID = b2.CustomerID AND b.P_Code = b2.P_Code
LEFT JOIN dbo.RB_Product_Supplier(NOLOCK) AS c1 ON b.CustomerID = c1.CustomerID AND b2.SupplierID = c1.ID
LEFT JOIN RB_Product_Supplier_PaymentDay(NOLOCK) AS c2 ON c1.PaymentDayNew = c2.ID
LEFT JOIN RB_Procurement_Detail_SingleList_Detail(NOLOCK) AS t1 ON t2.CustomerID = t1.CustomerID AND t1.SKU = b.SKU AND t1.ListID = t2.ID -- 采购下单产品明细表
LEFT JOIN dbo.RB_Admin ( NOLOCK ) ad ON t2.BuyerID=ad.ID
LEFT JOIN RB_Department(NOLOCK) AS dn ON ad.DepartmentID = dn.ID
LEFT JOIN(SELECT yf1.Single_NO,SUM(yf3.ActualBuyNum) '下单号产品总数' FROM RB_Procurement_Detail_SingleList(NOLOCK) yf1
LEFT JOIN  RB_Procurement_Detail(NOLOCK) yf2 ON yf1.CustomerID = yf2.CustomerID AND yf2.P_Code = yf1.P_Code
LEFT JOIN RB_Procurement_List(NOLOCK) AS bb2 ON yf2.CustomerID = bb2.CustomerID AND yf2.P_Code = bb2.P_Code
LEFT JOIN RB_Procurement_Detail_SingleList_Detail(NOLOCK) AS yf3 ON yf1.CustomerID = yf3.CustomerID AND yf3.SKU = yf2.SKU AND yf3.ListID = yf1.ID
WHERE yf1.CustomerID = 1 AND ISNULL(bb2.IsSample,0) != 1 AND bb2.FinishStatus = 1
GROUP BY yf1.Single_NO) yf
ON yf.Single_NO = t2.Single_NO

WHERE t1.CustomerID = 1 
AND ISNULL(b2.IsSample,0) != 1 -- 是否样品单
AND b2.FinishStatus = 1 --采购单完结状态
AND t2.CustomerId = @CustomerId
AND t2.AddTime >= @StartTime 
AND t2.AddTime <= @EndTime 
AND ad.DepartmentID = SELECT item FROM dbo.CharSplitForIn(@DepartmentID,',')  
;

SELECT b1.下单时间
,b1.下单号
,b1.SKU '下单SKU'
,b1.下单价
,b1.下单数量
,b1.产品总额
,b1.下单总额
,b1.下单运费
,b1.SKU均摊运费
,b1.采购员
,b1.采购组
,b1.去年最后一次下单价  
,b2.对比下单价 '今年第一次下单价'
,b1.账期类型
--,b1.去年最后一次下单时间 
--,b2.对比下单时间 今年第一次下单时间
,((CASE WHEN b1.去年最后一次下单价 IS NULL THEN b2.对比下单价 ELSE b1.去年最后一次下单价 END)-b1.下单价) * b1.下单数量 '降价金额'
INTO #mx
FROM (
SELECT a1.*,MIN(t4.rank) 'rank2'
FROM (
SELECT c1.*
,c2.对比下单时间 去年最后一次下单时间
,c2.对比下单价 去年最后一次下单价 
FROM(
SELECT main.*,o1.rank 
FROM #main main
LEFT JOIN(
SELECT m.*,MAX(t3.rank) 'rank'
FROM #main  m
LEFT JOIN #tt t3 ON m.SKU = t3.SKU 
WHERE YEAR(t3.对比下单时间) = YEAR(m.下单时间) - 1
GROUP BY m.下单时间
,m.下单号
,m.SKU
,m.下单价
,m.下单数量
,m.产品总额
,m.下单总额
,m.下单运费
,m.SKU均摊运费
,m.采购员
,m.采购组
,m.账期类型
) o1 ON o1.SKU = main.SKU AND o1.下单号 = main.下单号 AND o1.采购员 = main.采购员 AND o1.采购组 = main.采购组
)c1
LEFT JOIN #tt c2 ON c1.SKU = c2.SKU AND c2.rank = c1.rank
) a1 
LEFT JOIN #tt t4 ON a1.SKU = t4.SKU 
WHERE YEAR(t4.对比下单时间) = YEAR(a1.下单时间)
GROUP BY a1.下单时间
,a1.下单号
,a1.SKU
,a1.下单价
,a1.下单数量
,a1.产品总额
,a1.下单总额
,a1.下单运费
,a1.SKU均摊运费
,a1.采购员
,a1.采购组
,a1.账期类型
,a1.rank
,a1.去年最后一次下单时间 
,a1.去年最后一次下单价  
) b1
LEFT JOIN #tt b2 ON b1.SKU = b2.SKU AND b2.rank = b1.rank2
;



SELECT a1.采购员
,a2.采购组
,a1.降价总额
,a1.产品总额
,a1.降价比率
,ROUND(a2.下单总运费/a1.下单总数量,2) '产品数量均摊运费'
,a2.账期下单总额
,a2.下单总额
,a2.账期下单比率
from(
SELECT 采购员
,采购组
,SUM(降价金额) '降价总额'
,SUM(产品总额) '产品总额'
,CONVERT(varchar(50), ROUND((
CAST(SUM(降价金额)AS FLOAT)/CAST(SUM(产品总额) AS FLOAT)
)*100 ,2)) + '%' '降价比率'
, SUM(下单数量) '下单总数量'
FROM #mx
GROUP BY 采购员,采购组
) a1 
LEFT JOIN(
SELECT 采购员
,采购组
,SUM(CASE WHEN 账期类型 = '现结'THEN 0 ELSE 下单总额 end) '账期下单总额'
,SUM(下单总额) '下单总额'
,CONVERT(varchar(50), ROUND((
CAST(SUM(CASE WHEN 账期类型 != '现结'THEN 下单总额 end) AS FLOAT)/CAST(SUM(下单总额) AS FLOAT)
)*100 ,2)) + '%' '账期下单比率'
,SUM(下单运费) '下单总运费' 
FROM(
SELECT 采购员,采购组,下单号,下单总额,账期类型,下单运费 
FROM #mx
GROUP BY 采购员,采购组,下单号,下单总额,账期类型,下单运费
)a
GROUP BY 采购员,采购组
)a2
ON a1.采购员=a2.采购员 AND a2.采购组 = a1.采购组
;


SELECT * FROM #mx;


PARAMS:@StartTime = 2020-01-01 ,@EndTime = 2020-12-31 23:59:59 ,@DepartmentID = 586 ,@CustomerId = 1 ,
MESSAGE:Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near the keyword 'SELECT'.
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()

[结果]

[期望]