--获取物料当月采购的最高价与最低价
CREATE VIEW V_Quick_Price
AS
SELECT MaxT.iYear AS iYear,
MaxT.iPeriod AS iPeriod,
MaxT.cInvCode,
MaxT.iTaxPrice AS MaxPrice,
MaxT.iQuantity AS MaxQuantity,
MinT.iTaxPrice AS MinPrice,
MinT.iQuantity AS MinQuantity
FROM
(
--最高价
SELECT A.iYear,
A.iPeriod,
A.cInvCode,
A.iQuantity,
A.iTaxPrice,
A.iSum
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PO_Podetails.cInvCode,
YEAR(dPODate),
MONTH(dPODate)
ORDER BY iTaxPrice DESC
) AS id,
YEAR(dPODate) AS iYear,
MONTH(dPODate) AS iPeriod,
cInvCode,
iQuantity,
iTaxPrice,
iSum
FROM dbo.PO_Pomain
JOIN dbo.PO_Podetails
ON PO_Podetails.POID = PO_Pomain.POID
AND iSum IS NOT NULL
) A
WHERE A.id = 1
) MaxT
JOIN
(
--最低价
SELECT A.iYear,
A.iPeriod,
A.cInvCode,
A.iQuantity,
A.iTaxPrice,
A.iSum
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PO_Podetails.cInvCode,
YEAR(dPODate),
MONTH(dPODate)
ORDER BY iTaxPrice ASC
) AS id,
YEAR(dPODate) AS iYear,
MONTH(dPODate) AS iPeriod,
cInvCode,
iQuantity,
iTaxPrice,
iSum
FROM dbo.PO_Pomain
JOIN dbo.PO_Podetails
ON PO_Podetails.POID = PO_Pomain.POID
AND iSum IS NOT NULL
) A
WHERE A.id = 1
) MinT
ON MinT.cInvCode = MaxT.cInvCode
AND MinT.iPeriod = MaxT.iPeriod
AND MinT.iYear = MaxT.iYear;
--月平均单价
SELECT Temp.cInvCode,
Temp.iQuantity,
Temp.iSum,
Temp.iSum / Temp.iQuantity AS price,
Temp.iYear,
Temp.iPeriod
FROM
(
SELECT cInvCode,
SUM(ISNULL(A.iQuantity, 0)) AS iQuantity,
SUM(ISNULL(A.iSum, 0)) AS iSum,
A.iYear,
A.iPeriod
FROM
(
SELECT YEAR(dPODate) AS iYear,
MONTH(dPODate) AS iPeriod,
cInvCode,
iQuantity,
iSum
FROM dbo.PO_Pomain
JOIN dbo.PO_Podetails
ON PO_Podetails.POID = PO_Pomain.POID
AND iSum IS NOT NULL
) A
GROUP BY A.cInvCode,
A.iYear,
A.iPeriod
) Temp