 |
|
ต้องการ ลบค่า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,
(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
Tag : .NET, VB.NET
|
|
 |
 |
 |
 |
Date :
2010-12-02 16:06:33 |
By :
chin |
View :
1091 |
Reply :
4 |
|
 |
 |
 |
 |
|
|
|
 |
 |
|
 |
 |
 |
|
|
 |
 |
|
แล้วปัญหาตอนนี้ติดตรงไหนครับ 
|
 |
 |
 |
 |
Date :
2010-12-02 19:58:40 |
By :
webmaster |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
 |
|
|
 |
 |
|
ตอบความคิดเห็นที่ : 2 เขียนโดย : หางอึ่ง เมื่อวันที่ 2010-12-03 10:09:18
รายละเอียดของการตอบ ::
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
ได้แล้วครับ
|
 |
 |
 |
 |
Date :
2010-12-03 11:47:02 |
By :
chin |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
 |
|
|
 |
 |
|
ตอบความคิดเห็นที่ : 1 เขียนโดย : webmaster เมื่อวันที่ 2010-12-02 19:58:40
รายละเอียดของการตอบ ::
Code (VB.NET)
SELECT Stocks.PRODUCT, SUM(ISNULL(CASE MarkingDetail.StatusEditRequis WHEN 'False' THEN MarkingDetail.Amount_Requis END, 0)) AS Total, 'รวมจองทุกเลขที่ผลิต ยกเว้นเลขที่5961 จะเขียนยังไง
SUM(ISNULL(MarkingDetail.Amount_Requis, 0)) AS Amount_Requis, 'รวมจองของเลขที่5961
SUM(Stocks.Amount + - ISNULL(CASE MarkingDetail.StatusEditRequis WHEN 'False' THEN MarkingDetail.Amount_Requis END, 0)) AS Rest, 'คลัง-จอง ค่าตรงนี้จริงๆจะเอาค่า คลัง-รวมจองทุกเลขที่ผลิต ยกเว้นเลขที่5961
Stocks.Amount AS Stocks, Production.ProductionID, Production.CustomerID, Stocks.GradName, Stocks.WeightSize, Stocks.Color AS StocksColor,
Stocks.UNIT AS StocksUNIT, Stocks.Remark, Stocks.MATW, Stocks.MATL, Stocks.MATT, Stocks.Strong, GenerateFG.Fullname, GenerateFG.Model,
GenerateFG.SizeW, GenerateFG.SizeL, GenerateFG.SizeH, GenerateFG.SizeT, GenerateFG.Color, GenerateFG.Charector, GenerateFG.Dwgno,
GenerateFG.Amount AS PDAmount, GenerateFG.Unit, GenerateFG.Charector_Format, Production.DateDelivery, Production.GenerateID,
Marking.Marking_Date, Production.DatePD
FROM Stocks INNER JOIN
MarkingDetail ON Stocks.PRODUCT = MarkingDetail.Product INNER JOIN
Marking ON Marking.ProductNo = MarkingDetail.ProductNo INNER JOIN
Production ON Marking.ProductNo = Production.ProductionID INNER JOIN
GenerateFG ON Marking.GenerateID = GenerateFG.GenerateID
WHERE (MarkingDetail.StatusEditRequis = 'False') AND (MarkingDetail.ProductNo = '5961')'เลขที่ผลิตที่เลือก
GROUP BY Stocks.PRODUCT, Stocks.CategoryName, Stocks.GradName, Stocks.WeightSize, Stocks.Color, Stocks.UNIT, Stocks.Remark, Stocks.Amount,
Production.ProductionID, Production.CustomerID, Stocks.MATW, Stocks.MATL, Stocks.MATT, Stocks.Strong, GenerateFG.Fullname, GenerateFG.Model,
GenerateFG.SizeW, GenerateFG.SizeL, GenerateFG.SizeH, GenerateFG.SizeT, GenerateFG.Color, GenerateFG.Charector, GenerateFG.Dwgno,
GenerateFG.Amount, GenerateFG.Unit, GenerateFG.Charector_Format, GenerateFG.Charector_Format, Production.DateDelivery, Production.GenerateID,
Marking.Marking_Date, Production.DatePD
ORDER BY Stocks.PRODUCT
|
 |
 |
 |
 |
Date :
2010-12-03 12:08:45 |
By :
chin |
|
 |
 |
 |
 |
|
|
 |
 |
|
 |
 |
|
|