IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ind_motivoReprogramacion]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ind_motivoReprogramacion]
GO
CREATE PROCEDURE [dbo].[ind_motivoReprogramacion]
	@p_fecha_fh as smalldatetime = null,
    @p_fecha_fh_b as smalldatetime = null,
	@p_id as int=null,
	@p_tercero as int= null,
    @p_sucursal as int= null,
	@p_tipoServicio as int= null,
    @p_departamentoSolicitud as int= null,
    @p_subtipoDocumento as int= null,
    @p_estadoTipodocumento as int =null,
    @p_eid varchar(100)

AS
BEGIN



select 'Total' as serie1 , count (MC.id)as valor   from 
ort_programacion as  prog inner join ort_motivoCancelacion as MC on MC.id=prog.id_motivoCancelacion
inner join [doc_documento.ot] as ot  on prog.[id_documento.ot] =ot.id
inner join doc_documento as doc on doc.id=ot.id
where 
	(@p_fecha_fh is null or @p_fecha_fh >= prog.desde_fh )and
    (@p_fecha_fh_b is null or @p_fecha_fh >= prog.desde_fh )and
	(@p_id is null or @p_id=ot.id )and 
	(@p_tercero is null or @p_tercero = doc.id_tercero_cliente)and
    (@p_sucursal is null or @p_sucursal= ot.id_sucursal)and
	(@p_tipoServicio is null or @p_tipoServicio= ot.id_tipoServicio )and
    (@p_departamentoSolicitud is null or @p_departamentoSolicitud =ot.id_departamentoSolicitud)and
    (@p_subtipoDocumento is null or @p_subtipoDocumento= doc.id_subtipoDocumento)and
    (@p_estadoTipodocumento is null or @p_estadoTipodocumento= doc.id_estadoTipoDocumento)and
    (@p_eid is null or prog.eid like @p_eid + '%')and 
     MC.id>0 and MC.active=1




select MC.motivoCancelacion as serie1 , count (MC.id)as valor  from 
ort_programacion as  prog inner join ort_motivoCancelacion as MC on MC.id=prog.id_motivoCancelacion
inner join [doc_documento.ot] as ot  on prog.[id_documento.ot] =ot.id
inner join doc_documento as doc on doc.id=ot.id
where 
	(@p_fecha_fh is null or @p_fecha_fh >= prog.desde_fh )and
    (@p_fecha_fh_b is null or @p_fecha_fh >= prog.desde_fh )and
	(@p_id is null or @p_id=ot.id )and 
	(@p_tercero is null or @p_tercero = doc.id_tercero_cliente)and
    (@p_sucursal is null or @p_sucursal= ot.id_sucursal)and
	(@p_tipoServicio is null or @p_tipoServicio= ot.id_tipoServicio )and
    (@p_departamentoSolicitud is null or @p_departamentoSolicitud =ot.id_departamentoSolicitud)and
    (@p_subtipoDocumento is null or @p_subtipoDocumento= doc.id_subtipoDocumento)and
    (@p_estadoTipodocumento is null or @p_estadoTipodocumento= doc.id_estadoTipoDocumento)and
    (@p_eid is null or prog.eid like @p_eid + '%')and 
     MC.id>0 and MC.active=1
 group by MC.id,MC.motivoCancelacion
 end