on 12-13-2010 8:57 PM
BUEN DIA A TODOS TENGO EL SIGUIENTE QRY:
SELECT T0.Dscription,
CASE
WHEN T0.Dscription LIKE '%EPIN%' THEN SUM(T0.Quantity)
ELSE 0
END 'SALDO',
CASE
WHEN T0.Dscription LIKE '%$1.50%' THEN SUM(T0.Quantity)
ELSE 0
END,
CASE
WHEN T0.Dscription LIKE '%$3.00%' THEN SUM(T0.Quantity)
ELSE 0
END ,
CASE
WHEN T0.Dscription LIKE '%$5.00%' THEN SUM(T0.Quantity)
ELSE 0
END ,
CASE
WHEN T0.Dscription LIKE '%SIM%' THEN SUM(T0.Quantity)
ELSE 0
END
FROM INV1 T0
LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.DocDate = '[%0]' AND
T1.SlpName = '[%1]'
GROUP BY Dscription
EL RESULTADO ES ASI:
*_Dscription_* *_Dscription1_* *_Dscription2_* *_Dscription3_* *_Dscription4_* *_Dscription5_*
ItemCode1 25,000 000.00 000.00 000.00 000.00
ItemCode2 000.00 10,000 000.00 000.00 000.00
ItemCode3 000.00 000.00 5,000 000.00 000.00
ItemCode4 000.00 000.00 000.00 500 000.00
ItemCode5 000.00 000.00 000.00 000.00 000.00
EL RESULTADO LO NECESITO EN UNA SOLA COLUMNA TODOS LOS DATOS EN DSCRIPTION1
ALGUIEN PUEDE RECOMENDARME ALGO PENSE EN LA SENTENCIA PIVOT PERO HE VISTO QUE EL resultADO DE TODAS LAS TABLAS PIVOT ES IGUAL AL QUE TENGO SERA QUE UNPIVOT RESULTA?
GRACIAS!!!!!!!!!
Buenos Dias
Trata con la siguiente query;
SELECT T0.Dscription,
CASE
WHEN T0.Dscription LIKE '%EPIN%' THEN (SELECT SUM(T2.Quantity) FROM dbo.INV1 T2 WHERE T2.Dscription LIKE '%EPIN%' AND T2.DocEntry = T0.DocEntry)
WHEN T0.Dscription LIKE '%$1.50%' THEN (SELECT SUM(T2.Quantity) FROM dbo.INV1 T2 WHERE T2.Dscription LIKE '%$1.50%' AND T2.DocEntry = T0.DocEntry)
WHEN T0.Dscription LIKE '%$3.00%' THEN (SELECT SUM(T2.Quantity) FROM dbo.INV1 T2 WHERE T2.Dscription LIKE '%$3.00%' AND T2.DocEntry = T0.DocEntry)
WHEN T0.Dscription LIKE '%$5.00%' THEN (SELECT SUM(T2.Quantity) FROM dbo.INV1 T2 WHERE T2.Dscription LIKE '%$5.00%' AND T2.DocEntry = T0.DocEntry)
WHEN T0.Dscription LIKE '%SIM%' THEN (SELECT SUM(T2.Quantity) FROM dbo.INV1 T2 WHERE T2.Dscription LIKE '%SIM%' AND T2.DocEntry = T0.DocEntry)
ELSE '0'
END 'SALDO'
FROM INV1 T0
LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.DocDate = '[%0]' AND
T1.SlpName = '[%1]'
GROUP BY Dscription, DocEntry
Ojala te funcione, saludos
FLR
Edited by: Floyola on Dec 14, 2010 10:06 AM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Porque no usas el Union???
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
100 | |
12 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.