SELECT WareInfo.WareId WareId,aa2.WareRe WareRe,aa2.Remains Remains, aa2.Price Price, aa2.CashSum CashSum,
aa2.Qty Qty, SaleMoney SaleMoney
INTO aa3 FROM
(SELECT (CASE WHEN aa1.WareNo IS NOT NULL THEN aa1.WareNo ELSE kffh_t.WareNo END) AS WareNo,ISNULL(aa1.Qty,0) Qty,ISNULL(aa1.SaleMoney,0) SaleMoney,
ISNULL(aa1.WareRemains,0) WareRe,ISNULL(kffh_t.WareRemains,0) Remains,
ISNULL(kffh_t.WarePrice,0) Price,ISNULL(kffh_t.CashSum,0) CashSum
FROM
(SELECT ass.ChuteNo,ISNULL(ass.SaleQty,0) Qty,
ISNULL(ass.SaleMoney,0) SaleMoney,
ISNULL(ChuteInfo.wareRemains,0) WareRemains,
ISNULL(ChuteInfo.WareNo,0) WareNo
FROM
(SELECT SUM(SaleQty) AS SaleQty,SUM(ISNULL(SaleMoney,0)) AS SaleMoney,ChuteNo
FROM SaleRec WHERE LEFT(SaleDate,8) BETWEEN 20030401 AND 20030601 GROUP BY ChuteNo)
ass FULL join ChuteInfo
ON ass.ChuteNo=ChuteInfo.ChuteNo)
aa1 FULL JOIN kffh_t
ON aa1.WareNo=kffh_t.WareNo)
aa2 INNER JOIN WareInfo ON aa2.WareNo=WareInfo.WareNo
SELECT WareId AS 商品名称,(WareRe+Qty-Remains) AS 期初数量,(WareRe+Qty-Remains)*Price AS 期初金额,Remains AS 加货数量,Remains*Price AS 加货金额,Qty AS 销售数量,SaleMoney AS 销售金额,
WareRe AS 库存数量,WareRe*Price AS 库存金额
FROM aa3 ORDER BY WareId
aa2.Qty Qty, SaleMoney SaleMoney
INTO aa3 FROM
(SELECT (CASE WHEN aa1.WareNo IS NOT NULL THEN aa1.WareNo ELSE kffh_t.WareNo END) AS WareNo,ISNULL(aa1.Qty,0) Qty,ISNULL(aa1.SaleMoney,0) SaleMoney,
ISNULL(aa1.WareRemains,0) WareRe,ISNULL(kffh_t.WareRemains,0) Remains,
ISNULL(kffh_t.WarePrice,0) Price,ISNULL(kffh_t.CashSum,0) CashSum
FROM
(SELECT ass.ChuteNo,ISNULL(ass.SaleQty,0) Qty,
ISNULL(ass.SaleMoney,0) SaleMoney,
ISNULL(ChuteInfo.wareRemains,0) WareRemains,
ISNULL(ChuteInfo.WareNo,0) WareNo
FROM
(SELECT SUM(SaleQty) AS SaleQty,SUM(ISNULL(SaleMoney,0)) AS SaleMoney,ChuteNo
FROM SaleRec WHERE LEFT(SaleDate,8) BETWEEN 20030401 AND 20030601 GROUP BY ChuteNo)
ass FULL join ChuteInfo
ON ass.ChuteNo=ChuteInfo.ChuteNo)
aa1 FULL JOIN kffh_t
ON aa1.WareNo=kffh_t.WareNo)
aa2 INNER JOIN WareInfo ON aa2.WareNo=WareInfo.WareNo
SELECT WareId AS 商品名称,(WareRe+Qty-Remains) AS 期初数量,(WareRe+Qty-Remains)*Price AS 期初金额,Remains AS 加货数量,Remains*Price AS 加货金额,Qty AS 销售数量,SaleMoney AS 销售金额,
WareRe AS 库存数量,WareRe*Price AS 库存金额
FROM aa3 ORDER BY WareId
WareRe AS 库存数量,WareRe*Price AS 库存金额
FROM
(
SELECT WareInfo.WareId WareId,aa2.WareRe WareRe,aa2.Remains Remains, aa2.Price Price, aa2.CashSum CashSum,
aa2.Qty Qty, SaleMoney SaleMoney
FROM
(SELECT (CASE WHEN aa1.WareNo IS NOT NULL THEN aa1.WareNo ELSE kffh_t.WareNo END) AS WareNo,ISNULL(aa1.Qty,0) Qty,ISNULL(aa1.SaleMoney,0) SaleMoney,
ISNULL(aa1.WareRemains,0) WareRe,ISNULL(kffh_t.WareRemains,0) Remains,
ISNULL(kffh_t.WarePrice,0) Price,ISNULL(kffh_t.CashSum,0) CashSum
FROM
(SELECT ass.ChuteNo,ISNULL(ass.SaleQty,0) Qty,
ISNULL(ass.SaleMoney,0) SaleMoney,
ISNULL(ChuteInfo.wareRemains,0) WareRemains,
ISNULL(ChuteInfo.WareNo,0) WareNo
FROM
(SELECT SUM(SaleQty) AS SaleQty,SUM(ISNULL(SaleMoney,0)) AS SaleMoney,ChuteNo
FROM SaleRec WHERE LEFT(SaleDate,8) BETWEEN 20030401 AND 20030601 GROUP BY ChuteNo)
ass FULL join ChuteInfo
ON ass.ChuteNo=ChuteInfo.ChuteNo)
aa1 FULL JOIN kffh_t
ON aa1.WareNo=kffh_t.WareNo)
aa2 INNER JOIN WareInfo ON aa2.WareNo=WareInfo.WareNo)aa3 ORDER BY WareId