--取时间区间内的采购平均单价,总金额,最后一采购单价,平均采购价格
ALTER PROCEDURE [dbo].[P_Quick_PoPrice]
@StartTime DATETIME,
@EndTime DATETIME
--SET @StartTime = '2022-01-01';
--SET @EndTime = '2023-03-29';
AS
SELECT '苏州' AS Acc,
cVenCode,
(
SELECT cVenName FROM dbo.Vendor WHERE cVenCode = dbo.PO_Pomain.cVenCode
) AS cVenName, --供应商名称
cInvCode, --存货编码
(
SELECT cInvName
FROM dbo.Inventory
WHERE cInvCode = dbo.PO_Podetails.cInvCode
) AS cInvName, --存货名称
(
SELECT cInvStd
FROM dbo.Inventory
WHERE cInvCode = dbo.PO_Podetails.cInvCode
) AS cInvStd, --存货规格
SUM(ISNULL(iNatSum, 0)) AS TotalSum, --采购总金额
SUM(iQuantity) AS iQuantity, --采购总数量
SUM(iNatSum) / NULLIF(SUM(iQuantity), 0) AS AgvPrice, --平均采购价格
(
SELECT TOP 1
PoS.iNatSum / NULLIF(PoS.iQuantity, 0)
FROM dbo.PO_Pomain PoM
JOIN dbo.PO_Podetails PoS
ON PoM.POID = PoS.POID
WHERE dPODate
BETWEEN @StartTime AND DATEADD(DAY, 1, @EndTime)
AND PoM.cVenCode = PO_Pomain.cVenCode
AND PoS.cInvCode = PO_Podetails.cInvCode
AND PoM.cVerifier IS NOT NULL
ORDER BY dPODate DESC
) AS LastPrice --最后一次采购价格
FROM dbo.PO_Pomain
JOIN dbo.PO_Podetails
ON PO_Podetails.POID = PO_Pomain.POID
AND cVerifier IS NOT NULL
WHERE dPODate
BETWEEN @StartTime AND DATEADD(DAY, 1, @EndTime)
GROUP BY cVenCode,
cInvCode
UNION ALL
SELECT '南通' AS Acc,
cVenCode,
(
SELECT cVenName
FROM UFDATA_004_2020.dbo.Vendor
WHERE cVenCode = PO_Pomain.cVenCode
) AS cVenName, --供应商名称
cInvCode, --存货编码
(
SELECT cInvName
FROM UFDATA_004_2020.dbo.Inventory
WHERE cInvCode = PO_Podetails.cInvCode
) AS cInvName, --存货名称
(
SELECT cInvStd
FROM UFDATA_004_2020.dbo.Inventory
WHERE cInvCode = PO_Podetails.cInvCode
) AS cInvStd, --存货规格
SUM(ISNULL(iNatSum, 0)) AS TotalSum, --采购总金额
SUM(iQuantity) AS iQuantity, --采购总数量
SUM(iNatSum) / NULLIF(SUM(iQuantity), 0) AS AgvPrice, --平均采购价格
(
SELECT TOP 1
PoS.iNatSum / NULLIF(PoS.iQuantity, 0)
FROM UFDATA_004_2020.dbo.PO_Pomain PoM
JOIN UFDATA_004_2020.dbo.PO_Podetails PoS
ON PoM.POID = PoS.POID
WHERE dPODate
BETWEEN @StartTime AND DATEADD(DAY, 1, @EndTime)
AND PoM.cVenCode = PO_Pomain.cVenCode
AND PoS.cInvCode = PO_Podetails.cInvCode
AND PoM.cVerifier IS NOT NULL
ORDER BY dPODate DESC
) AS LastPrice --最后一次采购价格
FROM UFDATA_004_2020.dbo.PO_Pomain PO_Pomain
JOIN UFDATA_004_2020.dbo.PO_Podetails PO_Podetails
ON PO_Podetails.POID = PO_Pomain.POID
AND cVerifier IS NOT NULL
WHERE dPODate
BETWEEN @StartTime AND DATEADD(DAY, 1, @EndTime)
GROUP BY cVenCode,
cInvCode;
用友U8取时间区间内的采购平均单价,总金额,最后一采购单价
于 2023-03-31 11:21:38 首次发布