declare @fe1 date = '2020-03-01'
declare @fe2 date = '2020-03-05'
declare @codigo varchar(100) = null--'00001'
declare @filtro varchar(100) = NULL-- 'BA'
declare @ttipousuario varchar(2) = NULL
DECLARE @SqlDinamica nvarchar(max)=''
declare @fechas nvarchar(max)=''
set @fechas = (select distinct
STUFF ((
select distinct ',' + '['+CONVERT (varchar(10), CAST(t2.fRegistroContable AS datetime), 126 )+']'
from TDETALLEASISTENCIA t2
where
CONVERT (varchar(10), CAST(t2.fRegistroContable AS datetime), 126 )
between
CONVERT (varchar(10), CAST(@fe1 AS datetime), 126 ) and
CONVERT (varchar(10), CAST(@fe2 AS datetime), 126 )
for xml path ('')),1,1,'') as col from TDETALLEASISTENCIA t1)
set @SqlDinamica = @SqlDinamica + '
SELECT * FROM
(
select
X1.tCodigo ,
(CASE WHEN X1.tTipoUsuario =''US'' THEN
X2.tDetallado
ELSE
X3.Descripcion
END
)USUARIO,
x1.tTipoUsuario,
(CASE WHEN x1.ttipomarca =''I'' THEN
''Ingreso''
ELSE
''Salida''
END)ttipomarca,
CONVERT (varchar(10), CAST(X1.fRegistroContable AS datetime), 126 ) fRegistroContable,
MIN(CONVERT(varchar,x1.tHora,108)) HI
from
TDETALLEASISTENCIA x1
left join TUSUARIO x2
on x1.tCodigo = x2.tCodigoUsuario
and x1.tTipoUsuario =''US''
left join vMozo x3
on x1.tCodigo = x3.Codigo
and x1.tTipoUsuario = ''MO''
WHERE
tTipoMarca =''I''
'
if @fe1 <> null or @fe1 <>''
begin
set @SqlDinamica = @SqlDinamica + ' and CONVERT (varchar(10), CAST(X1.fRegistroContable AS datetime), 126 ) between ''' + CONVERT (varchar(10), CAST(@fe1 AS datetime), 126 )+''' and ''' + CONVERT (varchar(10), CAST(@fe2 AS datetime), 126 )+''''
end
if @ttipousuario <> null OR @ttipousuario <>''
begin
set @SqlDinamica = @SqlDinamica + ' and x1.tTipoUsuario = ''' + @ttipousuario +''''
end
if @codigo <> null OR @codigo <>''
begin
set @SqlDinamica = @SqlDinamica + ' and x1.tCodigo = ''' + @codigo +''''
end
if @filtro <> null OR @filtro <>''
begin
set @SqlDinamica = @SqlDinamica + ' and X2.tDetallado LIKE ''%' + @filtro +'%'' OR X3.Descripcion LIKE ''%' + @filtro +'%'''
end
set @SqlDinamica = @SqlDinamica +'
GROUP BY X1.tCodigo , X2.tDetallado, X3.Descripcion, x1.tTipoUsuario,X1.ttipomarca,
CONVERT (varchar(10), CAST(X1.fRegistroContable AS datetime), 126 ) '
set @SqlDinamica = @SqlDinamica + 'UNION ALL '
set @SqlDinamica = @SqlDinamica + ' select
X1.tCodigo ,
(CASE WHEN X1.tTipoUsuario =''US'' THEN
X2.tDetallado
ELSE
X3.Descripcion
END
)USUARIO,
x1.tTipoUsuario,
(CASE WHEN x1.ttipomarca =''I'' THEN
''Ingreso''
ELSE
''Salida''
END)ttipomarca,
CONVERT (varchar(10), CAST(X1.fRegistroContable AS datetime), 126 ) fRegistroContable,
MAX(CONVERT(varchar,x1.tHora,108)) HI
from
TDETALLEASISTENCIA x1
left join TUSUARIO x2
on x1.tCodigo = x2.tCodigoUsuario
and x1.tTipoUsuario =''US''
left join vMozo x3
on x1.tCodigo = x3.Codigo
and x1.tTipoUsuario = ''MO''
WHERE
tTipoMarca =''S''
'
if @fe1 <> null or @fe1 <>''
begin
set @SqlDinamica = @SqlDinamica + ' and CONVERT (varchar(10), CAST(X1.fRegistroContable AS datetime), 126 ) between ''' + CONVERT (varchar(10), CAST(@fe1 AS datetime), 126 )+''' and ''' + CONVERT (varchar(10), CAST(@fe2 AS datetime), 126 )+''''
end
if @ttipousuario <> null OR @ttipousuario <>''
begin
set @SqlDinamica = @SqlDinamica + ' and x1.tTipoUsuario = ''' + @ttipousuario +''''
end
if @codigo <> null OR @codigo <>''
begin
set @SqlDinamica = @SqlDinamica + ' and x1.tCodigo = ''' + @codigo +''''
end
if @filtro <> null OR @filtro <>''
begin
set @SqlDinamica = @SqlDinamica + ' and X2.tDetallado LIKE ''%' + @filtro +'%'' OR X3.Descripcion LIKE ''%' + @filtro +'%'''
end
set @SqlDinamica = @SqlDinamica + '
GROUP BY X1.tCodigo , X2.tDetallado, X3.Descripcion, x1.tTipoUsuario,X1.ttipomarca,
CONVERT (varchar(10), CAST(X1.fRegistroContable AS datetime), 126 )
)MAIN
pivot (
MAX(HI) for fRegistroContable in ('+@fechas+'))as tabla
'
print @SqlDinamica
exec SP_EXECUTESQL @SqlDinamica
Crear cuenta
Comentarios sobre la versión: 1 (0)
No hay comentarios