[步骤]
prometheus告警
开始时间: 10:33:55
错误代码: N103355358
sql类型: DECLARE
sql: [10:33:55] -------------------------------------------------------------
CODE:N103355358
Error: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)
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,
NowTotalBuyMoney = (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
FROM #TempProcurementBuyList #t
LEFT JOIN
(
SELECT s.SKU,
s.BuyerName,
MIN(NowDate) AS minNowDate,
MAX(NowDate) AS maxNowDate,
CASE
WHEN SUM( CASE
WHEN s.NowDate < DATEADD(DAY, 15, @nowDate) THEN
s.NowBuyNum
ELSE
0
END
) = 0 THEN
AVG( CASE
WHEN s.NowDate < DATEADD(DAY, 15, @nowDate) THEN
NowAvgPurchasePrice1
ELSE
NULL
END
)
ELSE
SUM( CASE
WHEN s.NowDate < DATEADD(DAY, 15, @nowDate) THEN
s.NowAvgPurchasePrice1 * s.NowBuyNum
ELSE
0
END
) / SUM( CASE
WHEN s.NowDate < DATEADD(DAY, 15, @nowDate) THEN
s.NowBuyNum
ELSE
0
END
)
END AS NowAvgPurchasePrice11,
CASE
WHEN SUM( CASE
WHEN s.NowDate >= DATEADD(DAY, 15, @nowDate) THEN
s.NowBuyNum
ELSE
0
END
) = 0 THEN
AVG( CASE
WHEN s.NowDate >= DATEADD(DAY, 15, @nowDate) THEN
NowAvgPurchasePrice1
ELSE
NULL
END
)
ELSE
SUM( (CASE
WHEN s.NowDate >= DATEADD(DAY, 15, @nowDate) THEN
s.NowAvgPurchasePrice1 * s.NowBuyNum
ELSE
0
END
)
) / SUM( CASE
WHEN s.NowDate >= DATEADD(DAY, 15, @nowDate) THEN
s.NowBuyNum
ELSE
0
END
)
END AS NowAvgPurchasePrice12
FROM #TempProcurementBuyList s
WHERE s.NowTotalBuyMoney IS NULL
GROUP BY s.SKU,
s.BuyerName
) hh
ON hh.SKU = #t.SKU
AND hh.BuyerName = #t.BuyerName
WHERE #t.NowTotalBuyMoney IS NULL;
UPDATE #TempProcurementBuyList
SET LowesBuyMoney = NowAvgPurchasePrice,
HistoryLowePrice = NowAvgPurchasePrice,
LowestDate = NowDate,
ReductionTotalMoney = (NowAvgPurchasePrice - NowAvgPurchasePrice) * NowBuyNum,
HistoryReductionTotalMoney = (NowAvgPurchasePrice - NowAvgPurchasePrice) * NowBuyNum,
NowTotalBuyMoney = NowAvgPurchasePrice * NowBuyNum
WHERE CustomerID = @CustomerID
AND NowTotalBuyMoney IS NULL;
UPDATE #t
SET isFirst = CASE
WHEN m.ID IS NULL THEN
1
ELSE
0
END
FROM #TempProcurementBuyList #t
LEFT JOIN RB_Procurement_Detail_BuyList_MixPrice m
ON #t.SKU = m.SKU
AND #t.CustomerID = m.CustomerID
AND #t.NowDate > m.SingleTime
AND m.SingleNo NOT IN
(
SELECT bl.Single_NO
FROM dbo.RB_Procurement_Detail_BuyList AS bl (NOLOCK)
WHERE bl.CustomerID = #t.CustomerID
AND #t.P_Code = bl.P_Code
)
WHERE #t.CustomerID = @CustomerID;
DECLARE @BuyerSummary TABLE
( --采购人员汇总
CustomerID INT,
BuyerID INT,
采购人员 NVARCHAR(20) COLLATE Chinese_PRC_CI_AS,
降价总额 MONEY,
历史降价总额 MONEY,
非首次下单总金额 MONEY,
首次下单总金额 MONEY,
公司降价目标 MONEY,
降价提成 MONEY,
涨价总额 MONEY,
涨价提成 MONEY,
默认采购提成 MONEY,
降价净值 MONEY
);
SELECT a.CustomerID,
a.P_Code,
a.Single_NO,
a.BuyerID,
a.BuyerName,
a.SKU,
a.ProductTitle,
a.ClientSKU,
a.isPackagePrice,
a.LowestDate,
a.LowesBuyMoney,
a.HistoryLowePrice,
a.LastIsTaxFree,
a.NowDate,
-- 需要用到！！！
a.NowBuyNum,
a.NowTotalBuyMoney,
-- 需要用到！！！
a.NowAvgPurchasePrice,
a.NowAvgPurchasePrice1,
a.ReductionTotalMoney *
(CASE WHEN a.ReductionTotalMoney >0 THEN (
SELECT TOP 1
Ratio
FROM #TempPriceRatio
WHERE NowTotalBuyMoneySum >= MinPrice
AND NowTotalBuyMoneySum < MaxPrice
)
ELSE 1 END ) AS ReductionTotalMoney,
a.HistoryReductionTotalMoney *
(
SELECT TOP 1
Ratio
FROM #TempPriceRatio
WHERE NowTotalBuyMoneySum >= MinPrice
AND NowTotalBuyMoneySum < MaxPrice
) AS HistoryReductionTotalMoney,
a.NowIsTaxFree,
a.SupplierPrice,
a.FirstDate,
a.ProductPrice,
a.AddTime,
a.isFirst,
a.SingleDetailID,
a.NowTotalBuyMoneySum,
-- 需要用到！！！
(
SELECT TOP 1
Ratio
FROM #TempPriceRatio
WHERE NowTotalBuyMoneySum >= MinPrice
AND NowTotalBuyMoneySum < MaxPrice
) AS '入库总金额系数'
INTO #TempProcurementBuyList2
FROM
(
SELECT *,
SUM(NowTotalBuyMoney) OVER (PARTITION BY BuyerName, SKU) AS NowTotalBuyMoneySum
FROM #TempProcurementBuyList
WHERE CustomerID = @CustomerID
) a
UPDATE #t
SET ClientSKU = p.ClientSKU,
ProductTitle = p.ProductNameCN
FROM #TempProcurementBuyList2 #t (NOLOCK)
LEFT JOIN RB_Product (NOLOCK) p
ON #t.SKU = p.SKU
AND #t.CustomerID = p.CustomerID
WHERE #t.CustomerID = @CustomerID;
--默认供货价
UPDATE #t
SET SupplierPrice = p.SupplierPrice
FROM #TempProcurementBuyList2 #t (NOLOCK)
LEFT JOIN RB_Product_SupplierPrice (NOLOCK) p
ON #t.SKU = p.SKU
AND #t.CustomerID = p.CustomerID
AND p.IsDefault = 1
AND p.IsNew = 1
WHERE #t.CustomerID = @CustomerID;
SELECT CAST(FirstDate AS DATE) AS 完结时间,
a.Single_NO AS 下单号,
SingleDetailID AS 下单SKU条码,
P_Code AS 采购单号,
BuyerName AS 采购人员,
dp.DepartmentName '采购组别',
a.SKU AS 系统SKU,
ClientSKU AS 自定义SKU,
ProductTitle AS 产品中文名,
CASE WHEN ((CAST(t1.SingleTime AS DATE)<DATEADD(day,-730,convert(varchar(100),getdate(),23)) OR (CAST(t1.SingleTime AS DATE) IS NULL)) AND LowesBuyMoney>ISNULL(t4.MixProductPrice,t3.MixProductPrice)) THEN CAST(ISNULL(t4.SingleTime,t3.SingleTime) AS DATE) ELSE CAST(LowestDate AS DATE) END AS 最低采购价时间,--随历史最低价改变
CASE WHEN ((CAST(t1.SingleTime AS DATE)<DATEADD(day,-730,convert(varchar(100),getdate(),23)) OR (CAST(t1.SingleTime AS DATE) IS NULL)) AND LowesBuyMoney>ISNULL(t4.MixProductPrice,t3.MixProductPrice)) THEN ISNULL(t4.MixProductPrice,t3.MixProductPrice) ELSE a.LowesBuyMoney END AS 最低采购价,--1.最近两年内没人工过滤；2.不大于历史最低价
CAST(NowDate AS DATE) AS 下单时间,
NowBuyNum AS [入库总数量],
NowAvgPurchasePrice1 AS '原下单价',
NowAvgPurchasePrice AS 平均下单价,
NowTotalBuyMoney AS 入库总金额,
((CASE WHEN ((CAST(t1.SingleTime AS DATE)<DATEADD(day,-730,convert(varchar(100),getdate(),23)) OR (CAST(t1.SingleTime AS DATE) IS NULL)) AND LowesBuyMoney>ISNULL(t4.MixProductPrice,t3.MixProductPrice)) THEN ISNULL(t4.MixProductPrice,t3.MixProductPrice) ELSE a.LowesBuyMoney END) - NowAvgPurchasePrice)NowBuyNum入库总金额系数 AS 降价总金额,
--随历史最低价改变
SupplierPrice AS 生单价,
CASE isFirst
WHEN 1 THEN
'是'
ELSE
'否'
END AS 是否首月采购,
入库总金额系数,
t.MixProductPrice 近一年最低价,
CAST(t.SingleTime AS DATE) 近一年最低价下单时间,
-- 更改字段
ISNULL(t2.MixProductPrice,t3.MixProductPrice) 近一年半最低价,
CAST(ISNULL(t2.SingleTime,t3.SingleTime) AS DATE) 近一年半最低价下单时间,
t1.MixProductPrice 最近一次人工过滤价,
CAST(t1.SingleTime AS DATE) 最近一次人工过滤价下单时间,
ISNULL(t4.MixProductPrice,t3.MixProductPrice) 历史最低价,
(ISNULL(t4.MixProductPrice,t3.MixProductPrice) - NowAvgPurchasePrice)* NowBuyNum * 入库总金额系数 历史最低价降价总额,
-- 近一年半降价总额
(ISNULL(t2.MixProductPrice,t3.MixProductPrice) - NowAvgPurchasePrice) * NowBuyNum * 入库总金额系数 近一年半降价总额
INTO #temp1
FROM #TempProcurementBuyList2 (NOLOCK)a
LEFT JOIN dbo.RB_Admin(NOLOCK) AS ad ON a.CustomerID = ad.CustomerID AND a.BuyerID = ad.ID
LEFT JOIN dbo.RB_Department(NOLOCK) AS dp ON a.CustomerID = dp.CustomerID AND ad.DepartmentID = dp.ID
LEFT JOIN
(
SELECT a.CustomerID,
a.SKU,
b.Single_NO,
b.AddTime SingleTime,
a.ActualProductPrice MixProductPrice,
ROW_NUMBER() OVER (PARTITION BY a.SKU ORDER BY a.ActualProductPrice) AS rowid
FROM RB_Procurement_Detail_BuyList_Detail (NOLOCK) a
LEFT JOIN dbo.RB_Procurement_Detail_BuyList (NOLOCK) d
ON a.ListNo=d.ListNo AND a.CustomerID=d.CustomerID
LEFT JOIN dbo.RB_Procurement_Detail_SingleList (NOLOCK) b
ON b.CustomerID = d.CustomerID
AND b.Single_NO = d.Single_NO
LEFT JOIN dbo.RB_Procurement_List (NOLOCK) c
ON c.CustomerID = a.CustomerID
AND c.P_Code = b.P_Code
WHERE a.CustomerID = @CustomerID
AND c.FinishTime < @nowDate
AND b.Single_NO IS NOT NULL
AND b.AddTime >= DATEADD(YEAR, -1, @nowDate)
) t
ON t.SKU = a.SKU
AND t.rowid = 1
LEFT JOIN
(
SELECT a.CustomerID,
a.SKU,
b.Single_NO,
b.AddTime SingleTime,
a.ActualProductPrice MixProductPrice,
ROW_NUMBER() OVER (PARTITION BY a.SKU ORDER BY a.ActualProductPrice) AS rowid
FROM RB_Procurement_Detail_BuyList_Detail (NOLOCK) a
LEFT JOIN dbo.RB_Procurement_Detail_BuyList (NOLOCK) d
ON a.ListNo=d.ListNo AND a.CustomerID=d.CustomerID
LEFT JOIN dbo.RB_Procurement_Detail_SingleList (NOLOCK) b
ON b.CustomerID = d.CustomerID
AND b.Single_NO = d.Single_NO
LEFT JOIN dbo.RB_Procurement_List (NOLOCK) c
ON c.CustomerID = a.CustomerID
AND c.P_Code = b.P_Code
WHERE a.CustomerID = @CustomerID
AND c.FinishTime < @nowDate
AND b.Single_NO IS NOT NULL
AND b.AddTime >= DATEADD(MONTH, -18, @nowDate)
) t2
ON t2.SKU = a.SKU
AND t2.rowid = 1
LEFT JOIN
(
SELECT a.CustomerID,
a.SKU,
b.Single_NO,
b.AddTime SingleTime,
a.ActualProductPrice MixProductPrice,
ROW_NUMBER() OVER (PARTITION BY a.SKU ORDER BY a.ActualProductPrice) AS rowid
FROM RB_Procurement_Detail_BuyList_Detail (NOLOCK) a
LEFT JOIN dbo.RB_Procurement_Detail_BuyList (NOLOCK) d
ON a.ListNo=d.ListNo AND a.CustomerID=d.CustomerID
LEFT JOIN dbo.RB_Procurement_Detail_SingleList (NOLOCK) b
ON b.CustomerID = d.CustomerID
AND b.Single_NO = d.Single_NO
LEFT JOIN dbo.RB_Procurement_List (NOLOCK) c
ON c.CustomerID = a.CustomerID
AND c.P_Code = b.P_Code
WHERE a.CustomerID = @CustomerID
-- AND c.FinishTime < @nowDate
AND b.Single_NO IS NOT NULL
-- AND b.AddTime >= @nowDate
--AND b.AddTime <= @nowEndDate
AND ISNULL(c.ManuallyFinishTime, c.FinishTime)
BETWEEN @nowDate AND @nowEndDate
) t3
ON t3.SKU = a.SKU
AND t3.rowid = 1
LEFT JOIN (SELECT CustomerID,SKU,SingleNo,SingleTime,MixProductPrice,ROW_NUMBER()OVER(PARTITION BY SKU ORDER BY AddTime DESC) AS rowid FROM RB_Procurement_Detail_BuyList_MixPrice(NOLOCK) WHERE CustomerID=@CustomerID AND SingleNo IS NOT NULL AND ListNo='') t1 ON t1.SKU = a.SKU AND t1.rowid=1
LEFT JOIN
(
SELECT a.CustomerID,
a.SKU,
b.Single_NO,
b.AddTime SingleTime,
a.ActualProductPrice MixProductPrice,
ROW_NUMBER() OVER (PARTITION BY a.SKU ORDER BY a.ActualProductPrice) AS rowid
FROM RB_Procurement_Detail_BuyList_Detail (NOLOCK) a
LEFT JOIN dbo.RB_Procurement_Detail_BuyList (NOLOCK) d
ON a.ListNo=d.ListNo AND a.CustomerID=d.CustomerID
LEFT JOIN dbo.RB_Procurement_Detail_SingleList (NOLOCK) b
ON b.CustomerID = d.CustomerID
AND b.Single_NO = d.Single_NO
LEFT JOIN dbo.RB_Procurement_List (NOLOCK) c
ON c.CustomerID = a.CustomerID
AND c.P_Code = b.P_Code
WHERE a.CustomerID = @CustomerID
AND c.FinishTime < @nowDate
AND b.Single_NO IS NOT NULL
) t4
ON t4.SKU = a.SKU
AND t4.rowid = 1
WHERE a.CustomerID = @CustomerID
ORDER BY BuyerName;
SELECT
[完结时间],
[下单号],
[下单SKU条码],
[采购单号],
[采购人员],
[采购组别],
[系统SKU],
[自定义SKU],
[产品中文名],
CASE WHEN DATEDIFF(MONTH,[最低采购价时间],GETDATE())>18 AND [最低采购价]<[近一年半最低价] THEN [近一年半最低价下单时间] ELSE [最低采购价时间] END [最低采购价时间],
CASE WHEN DATEDIFF(MONTH,[最低采购价时间],GETDATE())>18 AND [最低采购价]<[近一年半最低价] THEN [近一年半最低价] ELSE [最低采购价] END [最低采购价],
[下单时间],
[入库总数量],
[原下单价],
[平均下单价],
[入库总金额],
((CASE WHEN DATEDIFF(MONTH,[最低采购价时间],GETDATE())>18 AND [最低采购价]<[近一年半最低价] THEN [近一年半最低价] ELSE [最低采购价] END) - [平均下单价])[入库总数量][入库总金额系数] [降价总金额],
[生单价],
[是否首月采购],
[入库总金额系数],
[近一年最低价],
[近一年最低价下单时间],
[近一年半最低价],
[近一年半最低价下单时间],
[最近一次人工过滤价],
[最近一次人工过滤价下单时间],
[历史最低价],
[历史最低价降价总额],
[近一年半降价总额]
INTO #temp2
FROM #temp1
--输出明细表
SELECT
*
FROM #temp2
--输出汇总表
SELECT
采购人员,
采购组别,
SUM(CASE WHEN 降价总金额>0 THEN 降价总金额 ELSE 0 END) AS '降价总金额',
SUM(CASE WHEN 降价总金额<0 THEN 降价总金额 ELSE 0 END) AS '涨价总金额',
SUM(CASE WHEN 是否首月采购='否' THEN 入库总金额 ELSE 0 END) AS '非首次下单总金额',
SUM(CASE WHEN 是否首月采购='是' THEN 入库总金额 ELSE 0 END) AS '是首次下单总金额',
SUM(降价总金额) '降价净值',
SUM(CASE WHEN 是否首月采购='否' THEN 入库总金额*@PriceReductionTarget/100
ELSE 0 END) AS '公司降价目标',
SUM(历史最低价降价总额)'降价净值(历史最低价降价额)',
(SUM(历史最低价降价总额)-SUM((CASE WHEN 是否首月采购='否' THEN 入库总金额*@PriceReductionTarget/100 ELSE 0 END)))@DropCommissionRatio/100 '采购提成(历史最低价)',
SUM(近一年半降价总额)'降价净值(近一年半降价总额)',
(SUM(近一年半降价总额)-SUM((CASE WHEN 是否首月采购='否' THEN 入库总金额@PriceReductionTarget/100 ELSE 0 END)))@DropCommissionRatio/100 '采购提成(近一年半最低价)',
(SUM(降价总金额)-SUM((CASE WHEN 是否首月采购='否' THEN 入库总金额@PriceReductionTarget/100 ELSE 0 END)))*@DropCommissionRatio/100 '采购提成'
FROM #temp2
GROUP BY
采购人员,
采购组别
--CASE WHEN 降价总金额>0 THEN 降价总金额 ELSE 0 END,
--CASE WHEN 降价总金额<0 THEN 降价总金额 ELSE 0 END,
--CASE WHEN 是否首月采购='否' THEN 入库总金额 ELSE 0 END,
--CASE WHEN 是否首月采购='是' THEN 入库总金额 ELSE 0 END,
--(CASE WHEN 是否首月采购='否' THEN 入库总金额 ELSE 0 END)*0.003,
--(历史最低价-平均下单价)入库总数量入库总金额系数,
--(近一年半最低价-平均下单价)入库总数量入库总金额系数,
--(最低采购价-平均下单价)入库总数量入库总金额系数
SELECT '以采购单的手动完结时间（若无手动完结时间则取自动完结时间）作为时间筛选条件' AS 说明
UNION ALL
SELECT '首次采购的订单，最低采购价取当前下单价' AS 说明
UNION ALL
SELECT '非首次采购的订单，最低采购价取本次采购前最近一次的最低采购价格，如果没有勾选采购人员不计入采购提成' AS 说明
UNION ALL
SELECT '下单号作废的订单不统计'
UNION ALL
SELECT '若选择按入库数量统计则为下单数量取实际入库数量' AS 说明
UNION ALL
SELECT '下单价：如果有退税标识，取值税前价' AS 说明
UNION ALL
SELECT '降价净值=降价总额+涨价总额' AS 说明
UNION ALL
SELECT '公司降价目标=非首次下单总金额*公司降价目标系数' AS 说明
UNION ALL
SELECT '降价提成=（降价总额-公司降价目标）降价提成系数' AS 说明
UNION ALL
SELECT '涨价提成=涨价总额涨价提成系数' AS 说明
UNION ALL
SELECT '采购提成=降价提成+涨价提成' AS 说明;
--DROP TABLE #TempPriceRatio
--DROP TABLE #TempProcurementBuyList
--DROP TABLE #TempProcurementBuyList2
--DROP TABLE #temp1
PARAMS:@DropCommissionRatio = 20.00 ,@PriceReductionTarget = 0.30 ,@CustomerId = 1 ,
MESSAGE:Must declare the scalar variable "@nowDate".
Must declare the scalar variable "@nowDate".
Must declare the scalar variable "@nowDate".
Must declare the scalar variable "@nowDate".
Must declare the scalar variable "@nowDate".
Must declare the scalar variable "@nowDate".
Must declare the scalar variable "@nowDate".
Must declare the scalar variable "@nowDate".
Must declare the scalar variable "@nowDate".
Incorrect syntax near 't1'.
Must declare the scalar variable "@nowDate".
TRACE: 在 System.Data.SqlClient.SqlConnection.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)
在 Biz.Irobotbox.ReportCentre.CustomReportService.ExportXlsxByDr(String firstFileFullPath, RB_CustomReportTask customReportTask, Dictionary2 paramList, List1 fields, Int32 timeOut)
在 Biz.Irobotbox.ReportCentre.CustomReportService.ExportXlsxBySteam(RB_CustomReportTask customReportTask, Dictionary2 paramValues, List1 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.b__6_0(Object _)
在 System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()
在 System.Threading.ThreadPoolWorkQueue.Dispatch()
[结果]
[期望]
数据小组开发的报表，让他们处理下