on 10-28-2014 9:45 PM
Buenas tardes jovenes expertos perdone pero necesito unir dos querys pero no encuentro la forma tal vez ustedes me pueda ayudar.
Query No. 1 (Este necesito que solicite los campos de seleccion) y este me da los datos para un inventario incial y final en base a los articulos.
DECLARE @Planta nvarchar(8)
DECLARE @FechaIni datetime
DECLARE @FechaFin datetime
SET @PLANTA='P09'
SET @FECHAINI ='20140701'
SET @FECHAFIN ='20140731'
SELECT
T5.U_Cod_Planta, T0.ItemCode, T3.ItemName,
''VENTAS,
(
--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 < @FechaIni
and t10.codigoplanta= T5.U_Cod_Planta
and t10.codigo=T0.ItemCode
and t11.pricelist='1'
--group by nombre, nombre_rubro,orden,t1.price
) 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 <= @FechaFin
and t10.codigoplanta= T5.U_Cod_Planta
and t10.codigo=T0.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 @FechaIni AND @FechaFin)
AND (V_CONSUMO_Y_PRODUCCION.CodigoPlanta= T5.U_Cod_Planta)
and V_CONSUMO_Y_PRODUCCION.GRUPO = T0.ItemCode
--fin costo de produccion
)costo_Produccion_Orig,
''fabricado
FROM
OWOR T0
LEFT JOIN OITM T3 ON T3.ItemCode = T0.ItemCode
left join OWHS T5 ON T5.WhsCode=T0.Warehouse
left outer join ITM1 t6 on T3.ItemCode=t6.ItemCode
WHERE
T0.DueDate BETWEEN @FechaIni AND @FechaFin
and T5.U_Cod_Planta= @Planta
and t6.PriceList='1'
AND T0.ITEMCODE NOT IN ('MPCEMGRANEL','MPCEMGRANELCFB')
group by T5.U_Cod_Planta, T0.ItemCode, T3.ItemName,t6.Price, T3.ItemCode
ORDER BY T0.ItemCode
Query No. 2 este genera las ventas por un rango de fechas por articulo
DECLARE @FechaInicial DATETIME
DECLARE @FechaFinal DATETIME
--DECLARE @Planta VARCHAR(8)
SET @FechaInicial=('20140701')
SET @FechaFinal=('20140731')
--SET @Planta=('P09')
SELECT DISTINCT ob.itemcode, A.ItemName dscription, SUM(ob.unidades) As unidades,
ROUND(SUM(ob.total),2) as 'VENTAS'
----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,T1.ItemCode as 'itemcode',T1.PackQty 'unidades',
(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,t0.docdate,/*T2.NumInCnt,T1.InvQty,*/ T1.LineTotal,
T0.DiscPrcnt, T1.PackQty
)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.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 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,t0.docdate,/*T2.NumInCnt,T1.InvQty,*/
T1.LineTotal,T0.DiscPrcnt, T1.PackQty
)A
)B
GROUP BY B.itemcode
)C
GROUP BY c.itemcode
----------------------------------------------------------------------------------------------
) as Ob
INNER JOIN OITM A ON A.ItemCode = ob.itemcode INNER JOIN ITM1 b ON b.ItemCode = a.ItemCode
GROUP BY ob.itemcode, A.ItemName, a.itemcode, b.pricelist
ORDER BY itemcode
Espero me puedas ayudar que llevo dos dias tratando de unificarlos pero no me funciona
De antemano les estare muy agradecido por ayuda y por su tiempo.
Saludos Cordiales
Ya encontre la solucion se agradece la ayuda.
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Con gusto Pablo este es
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
ORDER BY itemcode
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
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.