--年度销售平均单价,销量排名,最高售价及数量,最低售价及数量
ALTER VIEW V_Quick_So
AS
SELECT ROW_NUMBER() OVER (PARTITION BY So.iYear ORDER BY So.iQuantity DESC) AS id,
So.iYear,
So.cInvCode,
So.iQuantity,
So.iSum,
So.avgPrice,
SoH.iQuantity AS HiQuantity,
SoH.iTaxUnitPrice AS HiTaxUnitPrice,
SoL.iQuantity AS LiQuantity,
SoL.iTaxUnitPrice AS LiTaxUnitPrice
FROM
(
SELECT So.cInvCode,
So.iYear,
SUM(So.iQuantity) AS iQuantity,
SUM(So.iSum) AS iSum,
SUM(So.iSum) / SUM(So.iQuantity) AS avgPrice
FROM
(
SELECT YEAR(dDate) AS iYear, --年度
cInvCode, --存货编码
iQuantity, --数量
iTaxUnitPrice, --原币含税单价
ISNULL(iSum, 0) AS iSum --原币价税合计
FROM dbo.SO_SOMain
JOIN dbo.SO_SODetails
ON SO_SODetails.ID = SO_SOMain.ID
WHERE SO_SOMain.cSTCode IN ( 'SC', 'SM', 'SW' )
) So
GROUP BY So.cInvCode,
So.iYear
) So
JOIN
(
SELECT So.iYear,
So.cInvCode,
So.iQuantity,
So.iTaxUnitPrice
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY cInvCode, YEAR(dDate) ORDER BY iTaxUnitPrice DESC) AS id,
YEAR(dDate) AS iYear, --年度
cInvCode, --存货编码
iQuantity, --数量
iTaxUnitPrice --原币含税单价
FROM dbo.SO_SOMain
JOIN dbo.SO_SODetails
ON SO_SODetails.ID = SO_SOMain.ID
AND SO_SOMain.cSTCode IN ( 'SC', 'SM', 'SW' )
) So
WHERE So.id = 1
) SoH
ON SoH.cInvCode = So.cInvCode
AND SoH.iYear = So.iYear
JOIN
(
SELECT So.iYear,
So.cInvCode,
So.iQuantity,
So.iTaxUnitPrice
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY cInvCode, YEAR(dDate) ORDER BY iTaxUnitPrice ASC) AS id,
YEAR(dDate) AS iYear, --年度
cInvCode, --存货编码
iQuantity, --数量
iTaxUnitPrice --原币含税单价
FROM dbo.SO_SOMain
JOIN dbo.SO_SODetails
ON SO_SODetails.ID = SO_SOMain.ID
AND SO_SOMain.cSTCode IN ( 'SC', 'SM', 'SW' )
) So
WHERE So.id = 1
) SoL
ON SoL.cInvCode = So.cInvCode
AND SoL.iYear = So.iYear