on 07-03-2014 8:36 PM
Buenas tardes para solicitar de su apoyo para un query que me muestre la venta por grupo de artículos concentrado en cantidad y costo por mes y por año para hacer un comparativo de antemano gracias y saludos
Hola Felix.
Podrias colocar en la primera linea del query que seleccione el itemname junto con el itemcode y si quieres ver el año, me parece entender que lo que quieres es que te muestre el periodo, en ese caso agregale a que tome el campo code de la tabla OFPR en una variable segun lo que necesites y para esto ponlo todo dentro de un Stored Procedure.
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Felix intenta con este:
SELECT T0.ITEMCODE,
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'ENE Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'FEB Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'MAR Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'ABR Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'MAY Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'JUN Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'JUL Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'AGO Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'SEP Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'OCT Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'NOV Amt',
(SELECT SUM(T1.QUANTITY*T1.PRICE) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE
= T0.ITEMCODE) AS 'DIC Amt'
FROM dbo.OITM T0
LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode
WHERE T0.SellItem = 'Y'
GROUP BY T0.ItemCode,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())
ORDER BY T0. ITEMCODE
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
104 | |
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.