on 10-29-2014 5:22 PM
Jovenes expertos tengo un query que esta basado en ventas pero necesito insertar las informacion siempre referente los articulos pero cuando inserto la informacion si me muestra los datos pero solo de los que tienen ventas de los que no tienen ventas no me los muestra me podrias ayudar a solucionar este problemita.
Les dejo el query para que le hechen una vista
SELECT | |
a.itemcode, | |
A.ItemName dscription, | |
SUM(ob.unidades) As unidades, | |
ROUND(SUM(ob.total),2) as 'Total', | |
( |
--Query para inventario inicial prodter costo
SELECT sum(costo)
from v_inventario_final_prod_ter t10,itm1 t11
where t11.ItemCode=t10.codigo
and t10.docdate < @FechaInicial
-- and t10.codigoplanta= T5.U_Cod_Planta
and t10.codigo=A.ItemCode
and t11.pricelist='1'
) Costo_Inv_INI,
(
--Query para inventario final prodter costo
SELECT sum(costo)
from v_inventario_final_prod_ter t10,itm1 t11
where t11.ItemCode=t10.codigo
and t10.docdate <= @FechaFinal
--and t10.codigoplanta= T5.U_Cod_Planta
and t10.codigo=A.ItemCode
and t11.pricelist='1'
) Costo_Inv_Fin,
( | |
--QUERY para costo de producción |
SELECT
Sum(V_CONSUMO_Y_PRODUCCION.Costo) AS 'costo'
FROM V_CONSUMO_Y_PRODUCCION V_CONSUMO_Y_PRODUCCION
WHERE
(V_CONSUMO_Y_PRODUCCION.DueDate BETWEEN @FechaInicial AND @FechaFinal)
--AND (V_CONSUMO_Y_PRODUCCION.CodigoPlanta= T5.U_Cod_Planta)
and V_CONSUMO_Y_PRODUCCION.GRUPO = A.ItemCode
--fin costo de produccion
)costo_Produccion,
(
SELECT SUM(Mano_Obra_Directa)
FROM [_SBOV_MANO_DE_OBRA_DIRECTA]
WHERE
[_SBOV_MANO_DE_OBRA_DIRECTA].DocDate BETWEEN @FechaInicial and @FechaFInal
and [_SBOV_MANO_DE_OBRA_DIRECTA].Itemcode like a.itemcode
)ManoObraDiecta,
(
SELECT
sum(fabricado)
FROM [_SBOV_PRODUCCION_PERIODO_CJ]
where DueDate BETWEEN @FechaInicial AND @FechaFinal
and ItemCode=A.Itemcode
)[PRODUCCION_PERIODO_%]
----INICIO OB--- |
FROM (
SELECT c.itemcode, SUM(c.unidades) unidades, | |||||||||
SUM(c.total) total | |||||||||
----INICIO C --- | |||||||||
FROM( | |||||||||
SELECT DISTINCT B.itemcode, SUM(B.unidades) unidades, | |||||||||
SUM(total2) total | |||||||||
---INICIO B -- | |||||||||
FROM( | |||||||||
SELECT * | |||||||||
---INICIO A--- | |||||||||
FROM ( | |||||||||
SELECT distinct | |||||||||
T0.DocEntry,t0.DocNum,T1.LineNum,T2.ItemCode as 'itemcode', | |||||||||
isnull((T1.PackQty),0) 'unidades', | |||||||||
(T1.Linetotal-(((T0.DiscPrcnt / 100)) * T1.Linetotal)) total2 | |||||||||
FROM OINV T0 INNER JOIN | |||||||||
INV1 T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER 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, T1.LineTotal, | |||||||||
T0.DiscPrcnt, T1.PackQty,T2.ItemCode | |||||||||
)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,T2.ItemCode as 'itemcode', | |||||||||
CASE | |||||||||
WHEN T0.CANCELED IN ('N','Y') THEN -T1.PackQty | |||||||||
ELSE T1.PackQty | |||||||||
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 LEFT OUTER 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, | |||||||||
T1.LineTotal,T0.DiscPrcnt, T1.PackQty, T2.ItemCode | |||||||||
)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.ItemName, A.ItemCode
RDER BY itemcode
Agradeciendo su ayuda y su tiempo
Saludos Cordiales
Carlos,
Intenta cambiar los LEFT por RIGHT. Con esto el JOIN se va ir por OITM en lugar de OINV/ORIN.
Adicional, posiblemente tendrás que hacer algunos ajustes en los campos que mandas llamar de INV1 / RIN1 para cuando no existe la venta.
Saludos,
JC.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Intenta así:
En lugar (desde la línea 111 a la 129):
FROM OINV T0 INNER JOIN
INV1 T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER 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, T1.LineTotal,
T0.DiscPrcnt, T1.PackQty,T2.ItemCode
Ponlo así:
FROM OITM T2
LEFT JOIN INV1 T1 ON T1.ItemCode = T2.ItemCode
INNER JOIN OINV T0 ON T0.DocEntry = T1.DocEntry AND T0.DocDate between @FechaInicial AND @FechaFinal AND T0.CANCELED ='N'
GROUP BY T0.DocEntry,T0.DocNum,T1.LineNum,T1.ItemCode, T1.LineTotal,
T0.DiscPrcnt, T1.PackQty,T2.ItemCode
En lugar (desde la línea 189 a la 204):
FROM ORIN T0 INNER JOIN
RIN1 T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER 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,
T1.LineTotal,T0.DiscPrcnt, T1.PackQty, T2.ItemCode
Ponlo así:
FROM OITM T2
LEFT JOIN RIN1 T1 ON T1.ItemCode = T2.ItemCode
INNER JOIN ORIN T0 ON T0.DocEntry = T1.DocEntry AND T0.DocDate BETWEEN @FechaInicial AND @FechaFinal
GROUP BY T0.DocEntry,T0.DocNum,T0.CANCELED,T1.LineNum,T1.ItemCode, T1.LineTotal,T0.DiscPrcnt, T1.PackQty, T2.ItemCode
Espero te funcione.
Saludos,
JC.
User | Count |
---|---|
99 | |
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.