Código SQL
[-]
select distinct a.cflocid, a.ccompanyid, d.ccompany,c.cdeptno, c.cdeptname, a.cempno,b.clname + ' , ' +b.cfname fullname,
a.dbper,a.deper,
a.nqty, e.cdesc,f.cdesc,a.laffect,a.laffectnom,fecha, (case when fecha<>a.dbper then 'O' else 'X' end) as asistencia
from prausper a join prempy b on a.cempno=b.cempno join prdept c on c.cdeptno = b.cdeptno join grpsyst d on a.ccompanyid = d.ccompanyid join hrtypper e on e.ctpermno = a.ctpermno join hrreason f on f.creasno=a.creasno,
#fecha
where b.cstatus in ('A','P')
and a.cempno between @cempnoi and @cempnof
and a.cdeptno between @cdeptnoi and @cdeptnof
and( convert(varchar,a.dbper,111) between @dfechai and @dfechaf
or convert(varchar,a.deper,111) between @dfechai and @dfechaf)
and a.cflocid = @cflocid
and a.ccompanyid between @ccompanyidi and @ccompanyidf
group by fecha,a.dbper,a.deper,a.cflocid, a.ccompanyid, d.ccompany,c.cdeptno, c.cdeptname, a.cempno,b.clname,b.cfname,a.dbper,a.deper,
a.nqty, e.cdesc,f.cdesc,a.laffect,a.laffectnom
union all
select distinct a.cflocid, a.ccompanyid, d.ccompany,c.cdeptno, c.cdeptname, a.cempno,b.clname + ' , ' +b.cfname fullname,
a.dbper,a.deper,
a.nqty, e.cdesc,f.cdesc,a.laffect,a.laffectnom,fecha, (case when fecha=a.dbper then 'X' else 'O' end) as asistencia
from prausperh a join prempy b on a.cempno=b.cempno join prdept c on c.cdeptno = b.cdeptno join grpsyst d on a.ccompanyid = d.ccompanyid join hrtypper e on e.ctpermno = a.ctpermno join hrreason f on f.creasno=a.creasno,
#fecha
where b.cstatus in ('A','P')
and a.cempno between @cempnoi and @cempnof
and a.cdeptno between @cdeptnoi and @cdeptnof
and( convert(varchar,a.dbper,111) between @dfechai and @dfechaf
or convert(varchar,a.deper,111) between @dfechai and @dfechaf)
and a.cflocid = @cflocid
and a.ccompanyid between @ccompanyidi and @ccompanyidf
group by fecha,a.dbper,a.deper,a.cflocid, a.ccompanyid, d.ccompany,c.cdeptno, c.cdeptname, a.cempno,b.clname,b.cfname,
a.nqty, e.cdesc,f.cdesc,a.laffect,a.laffectnom
order by fecha
El query es el que esta arriba y el resultado lo coloco en una imagen adjunta.
se se dan cuenta en la imagen las columna dbper es la de las ausencias pero por aca ausencia que hay genera siempre la misma fecha, y lo que quiero es que la fecha no se duplique.
Saludos.