SQL using same field twice with different criteria

Former Member — Fri, 21 Apr 2017 14:19:15 GMT

I need quantity to have 3 columns:

*Quantity

*Free Quantity - Where Linetotal = 0.00

*Paid Quantity - Where Linetotal > 0.00

Please see report below:

DECLARE @BL Table

(FrgnName CHAR(100), Quantity DECIMAL(18,2), FreeQty DECIMAL(18,2), PaidQty DECIMAL(18,2), LineTotal Money)

INSERT INTO @BL (FrgnName, Quantity, FreeQty, PaidQty, Linetotal)

SELECT T3.FrgnName AS 'FrgnName', SUM(T1.Quantity) AS 'Quantity',
(SELECT SUM(T1.Quantity) FROM INV1 T1 INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OINV T0 ON T1.DocEntry = T0.DocEntry WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T0.DocType = 'I' AND T3.FrgnName = FrgnName and T3.ItmsGrpCod IN (107, 108, 110, 111, 112, 114, 115, 116, 117, 127, 129, 131, 132) AND T1.LineTotal = '0.00') AS 'FreeQty',
(SELECT SUM(T1.Quantity) FROM INV1 T1 INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OINV T0 ON T1.DocEntry = T0.DocEntry WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T0.DocType = 'I' AND T3.FrgnName = FrgnName and T3.ItmsGrpCod IN (107, 108, 110, 111, 112, 114, 115, 116, 117, 127, 129, 131, 132) AND T1.LineTotal <> '0.00') AS 'PaidQty',
SUM(NULLIF(T1.Linetotal - (T0.DiscPrcnt * (T1.LineTotal)/100),0)) AS 'LineTotal'

FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN OCRD T2 ON T0.Cardcode = T2.CardCode
INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T0.DocType = 'I' and T3.ItmsGrpCod IN (107, 108, 110, 111, 112, 114, 115, 116, 117, 127, 129, 131, 132)

GROUP BY T3.FrgnName ORDER BY 1

INSERT INTO @BL (FrgnName, Quantity, FreeQty, PaidQty, Linetotal)

SELECT T3.FrgnName AS 'FrgnName', - SUM(T1.Quantity) AS 'Quantity',
- (SELECT SUM(T1.Quantity) FROM RIN1 T1 INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OINV T0 ON T1.DocEntry = T0.DocEntry WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T0.DocType = 'I' AND T3.FrgnName = FrgnName and T3.ItmsGrpCod IN (107, 108, 110, 111, 112, 114, 115, 116, 117, 127, 129, 131, 132) AND T1.LineTotal = '0.00') AS 'FreeQty',
- (SELECT SUM(T1.Quantity) FROM RIN1 T1 INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OINV T0 ON T1.DocEntry = T0.DocEntry WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T0.DocType = 'I' AND T3.FrgnName = FrgnName and T3.ItmsGrpCod IN (107, 108, 110, 111, 112, 114, 115, 116, 117, 127, 129, 131, 132) AND T1.LineTotal <> '0.00') AS 'PaidQty',
- SUM(NULLIF(T1.Linetotal - (T0.DiscPrcnt * (T1.LineTotal)/100),0)) AS 'LineTotal'
FROM ORIN T0 INNER JOIN RIN1 T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN OCRD T2 ON T0.Cardcode = T2.CardCode
INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
WHERE T0.DocDate BETWEEN [%0] AND [%1] AND T0.DocType = 'I' and T3.ItmsGrpCod IN (107, 108, 110, 111, 112, 114, 115, 116, 117, 127, 129, 131, 132)
GROUP BY T3.FrgnName
ORDER BY 1

SELECT FrgnName, SUM(Quantity) AS 'Quantity', SUM(FreeQty) AS 'Free Qty', SUM(PaidQty) AS 'Paid Qty', SUM(Linetotal) AS 'Amount'
FROM @BL T0
GROUP BY FrgnName ORDER BY 1

**FrgnName** is the Model Name in this instance (Shorter version of the ItemName)

topic SQL using same field twice with different criteria in Application Development and Automation Discussions

SQL using same field twice with different criteria