[步骤]
CODE:N1735187Error:15_137_2_-2146232060
SQL:DECLARE @nowEndDate DATETIME;
SET @nowEndDate = DATEADD(s, -1, DATEADD(m, 1, @nowDate)); --本月结束
IF OBJECT_ID('Tempdb..#TempPriceRatio') IS NOT NULL
DROP TABLE #TempPriceRatio;
CREATE TABLE #TempPriceRatio
(
MinPrice DECIMAL(18, 2),
MaxPrice DECIMAL(18, 2),
Ratio DECIMAL(18, 2),
);
INSERT INTO #TempPriceRatio
VALUES
(0, 30000, 1),
(30000, 50000, 0.8),
(50000, 80000, 0.6),
(80000, 9999999999, 0.4);
IF OBJECT_ID('Tempdb..#TempProcurementBuyList') IS NOT NULL
DROP TABLE #TempProcurementBuyList;
CREATE TABLE #TempProcurementBuyList
(
ID INT PRIMARY KEY NOT NULL IDENTITY(1, 1),
CustomerID INT,
P_Code NVARCHAR(1000) COLLATE Chinese_PRC_CI_AS,
Single_NO VARCHAR(50) COLLATE Chinese_PRC_CI_AS,
BuyerID INT,
BuyerName NVARCHAR(20) COLLATE Chinese_PRC_CI_AS,
SKU NVARCHAR(10) COLLATE Chinese_PRC_CI_AS,
ProductTitle NVARCHAR(500) COLLATE Chinese_PRC_CI_AS, ClientSKU NVARCHAR(50) COLLATE Chinese_PRC_CI_AS, isPackagePrice INT, LowestDate DATETIME, LowesBuyMoney MONEY, HistoryLowestDate DATETIME, HistoryLowePrice MONEY, LastIsTaxFree INT, NowDate DATETIME, NowBuyNum INT, NowTotalBuyMoney MONEY, NowAvgPurchasePrice MONEY, NowAvgPurchasePrice1 MONEY, ReductionTotalMoney MONEY, --降价金额 HistoryReductionTotalMoney MONEY, --历史降价金额 NowIsTaxFree INT, --是否退税产品 SupplierPrice MONEY, --默认供货价 FirstDate DATE, ProductPrice MONEY, AddTime DATETIME, isFirst INT, SingleDetailID INT ); INSERT INTO #TempProcurementBuyList ( CustomerID, BuyerID, BuyerName, SingleDetailID, P_Code, SKU, isPackagePrice, LowestDate, LowesBuyMoney, HistoryLowestDate, HistoryLowePrice, NowDate, NowBuyNum, NowAvgPurchasePrice, NowAvgPurchasePrice1, NowIsTaxFree, Single_NO, ProductPrice, FirstDate, AddTime ) SELECT d.CustomerID, l.BuyerID, l.BuyerName, s.ID SingleDetailID, d.P_Code, s.SKU, -- 是否包装涨价？？？，这个字段是什么意思 s.isPackagePrice, MIN( CASE WHEN isPackagePrice = 1 THEN s.AddTime ELSE NULL END ), MIN( CASE WHEN isPackagePrice = 1 THEN s.ActualProductPrice ELSE NULL END), MIN( CASE WHEN isPackagePrice = 1 THEN s.AddTime ELSE NULL END), MIN( CASE WHEN isPackagePrice = 1 THEN s.ActualProductPrice ELSE NULL END), s.AddTime, ISNULL(pl.PutNum, 0), --s.ActualBuyNum MIN(s.ActualProductPrice), MIN(s.ActualProductPrice), MAX(ISNULL(wh.IsTaxFree, 0)), -- 下单号字段都能拿来聚合啊 MAX(Single_NO), MIN(d.ProductPrice), MIN(ISNULL(l.ManuallyFinishTime, l.FinishTime)), MIN(l.AddTime) FROM RB_Procurement_Detail d (NOLOCK) INNER JOIN RB_Procurement_List l (NOLOCK) ON d.P_Code = l.P_Code AND d.CustomerID = l.CustomerID -- 下单主表 LEFT JOIN RB_Procurement_Detail_SingleList ds (NOLOCK) ON d.P_Code = ds.P_Code AND d.CustomerID = ds.CustomerID AND ISNULL(ds.IsDelete, 0) = 0 AND ds.StepState <> -9 -- 下单明细表 INNER JOIN RB_Procurement_Detail_SingleList_Detail s (NOLOCK) ON d.ID = s.DetailID AND d.SKU = s.SKU AND d.CustomerID = s.CustomerID AND ds.ID = s.ListID LEFT JOIN RB_WareHouse wh (NOLOCK) ON l.WareHouseID = wh.ID AND l.CustomerID = wh.CustomerID -- 供应商表 LEFT JOIN RB_Product_Supplier PS (NOLOCK) ON PS.CustomerID = l.CustomerID AND PS.ID = l.SupplierID LEFT JOIN ( SELECT CustomerID, P_Code, SKU, SingleDetailID, SUM(PutNum) AS PutNum -- 入库表 FROM RB_Procurement_Detail_PutList AS p (NOLOCK) WHERE p.CustomerID = @CustomerID GROUP BY CustomerID, P_Code, SKU, SingleDetailID ) AS pl ON pl.CustomerID = d.CustomerID AND pl.P_Code = d.P_Code AND pl.SKU = d.SKU AND pl.SingleDetailID = s.ID LEFT JOIN ( SELECT DISTINCT CustomerID, P_Code, SKU, 1 AS HasMissing FROM RB_Procurement_Detail_PutList AS p (NOLOCK) WHERE p.CustomerID = @CustomerID -- 这一步的筛选条件好奇怪，怎么会有销单明细表里有数据，但是下单表里面没有数据的呢 AND ( p.SingleDetailID = 0 OR p.SingleDetailID IS NULL ) ) AS pl2 ON pl2.CustomerID = d.CustomerID AND pl2.P_Code = d.P_Code AND pl2.SKU = d.SKU WHERE FinishStatus = 1 AND d.CustomerID = @CustomerID -- 这里的ManuallyFinishTime是什么意思？？？ --111111111111111111111 AND ISNULL(l.ManuallyFinishTime, l.FinishTime) BETWEEN @nowDate AND @nowEndDate AND pl.PutNum > 0 AND PS.SupplierType <> 4 --排除自主生产的产品 GROUP BY d.CustomerID, l.BuyerID, l.BuyerName, d.P_Code, ds.ID, s.SKU, ISNULL(pl.PutNum, 0), s.AddTime, s.isPackagePrice, s.ID; --更新最低价状态 UPDATE #t SET --#t.isPackagePrice = 1, #t.LowestDate = #t.NowDate, #t.LowesBuyMoney = #t.NowAvgPurchasePrice --#t.HistoryLowestDate = #t.NowDate, --#t.HistoryLowePrice = #t.NowAvgPurchasePrice FROM #TempProcurementBuyList #t LEFT JOIN ( SELECT SKU, MIN(LowestDate) AS mindate FROM #TempProcurementBuyList WHERE isPackagePrice = 1 GROUP BY SKU ) ss ON ss.SKU = #t.SKU WHERE ss.SKU IS NOT NULL AND #t.AddTime > ss.mindate; --获取税后价 UPDATE #TempProcurementBuyList SET NowAvgPurchasePrice = NowAvgPurchasePrice / (1 + ( SELECT TaxPoint FROM RB_Product_SupplierPrice (NOLOCK) WHERE CustomerID = #TempProcurementBuyList.CustomerID AND SKU = #TempProcurementBuyList.SKU AND IsDefault = 1 AND IsNew = 1 )/100 ) WHERE CustomerID = @CustomerID AND NowIsTaxFree = 1; -------------------下单价变成平均价 UPDATE #t SET NowAvgPurchasePrice = hh.NowAvgPurchasePrice1 FROM #TempProcurementBuyList #t LEFT JOIN ( SELECT s.SKU, s.BuyerName, CASE WHEN SUM(s.NowBuyNum) = 0 THEN AVG(NowAvgPurchasePrice) ELSE -- 应该是每一次下单采购的价格都不一样 -- 这里的NowBuyNum其实是每次入库的数量 SUM(s.NowAvgPurchasePrice * s.NowBuyNum) / SUM(s.NowBuyNum) END AS NowAvgPurchasePrice1 FROM #TempProcurementBuyList s WHERE s.LowestDate IS NULL GROUP BY s.SKU, s.BuyerName ) hh ON hh.SKU = #t.SKU AND hh.BuyerName = #t.BuyerName WHERE #t.LowestDate IS NULL; -------------------------------更新最低价 UPDATE #t SET #t.LowestDate = (CASE WHEN SingleTime IS NULL THEN #t.NowDate ELSE SingleTime END ), #t.LowesBuyMoney = (CASE WHEN MixProductPrice IS NULL THEN 0 ELSE MixProductPrice END ), #t.ReductionTotalMoney = ((CASE WHEN MixProductPrice IS NULL THEN #t.NowAvgPurchasePrice ELSE MixProductPrice END ) - NowAvgPurchasePrice ) * NowBuyNum, #t.NowTotalBuyMoney = #t.NowAvgPurchasePrice * #t.NowBuyNum FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY t.CustomerID, t.Single_NO, t.SKU, t.BuyerID ORDER BY m.SingleTime DESC ) AS rowid, t.*, MixProductPrice, m.SingleTime --最低销单时间 FROM #TempProcurementBuyList t (NOLOCK) -- RB_Procurement_Detail_BuyList_MixPrice：历史最低销单价统计 LEFT JOIN RB_Procurement_Detail_BuyList_MixPrice m (NOLOCK) ON t.SKU = m.SKU AND t.CustomerID = m.CustomerID LEFT JOIN dbo.RB_Procurement_Detail_SingleList(NOLOCK) pds ON pds.CustomerID = m.CustomerID AND m.SingleNo=pds.Single_NO LEFT JOIN dbo.RB_Procurement_List(NOLOCK) pl ON pl.CustomerID = pds.CustomerID AND pl.P_Code = pds.P_Code WHERE t.CustomerID = 1 -- 这个筛选条件不是很能理解，采购清单的完结状态很重要吗？？ AND ((m.ListNo!='' AND pl.FinishStatus=1 AND pl.FinishTime<@nowDate AND t.LowestDate IS NULL) OR (ISNULL(m.ListNo,'')='' AND m.SingleTime<t.NowDate AND ISNULL(t.isPackagePrice,0)!=1)) AND t.P_Code <> ISNULL(pl.P_Code, '') ) AS #t WHERE rowid = 1; UPDATE #t SET #t.HistoryLowestDate = (CASE WHEN SingleTime IS NULL THEN #t.NowDate ELSE SingleTime END ), #t.HistoryLowePrice = (CASE WHEN MixProductPrice IS NULL THEN 0 ELSE MixProductPrice END ), #t.HistoryReductionTotalMoney = ((CASE WHEN MixProductPrice IS NULL THEN #t.NowAvgPurchasePrice ELSE MixProductPrice END ) - NowAvgPurchasePrice ) * NowBuyNum FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY t.CustomerID, t.Single_NO, t.SKU, t.BuyerID ORDER BY m.MixProductPrice ASC ) AS rowid, t.*, MixProductPrice, m.SingleTime --最低销单时间 FROM #TempProcurementBuyList t (NOLOCK) LEFT JOIN RB_Procurement_Detail_BuyList_MixPrice m (NOLOCK) ON t.SKU = m.SKU AND t.CustomerID = m.CustomerID LEFT JOIN dbo.RB_Procurement_Detail_SingleList(NOLOCK) pds ON pds.CustomerID = m.CustomerID AND m.SingleNo=pds.Single_NO LEFT JOIN dbo.RB_Procurement_List(NOLOCK) pl ON pl.CustomerID = pds.CustomerID AND pl.P_Code = pds.P_Code WHERE t.CustomerID = 1 AND (( m.ListNo!='' AND pl.FinishStatus=1 AND pl.FinishTime<@nowDate --AND t.HistoryLowestDate IS NULL ) OR (ISNULL(m.ListNo,'')='' AND m.SingleTime<t.NowDate AND ISNULL(t.isPackagePrice,0)!=1)) AND t.P_Code <> ISNULL(pl.P_Code, '') ) AS #t WHERE rowid = 1; UPDATE #t SET LowestDate = NowDate, LowesBuyMoney = (CASE WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate < DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice11 WHEN hh.minNowDate >= DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice12 WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice11 END ), HistoryLowePrice = (CASE WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate < DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice11 WHEN hh.minNowDate >= DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice12 WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice11 END ), NowAvgPurchasePrice = (CASE WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate < DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice11 WHEN hh.minNowDate >= DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice12 WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) AND NowDate < DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice11 WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) AND NowDate >= DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice12 END ), ReductionTotalMoney = ((CASE WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate < DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice11 WHEN hh.minNowDate >= DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice12 WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice11 END ) - (CASE WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate < DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice11 WHEN hh.minNowDate >= DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice12 WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) AND NowDate < DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice11 WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) AND hh.maxNowDate >= DATEADD(DAY, 15, @nowDate) AND NowDate >= DATEADD(DAY, 15, @nowDate) THEN hh.NowAvgPurchasePrice12 END ) ) * NowBuyNum, #t.HistoryReductionTotalMoney = ((CASE WHEN hh.minNowDate < DATEADD(DAY, 15, @nowDate) &
