on 05-28-2015 4:17 AM
Saludos estimado Joseph Pérez , en el query que posteaste de analisis de venta e lo que estaba buscando yo no se mucho de Sql, por lo que te pido que me ayuden gracias de antemano, el tema es que quisiera a esta consulta aumentarle el campo del costo del producto, y las listas de precio en mi caso usamos dos listas una lista precio base y lista presentacion y el promeio de precio que sale de la cantidad y las ventas. en esta consulta la verdad no se como hacerlo ya que soy novato. ojala puedan ayudarme
pongo tu consulta ya que quiero adicionar los campos que menciono.
este es el que uso para articulos
DECLARE @FechaInicial DATETIME
DECLARE @FechaFinal DATETIME
SET @FechaInicial=('20140901')
SET @FechaFinal=('20140930')
SELECT
ob.itemcode,
A.ItemName dscription,
SUM(ob.unidades) As unidades
,ROUND(SUM(ob.total),2) as 'Total'
--,(SUM(ob.total)/Sum(ob.unidades)) precio
FROM (
SELECT c.itemcode, SUM(c.unidades) unidades, SUM(c.total) total FROM(
SELECT DISTINCT B.itemcode, SUM(B.unidades) unidades, SUM(total2) total FROM(
SELECT * FROM (
SELECT distinct
T0.DocEntry,
t0.DocNum,
T1.LineNum,
T1.ItemCode as 'itemcode',
T1.InvQty 'unidades',
--T1.Linetotal 'total',
--((t0.DiscPrcnt / 100)) porcentaje,
(T1.Linetotal-(((T0.DiscPrcnt / 100)) * T1.Linetotal)) total2
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.DocDate between @FechaInicial AND @FechaFinal
AND T0.CANCELED ='N'
GROUP BY
T0.DocEntry,
T0.DocNum,
T1.LineNum,
T1.ItemCode,
T2.NumInCnt,
T1.InvQty,
T1.LineTotal,
T0.DiscPrcnt
)A
)B
GROUP BY B.itemcode
UNION ALL
SELECT DISTINCT B.itemcode, SUM(B.unidades) unidades, SUM(total2) total FROM(
SELECT * FROM (
SELECT distinct
T0.DocEntry,
t0.DocNum,
T1.LineNum,
T1.ItemCode as 'itemcode',
CASE
WHEN T0.CANCELED IN ('N','Y') THEN -T1.InvQty
ELSE T1.InvQty
END 'unidades',
CASE
WHEN T0.CANCELED IN ('N','Y') THEN -(T1.Linetotal-(((t0.DiscPrcnt / 100)) * T1.Linetotal))
ELSE (T1.Linetotal-(((t0.DiscPrcnt / 100)) * T1.Linetotal))
END total2
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.DocDate BETWEEN @FechaInicial AND @FechaFinal
GROUP BY
T0.DocEntry,
T0.DocNum,
T0.CANCELED,
T1.LineNum,
T1.ItemCode,
T2.NumInCnt,
T1.InvQty,
T1.LineTotal,
T0.DiscPrcnt
)A
)B
GROUP BY B.itemcode
)C
GROUP BY c.itemcode
----------------------------------------------------------------------------------------------
) as Ob
INNER JOIN OITM A ON A.ItemCode = ob.itemcode
GROUP BY ob.itemcode, A.numincnt, A.ItemName
ORDER BY itemcode
ejemplo asi deberia verse solo falta el costo del producto es decir lo que le cuesta ya puesto en la empresa depues de ingreso a bodegas
Prueba con este query, le agrege el precio promedio "AvgPrice" y los precios de lista 1 y 2, no se cual necesitas solo cambia el numero
DECLARE @FechaInicial DATETIME
DECLARE @FechaFinal DATETIME
SET @FechaInicial=('20140901')
SET @FechaFinal=('20140930')
SELECT
ob.itemcode,
A.ItemName dscription,
SUM(ob.unidades) As unidades
,ROUND(SUM(ob.total),2) as 'Total',
--,(SUM(ob.total)/Sum(ob.unidades)) precio
A.AvgPrice PrecioPromedio,
B.Price 'Precio de Lista1',
C.Price 'Precio de Lista2'
FROM (
SELECT c.itemcode, SUM(c.unidades) unidades, SUM(c.total) total FROM(
SELECT DISTINCT B.itemcode, SUM(B.unidades) unidades, SUM(total2) total FROM(
SELECT * FROM (
SELECT distinct
T0.DocEntry,
t0.DocNum,
T1.LineNum,
T1.ItemCode as 'itemcode',
T1.InvQty 'unidades',
--T1.Linetotal 'total',
--((t0.DiscPrcnt / 100)) porcentaje,
(T1.Linetotal-(((T0.DiscPrcnt / 100)) * T1.Linetotal)) total2
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.DocDate between @FechaInicial AND @FechaFinal
AND T0.CANCELED ='N'
GROUP BY
T0.DocEntry,
T0.DocNum,
T1.LineNum,
T1.ItemCode,
T2.NumInCnt,
T1.InvQty,
T1.LineTotal,
T0.DiscPrcnt
)A
)B
GROUP BY B.itemcode
UNION ALL
SELECT DISTINCT B.itemcode, SUM(B.unidades) unidades, SUM(total2) total FROM(
SELECT * FROM (
SELECT distinct
T0.DocEntry,
t0.DocNum,
T1.LineNum,
T1.ItemCode as 'itemcode',
CASE
WHEN T0.CANCELED IN ('N','Y') THEN -T1.InvQty
ELSE T1.InvQty
END 'unidades',
CASE
WHEN T0.CANCELED IN ('N','Y') THEN -(T1.Linetotal-(((t0.DiscPrcnt / 100)) * T1.Linetotal))
ELSE (T1.Linetotal-(((t0.DiscPrcnt / 100)) * T1.Linetotal))
END total2
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.DocDate BETWEEN @FechaInicial AND @FechaFinal
GROUP BY
T0.DocEntry,
T0.DocNum,
T0.CANCELED,
T1.LineNum,
T1.ItemCode,
T2.NumInCnt,
T1.InvQty,
T1.LineTotal,
T0.DiscPrcnt
)A
)B
GROUP BY B.itemcode
)C
GROUP BY c.itemcode
----------------------------------------------------------------------------------------------
) as Ob
INNER JOIN OITM A ON A.ItemCode = ob.itemcode
LEFT JOIN ITM1 B ON B.ItemCode = A.ItemCode AND B.PriceList = '1' --CAMBIA EL 1 POR EL QUE NECESITES
LEFT JOIN ITM1 c ON c.ItemCode = A.ItemCode AND c.PriceList = '2' --CAMBIA EL 2 POR EL QUE NECESITES
GROUP BY ob.itemcode, A.numincnt, A.ItemName, A.AvgPrice, B.Price, C.Price
ORDER BY itemcode
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
eso es porque hay varias bodegas, si quieres una en especifico ingresa el código de la bodega en donde esta el left join de la OITW. abajo esta el mensaje
DECLARE @FechaInicial DATETIME
DECLARE @FechaFinal DATETIME
SET @FechaInicial=('20140901')
SET @FechaFinal=('20140930')
SELECT
ob.itemcode,
A.ItemName dscription,
SUM(ob.unidades) As unidades
,ROUND(SUM(ob.total),2) as 'Total',
case when Sum(ob.unidades)= 0 then 0 else (SUM(ob.total)/Sum(ob.unidades)) end precio,
D.AvgPrice PrecioPromedio,
B.Price 'Precio de Lista1',
C.Price 'Precio de Lista2'
FROM (
SELECT c.itemcode, SUM(c.unidades) unidades, SUM(c.total) total FROM(
SELECT DISTINCT B.itemcode, SUM(B.unidades) unidades, SUM(total2) total FROM(
SELECT * FROM (
SELECT distinct
T0.DocEntry,
t0.DocNum,
T1.LineNum,
T1.ItemCode as 'itemcode',
T1.InvQty 'unidades',
--T1.Linetotal 'total',
--((t0.DiscPrcnt / 100)) porcentaje,
(T1.Linetotal-(((T0.DiscPrcnt / 100)) * T1.Linetotal)) total2
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.DocDate between @FechaInicial AND @FechaFinal
AND T0.CANCELED ='N'
GROUP BY
T0.DocEntry,
T0.DocNum,
T1.LineNum,
T1.ItemCode,
T2.NumInCnt,
T1.InvQty,
T1.LineTotal,
T0.DiscPrcnt
)A
)B
GROUP BY B.itemcode
UNION ALL
SELECT DISTINCT B.itemcode, SUM(B.unidades) unidades, SUM(total2) total FROM(
SELECT * FROM (
SELECT distinct
T0.DocEntry,
t0.DocNum,
T1.LineNum,
T1.ItemCode as 'itemcode',
CASE
WHEN T0.CANCELED IN ('N','Y') THEN -T1.InvQty
ELSE T1.InvQty
END 'unidades',
CASE
WHEN T0.CANCELED IN ('N','Y') THEN -(T1.Linetotal-(((t0.DiscPrcnt / 100)) * T1.Linetotal))
ELSE (T1.Linetotal-(((t0.DiscPrcnt / 100)) * T1.Linetotal))
END total2
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.DocDate BETWEEN @FechaInicial AND @FechaFinal
GROUP BY
T0.DocEntry,
T0.DocNum,
T0.CANCELED,
T1.LineNum,
T1.ItemCode,
T2.NumInCnt,
T1.InvQty,
T1.LineTotal,
T0.DiscPrcnt
)A
)B
GROUP BY B.itemcode
)C
GROUP BY c.itemcode
----------------------------------------------------------------------------------------------
) as Ob
INNER JOIN OITM A ON A.ItemCode = ob.itemcode
LEFT JOIN ITM1 B ON B.ItemCode = A.ItemCode AND B.PriceList = '1' --CAMBIA EL 1 POR EL QUE NECESITES
LEFT JOIN ITM1 c ON c.ItemCode = A.ItemCode AND c.PriceList = '2' --CAMBIA EL 2 POR EL QUE NECESITES
LEFT JOIN OITW D ON D.ItemCode = ob.itemcode AND D.WhsCode = '' -- COLOCA EL CODIGO DEL ALMACEN EN LAS COMILLAS
GROUP BY ob.itemcode, A.numincnt, A.ItemName, A.AvgPrice, B.Price, C.Price, D.AvgPrice
ORDER BY itemcode
User | Count |
---|---|
95 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
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.