on 05-13-2011 11:02 PM
Buenas tardes,
Tengo una query la cual la voy a manejar en crystal insertando el código en comandos, pero no sé cómo manejar las variables en crystal, cuando ingreso en el código la fecha me funciona correctamente
DECLARE @FechaInicial DATETIME
DECLARE @FechaFinal DATETIME
DECLARE @RangoFech INT
SET @RangoFech=(SELECT TOP 1 T0.Transnum FROM OINM T0 WHERE T0.[DocDate] >='{?FechaInicial}' AND T0.[DocDate] <='{?FechaFinal}')
SET @FechaInicial=(SELECT '{?FechaInicial}')
SET @FechaFinal=(SELECT '{?FechaFinal}')
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='{?Bodega}'
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='{?Bodega}'
GROUP BY A.ItemCode,A.ItmsGrpCod,S.Dscription
) T1 ON T1.ItemCode=T0.ItemCode
Atentamente
JUAN CAMILO GÓMEZ
Yo te recomiendo que manejes tu reporte como stored procedure y tus variables las podras usar como parametros para enviarlos a tu stored, este lo puedes traer desde Crystal y usarlo como si fuera una tabla con sus campos.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Como store procedure no lo hice, le quite la variable del rango y me da perfectamente.
Una pregunta lo que pasa nuevamente modifique ese informe y le puse, costo a las entradas y salidas, pero el problema que se demora mucho en ejecutar el proceso, hay una forma mucho fácil de hacer este mismo informe
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),
=(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(T1.Entradas,0),0),
=ISNULL(COALESCE(T1.Consumo,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(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,A1.ItemName,
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='PB-PT'
GROUP BY A1.ItemCode,A1.ItmsGrpCod,A1.ItemName
) AS T0 RIGHT JOIN (
SELECT A.ItemCode,A.ItmsGrpCod,A.ItemName,
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='PB-PT'
GROUP BY A.ItemCode,A.ItmsGrpCod,A.ItemName
) T1 ON T1.ItemCode=T0.ItemCode
O sera que como store porcedure me funciona mucho mas rápido
JUAN CAMILO GÓMEZ
Juan, dejo query final
/*SELECT * FROM OINM Y WHERE Y.DocDate >= '[%0]' AND Y.DocDate <= '[%1]'*/
DECLARE @Fechaini DATETIME
SET @Fechaini = '[%0]'
DECLARE @Fechafin DATETIME
SET @Fechafin = '[%1]'
SELECT T0.[ItemCode]'Código Artículo', T2.[ItemName]'Nombre Artículo', T1.[WhsName]'Nombre Almacén',
ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.Warehouse = T1.WhsCode AND Y.DocDate < @Fechaini),0) 'Stock Inicial',
ISNULL((SELECT (SUM(Y.InQty*Y.CalcPrice)-SUM(Y.OutQty*Y.CalcPrice)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.Warehouse = T1.WhsCode AND Y.DocDate < @fechaini),0)'Costo Stock Inicial',
ISNULL((SELECT (SUM(Y.InQty)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.Warehouse = T1.WhsCode AND Y.DocDate BETWEEN @Fechaini AND @fechafin),0) 'Entradas',
ISNULL((SELECT (SUM(Y.InQty*Y.CalcPrice)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.Warehouse = T1.WhsCode AND Y.DocDate BETWEEN @Fechaini AND @fechafin),0) 'Costo Entradas',
ISNULL((SELECT (SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.Warehouse = T1.WhsCode AND Y.DocDate BETWEEN @Fechaini AND @fechafin),0) 'Salidas',
ISNULL((SELECT (SUM(Y.OutQty*Y.CalcPrice)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.Warehouse = T1.WhsCode AND Y.DocDate BETWEEN @Fechaini AND @fechafin),0) 'Costo Salidas',
ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.Warehouse = T1.WhsCode AND Y.DocDate <= @fechafin),0) 'Stock',
ISNULL((SELECT (SUM(Y.InQty*Y.CalcPrice)-SUM(Y.OutQty*Y.CalcPrice)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.Warehouse = T1.WhsCode AND Y.DocDate <= @fechafin),0)'Costo Stock'
FROM OITW T0
INNER JOIN OWHS T1 ON T0.WhsCode = T1.WhsCode
INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode
WHERE (SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.Warehouse = T1.WhsCode AND Y.DocDate <= @fechafin) >'0'
ORDER BY T0.[WhsCode], T0.[ItemCode]
Atte.
Felipe Loyola
Agradezco si me pueden enviar el manual a mi correo cortega @ casamedic.com
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.