on 02-16-2016 5:38 PM
Buenas tardes foro tengo una consulta y no se si se pueda realizar
estoy realizando la siguiente consulta
declare @i datetime, @f datetime
set @i=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%0]')
set @f=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%1]')
select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',
B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',
B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'
from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry
WHERE A.DocDate BETWEEN @i and @f AND B.FromWhsCod = '[%2]' and A.U_Turno = '[%3]'
ORDER BY A.DocNum,B.FromWhsCod
donde me muestra los traspasos realizados por rango de fechas turno y almacen
pero quisiera saber si se puede realizar que cuando yo no quiera ingresar el filtro de almacenes o de turno me muestre todo los movimientos de tras pasos que se realizaron durante esa fecha.
por que actual mente si no le pongo el rango completo (fechas,almacen y turno) me marca error y quisiera saber si se puede realizar esta operación
Puedes agregar "If" para corroborar los parametros
Ejemplo
declare @i datetime, @f datetime
set @i=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%0]')
set @f=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%1]')
if (@i is null and @f is not null)
begin
select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',
B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',
B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'
from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry
WHERE A.DocDate <= @f AND B.FromWhsCod = '[%2]' and A.U_Turno = '[%3]'
ORDER BY A.DocNum,B.FromWhsCod
end
else if (@f is null and @i is not null)
begin
select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',
B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',
B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'
from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry
WHERE A.DocDate >= @i AND B.FromWhsCod = '[%2]' and A.U_Turno = '[%3]'
ORDER BY A.DocNum,B.FromWhsCod
end
else
select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',
B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',
B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'
from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry
WHERE A.DocDate between @i and @f AND B.FromWhsCod = '[%2]' and A.U_Turno = '[%3]'
ORDER BY A.DocNum,B.FromWhsCod
Saludos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
ok felipe
he estado tratando de programar los parámetros que me comentas
este es mi consulta si me puedes explicar como seria la estructura te lo agradecería por que he buscado en internet pero la verdad no entiendo como se componen estos parámetros
declare @i datetime, @f datetime, @g VarChar, @h VarChar
set @i=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%0]')
set @f=(/*select top 1 A.DocDate from OINV A where A.DocDate=*/'[%1]')
set @g=(/*select top 2 A.FromWhsCod from WTR1 A where A.FromWhsCod=*/'[%2]')
set @h=(/*select top 3 A.U_Turno from OWTR A where A.U_Turno=*/'[%3]')
if (@i is null and @f is not null and @g is not null and @h is not null )
begin
select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',
B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',
B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'
from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry
WHERE A.DocDate <= @f AND B.FromWhsCod = @g and A.U_Turno = @h
ORDER BY A.DocNum,B.FromWhsCod
end
else if (@f is null and @i is not null and @g is not null and @h is not null)
begin
select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',
B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',
B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'
from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry
WHERE A.DocDate >= @i AND B.FromWhsCod = @g and A.U_Turno = @h
ORDER BY A.DocNum,B.FromWhsCod
end
else
select A.DocNum as 'Documento',B.ItemCode as 'Numero de Parte',B.Dscription as 'Articulo',
B.FromWhsCod as 'Almacen Salida',B.WhsCode as 'Almacen entrada',
B.Quantity as 'Cantidad',A.DocDate AS 'Fecha'
from OWTR A INNER JOIN WTR1 B ON A.DocEntry = B.DocEntry
WHERE A.DocDate between @i and @f AND B.FromWhsCod = @g and A.U_Turno = @h
ORDER BY A.DocNum,B.FromWhsCod
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.