Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Crystal Report

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

=T1.ItemCode,

=T1.ItmsGrpCod,

= T1.Dscription,

=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

Former Member
Former Member replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question