SQL using same field twice with different criteria

[Former Member]

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)