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

prometheus告警

            开始时间: 17:49:00 

            错误代码: N174900119  

            sql类型: SELECT   

            sql: [17:49:00]  -------------------------------------------------------------

CODE:N174900119

Error:15_137_2_-2146232060

SQL:select top 0 tt1.SKU

,tt1.ActualProductPrice '对比下单价'

,aad.UserName '采购员'

,aad.DepartmentID '采购组'

,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

;

select top 0 t2.AddTime '下单时间'

,t2.Single_NO AS '下单号'

,b.SKU

,t1.ActualProductPrice '下单价'

,t1.ActualBuyNum AS '下单数量'

,t1.ActualProductPricet1.ActualBuyNum '产品总额'

,t2.PayTotalAmount '下单总额' -- (含运费)

,(t2.PayTotalAmount - t1.ActualProductPricet1.ActualBuyNum) '下单运费'

,CASE WHEN ISNULL(t1.ActualBuyNum,0) = 0 THEN 0

ELSE (t2.PayTotalAmount - t1.ActualProductPrice*t1.ActualBuyNum)/(t1.ActualBuyNum)

END 'SKU均摊运费'

,ad.UserName '采购员'

,ad.DepartmentID '采购组'

,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

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

;

select top 0 b1.下单时间

,b1.下单号

,b1.SKU '下单SKU'

,b1.下单价

,b1.下单数量

,b1.产品总额

,b1.下单总额

,b1.下单运费

,b1.SKU均摊运费

,b1.采购员

,b1.采购组

,b1.去年最后一次下单价

,b2.对比下单价 '今年第一次下单价'

,b1.账期类型

INTO #mx

FROM (

select top 0 a1.,MIN(t4.rank) 'rank2'

FROM (

select top 0 c1.

,c2.对比下单时间 去年最后一次下单时间

,c2.对比下单价 去年最后一次下单价

FROM(

select top 0 main.,o1.rank

FROM #main main

LEFT JOIN(

select top 0 m.,MAX(t3.rank) 'rank'

FROM #main m

LEFT JOIN #tt t3 ON m.SKU = t3.SKU AND m.采购员 = t3.采购员 AND m.采购组 = t3.采购组

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 c1.采购员 = c2.采购员 AND c1.采购组 = c2.采购组 AND c2.rank = c1.rank

) a1

LEFT JOIN #tt t4 ON a1.SKU = t4.SKU AND a1.采购员 = t4.采购员 AND a1.采购组 = t4.采购组

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 b1.采购员 = b2.采购员 AND b1.采购组 = b2.采购组 AND b2.rank = b1.rank2

;

select top 0 采购员

,采购组

,SUM((下单价 - (CASE WHEN 去年最后一次下单价 IS NULL THEN 今年第一次下单价 ELSE 去年最后一次下单价 END)) * 下单数量) '降价总额'

,SUM(产品总额) '产品总额'

,SUM( CASE

WHEN ISNULL(产品总额,0) = 0 THEN 0

ELSE ((下单价 - (CASE WHEN 去年最后一次下单价 IS NULL THEN 今年第一次下单价 ELSE 去年最后一次下单价 END)) * 下单数量) / (产品总额)

END

) '降价比率'

,SUM( CASE

WHEN ISNULL(下单数量,0) = 0 THEN 0

ELSE 下单运费 / 下单数量

END

) '产品数量均摊运费'

,SUM(CASE WHEN 账期类型 != '现结'THEN 下单总额 end) '账期下单总额'

,SUM(下单总额) '下单总额'

,SUM( CASE

WHEN ISNULL(下单总额,0) = 0 THEN 0

ELSE (CASE WHEN 账期类型 != '现结'THEN 下单总额 end) / (下单总额)

END

) '账期下单比率'

FROM #mx

GROUP BY 采购员,采购组

;

select top 0 * FROM #mx;

PARAMS:@CustomerId = 0 ,

MESSAGE:Must declare the scalar variable "@StartTime".

TRACE: 在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) 在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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, TaskCompletionSource1 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) 在 RB.DALExtend.RB_CustomReportExtend.GetSheetFieldsDr(String sqlContent, List1 sqlParameters)

在 Biz.Irobotbox.ReportCentre.CustomReportService.CheckSqlAndGetFields(String sqlContent, List`1 paramList)

在 PandaBuy.ERP.Web.IrobotBox.ReportCentre.CustomReportEdit.CheckSqlAndGetFields()

在 PandaBuy.ERP.Web.IrobotBox.ReportCentre.CustomReportEdit.Page_Load(Object sender, EventArgs e)

在 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)

在 System.Web.UI.Control.OnLoad(EventArgs e)

在 System.Web.UI.Control.LoadRecursive()

在 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

在 System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

在 System.Web.UI.Page.ProcessRequest()

在 System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)

在 System.Web.UI.Page.ProcessRequest(HttpContext context)

在 ASP.irobotbox_reportcentre_customreportedit_aspx.ProcessRequest(HttpContext context)

在 System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()

在 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)

在 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

在 System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)

在 System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)

在 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)

在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

---