[.NET] ต้องการ ลบค่า2ค่า ใน query ทำไงครับ คือจะหาค่า คงคลังที่เหลือ = คงคลัง-จอง

มีเพื่อนล่ะ ผมก็ไม่รู้จะช่วยยังไงเหมือนกัน

SELECT DISTINCT
Stocks.PRODUCT, SUM(ISNULL(CASE MarkingDetail.StatusEditRequis WHEN 'False' THEN MarkingDetail.Amount_Requis END, 0)) AS Requis,
Stocks.GradName, Stocks.WeightSize, Stocks.Color, Stocks.UNIT, Stocks.Remark,
SUM(Stocks.Amount + - ISNULL(CASE MarkingDetail.StatusEditRequis WHEN 'False' THEN MarkingDetail.Amount_Requis END, 0))
AS StockAmount_Requis, Stocks.Amount, SUM(ISNULL(MarkingDetail.Amount_Requis, 0)) AS Amount_Requis, Stocks.MATW, Stocks.MATL,
Stocks.MATT, Stocks.Strong
FROM Stocks LEFT OUTER JOIN
MarkingDetail ON Stocks.PRODUCT = MarkingDetail.Product
GROUP BY Stocks.CategoryName, Stocks.PRODUCT, Stocks.GradName, Stocks.WeightSize, Stocks.Color, Stocks.UNIT, Stocks.Remark, Stocks.Amount,
Stocks.MATW, Stocks.MATL, Stocks.MATT, Stocks.Strong
ORDER BY Stocks.PRODUCT

ได้แล้วครับ

Code (VB.NET)

view source

print?

01.SELECT     Stocks.PRODUCT, SUM(ISNULL(CASE MarkingDetail.StatusEditRequis WHEN 'False' THEN MarkingDetail.Amount_Requis END, 0)) AS Total, 'รวมจองทุกเลขที่ผลิต ยกเว้นเลขที่5961 จะเขียนยังไง

02.                      SUM(ISNULL(MarkingDetail.Amount_Requis, 0)) AS Amount_Requis, 'รวมจองของเลขที่5961

03.                      SUM(Stocks.Amount + - ISNULL(CASE MarkingDetail.StatusEditRequis WHEN 'False' THEN MarkingDetail.Amount_Requis END, 0)) AS Rest, 'คลัง-จอง ค่าตรงนี้จริงๆจะเอาค่า คลัง-รวมจองทุกเลขที่ผลิต ยกเว้นเลขที่5961

04.                      Stocks.Amount AS Stocks, Production.ProductionID, Production.CustomerID, Stocks.GradName, Stocks.WeightSize, Stocks.Color AS StocksColor,

05.                      Stocks.UNIT AS StocksUNIT, Stocks.Remark, Stocks.MATW, Stocks.MATL, Stocks.MATT, Stocks.Strong, GenerateFG.Fullname, GenerateFG.Model,

06.                      GenerateFG.SizeW, GenerateFG.SizeL, GenerateFG.SizeH, GenerateFG.SizeT, GenerateFG.Color, GenerateFG.Charector, GenerateFG.Dwgno,

07.                      GenerateFG.Amount AS PDAmount, GenerateFG.Unit, GenerateFG.Charector_Format, Production.DateDelivery, Production.GenerateID,

08.                      Marking.Marking_Date, Production.DatePD

09.FROM         Stocks INNER JOIN

10.                      MarkingDetail ON Stocks.PRODUCT = MarkingDetail.Product INNER JOIN

11.                      Marking ON Marking.ProductNo = MarkingDetail.ProductNo INNER JOIN

12.                      Production ON Marking.ProductNo = Production.ProductionID INNER JOIN

13.                      GenerateFG ON Marking.GenerateID = GenerateFG.GenerateID

14.WHERE     (MarkingDetail.StatusEditRequis = 'False') AND (MarkingDetail.ProductNo = '5961')'เลขที่ผลิตที่เลือก

15.GROUP BY Stocks.PRODUCT, Stocks.CategoryName, Stocks.GradName, Stocks.WeightSize, Stocks.Color, Stocks.UNIT, Stocks.Remark, Stocks.Amount,

16.                      Production.ProductionID, Production.CustomerID, Stocks.MATW, Stocks.MATL, Stocks.MATT, Stocks.Strong, GenerateFG.Fullname, GenerateFG.Model,

17.                      GenerateFG.SizeW, GenerateFG.SizeL, GenerateFG.SizeH, GenerateFG.SizeT, GenerateFG.Color, GenerateFG.Charector, GenerateFG.Dwgno,

18.                      GenerateFG.Amount, GenerateFG.Unit, GenerateFG.Charector_Format, GenerateFG.Charector_Format, Production.DateDelivery, Production.GenerateID,

19.                      Marking.Marking_Date, Production.DatePD

20.ORDER BY Stocks.PRODUCT