on 05-16-2011 9:15 PM
Buenas tardes,
Tengo la siguiente query que me muestra los inventarios iníciales y finales, el problema que tengo que cuando lo genero alguno de los artículos me los duplica, no he podido encontrar el porquéu2026
DECLARE @FechaInicial DATETIME
DECLARE @FechaFinal DATETIME
DECLARE @RangoFech INT
SET @RangoFech=(SELECT TOP 1 T0.Transnum FROM OINM T0 WHERE T0.[DocDate] >='04/01/2011' AND T0.[DocDate] <='04/30/2011')
SET @FechaInicial=(SELECT '04/01/2011')
SET @FechaFinal=(SELECT '04/30/2011')
SELECT
=ISNULL(COALESCE(T0.SaldoInic,0),0),
=(SELECT TOP 1 CostoInicial=ISNULL(COALESCE(S2.CalcPrice,0),0) FROM OINM S2 JOIN OITM A2 ON A2.ItemCode=S2.ItemCode
WHERE S2.DocDate<@FechaFinal AND A2.ItemCode=T1.ItemCode AND S2.CalcPrice <> 0 AND S2.Transnum = (SELECT MAX(S3.Transnum) FROM OINM S3 WHERE S3.DocDate<@FechaFinal AND S2.ItemCode=S3.ItemCode AND S3.CalcPrice <> 0)
GROUP BY A2.ItemCode, S2.CalcPrice) * ISNULL(COALESCE(T0.SaldoInic,0),0),
=ISNULL(COALESCE(T1.Entradas,0),0),
=ISNULL(COALESCE(T1.Consumo,0),0),
=ISNULL(COALESCE(T0.SaldoInic,0)+COALESCE(T1.SaldoFin,0),0),
= (SELECT TOP 1 Costo=ISNULL(COALESCE(S2.CalcPrice,0),0) FROM OINM S2 JOIN OITM A2 ON A2.ItemCode=S2.ItemCode
WHERE S2.DocDate<@FechaFinal AND A2.ItemCode=T1.ItemCode AND S2.CalcPrice <> 0 AND S2.Transnum = (SELECT MAX(S3.Transnum) FROM OINM S3 WHERE S3.DocDate<@FechaFinal AND S2.ItemCode=S3.ItemCode AND S3.CalcPrice <> 0)
GROUP BY A2.ItemCode, S2.CalcPrice) * ISNULL(COALESCE(T0.SaldoInic,0)+COALESCE(T1.SaldoFin,0),0)
FROM (
SELECT A1.ItemCode,A1.ItmsGrpCod,S1.Dscription,
SaldoInic=ISNULL(SUM(COALESCE(S1.Inqty,0))-SUM(COALESCE(S1.outqty,0)),0)
FROM OITM A1 JOIN OINM S1 ON A1.ItemCode=S1.ItemCode
WHERE S1.DocDate<@FechaInicial AND S1.Warehouse='PV-PRA'
GROUP BY A1.ItemCode,A1.ItmsGrpCod,S1.Dscription
) AS T0 RIGHT JOIN (
SELECT A.ItemCode,A.ItmsGrpCod,S.Dscription,
Entradas=ISNULL(SUM(S.Inqty),0),
Consumo=ISNULL(SUM(S.Outqty),0),
SaldoFin=ISNULL(SUM(S.Inqty)-SUM(S.Outqty),0)
FROM OINM S JOIN OITM A ON A.ItemCode=S.ItemCode
WHERE S.DocDate BETWEEN @FechaInicial AND @FechaFinal AND S.Warehouse='PV-PRA'
GROUP BY A.ItemCode,A.ItmsGrpCod,S.Dscription
) T1 ON T1.ItemCode=T0.ItemCode
Atentemente
JUAN CAMILO GÓMEZ
Te recomiendo que hagas un select distinct para tus items , quizas en algun join estas trayendo los datos repetidos
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 | |
11 | |
11 | |
6 | |
6 | |
5 | |
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.