
/****** Object:  StoredProcedure [dbo].[a_sel_recordatorios]    Script Date: 11/22/2011 19:26:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[a_sel_recordatorios]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[a_sel_recordatorios]
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[a_sel_recordatorios]
 @p_pasados as bit = 1,
 @p_filtro as varchar(8000)='1=1'
AS BEGIN

declare @sentencia as varchar(8000)

if @p_pasados=1
begin

set @sentencia ='
 declare @lunes as smalldatetime
 set @lunes=dateadd(day,-DATEPART(dw, GETDATE()),getdate())

 select 
 count( case when datepart(year,fechaRecordar_fh)<datepart(year,getdate()) then
   id
  else
   null
  end) as otrosAnos,
 min(case when datepart(year,fechaRecordar_fh)<datepart(year,getdate()) then
   id
  else
   null
  end) as minOtrosAnos,
 count( case when datepart(year,fechaRecordar_fh)=datepart(year,getdate()) 
     and datepart(month,fechaRecordar_fh)<datepart(month,getdate()) then
   id
  else
   null
  end) as esteAno,
min( case when datepart(year,fechaRecordar_fh)=datepart(year,getdate()) 
     and datepart(month,fechaRecordar_fh)<datepart(month,getdate()) then
   id
  else
   null
  end) as minEsteAno,
 count( case when datepart(year,fechaRecordar_fh)=datepart(year,getdate()) 
     and datepart(month,fechaRecordar_fh)=datepart(month,getdate())
     and fechaRecordar_fh<@lunes then
   id
  else
   null
  end) as esteMes,
min(case when datepart(year,fechaRecordar_fh)=datepart(year,getdate()) 
     and datepart(month,fechaRecordar_fh)=datepart(month,getdate())
     and fechaRecordar_fh<@lunes then
   id
  else
   null
  end) as minEsteMes,
 count( case when fechaRecordar_fh>=@lunes 
    and fechaRecordar_fh<getdate()
    and day(fechaRecordar_fh)<day(getdate()) then
   id
  else
   null
  end) as estaSemana,
min(case when fechaRecordar_fh>=@lunes 
    and fechaRecordar_fh<getdate()
    and day(fechaRecordar_fh)<day(getdate()) then
   id
  else
   null
  end) as minEstaSemana,
 count( case when fechaRecordar_fh>=@lunes 
    and fechaRecordar_fh<getdate()
    and day(fechaRecordar_fh)=day(getdate())
    and datepart(hour,fechaRecordar_fh)<datepart(hour,getdate()) then
   id
  else
   null
  end) as esteDia,
min(case when fechaRecordar_fh>=@lunes 
    and fechaRecordar_fh<getdate()
    and day(fechaRecordar_fh)=day(getdate())
    and datepart(hour,fechaRecordar_fh)<datepart(hour,getdate()) then
   id
  else
   null
  end) as minEsteDia,
 count( case when fechaRecordar_fh>=@lunes 
    and fechaRecordar_fh<getdate()
    and day(fechaRecordar_fh)=day(getdate())
    and datepart(hour,fechaRecordar_fh)=datepart(hour,getdate()) then
   id
  else
   null
  end) as estaHora,
min( case when fechaRecordar_fh>=@lunes 
    and fechaRecordar_fh<getdate()
    and day(fechaRecordar_fh)=day(getdate())
    and datepart(hour,fechaRecordar_fh)=datepart(hour,getdate()) then
   id
  else
   null
  end) as minEstaHora,
 count (id) as total
 from doc_documentoComentario where '
end
else
begin
set @sentencia ='
 declare @viernes as smalldatetime
 set @viernes=dateadd(day,(7-DATEPART(dw, GETDATE())),getdate())

 select 
 count( case when datepart(year,fechaRecordar_fh)>datepart(year,getdate()) then
   id
  else
   null
  end) as otrosAnos,
min( case when datepart(year,fechaRecordar_fh)>datepart(year,getdate()) then
   id
  else
   null
  end) as minOtrosAnos,
 count( case when datepart(year,fechaRecordar_fh)=datepart(year,getdate()) 
     and datepart(month,fechaRecordar_fh)>datepart(month,getdate()) then
   id
  else
   null
  end) as esteAno,
min( case when datepart(year,fechaRecordar_fh)=datepart(year,getdate()) 
     and datepart(month,fechaRecordar_fh)>datepart(month,getdate()) then
   id
  else
   null
  end) as minEsteAno,
 count( case when datepart(year,fechaRecordar_fh)=datepart(year,getdate()) 
     and datepart(month,fechaRecordar_fh)=datepart(month,getdate())
     and fechaRecordar_fh>@viernes then
   id
  else
   null
  end) as esteMes,
min( case when datepart(year,fechaRecordar_fh)=datepart(year,getdate()) 
     and datepart(month,fechaRecordar_fh)=datepart(month,getdate())
     and fechaRecordar_fh>@viernes then
   id
  else
   null
  end) as minEsteMes,
 count( case when fechaRecordar_fh<=@viernes
    and fechaRecordar_fh>getdate()
    and day(fechaRecordar_fh)>day(getdate()) then
   id
  else
   null
  end) as estaSemana,
min( case when fechaRecordar_fh<=@viernes
    and fechaRecordar_fh>getdate()
    and day(fechaRecordar_fh)>day(getdate()) then
   id
  else
   null
  end) as minEstaSemana,
 count( case when fechaRecordar_fh<=@viernes
    and fechaRecordar_fh>getdate()
    and day(fechaRecordar_fh)=day(getdate())
    and datepart(hour,fechaRecordar_fh)>datepart(hour,getdate()) then
   id
  else
   null
  end) as esteDia,
min( case when fechaRecordar_fh<=@viernes
    and fechaRecordar_fh>getdate()
    and day(fechaRecordar_fh)=day(getdate())
    and datepart(hour,fechaRecordar_fh)>datepart(hour,getdate()) then
   id
  else
   null
  end) as minEsteDia,
 count( case when fechaRecordar_fh<=@viernes
    and fechaRecordar_fh>getdate()
    and day(fechaRecordar_fh)=day(getdate())
    and datepart(hour,fechaRecordar_fh)=datepart(hour,getdate()) then
   id
  else
   null
  end) as estaHora,
min( case when fechaRecordar_fh<=@viernes
    and fechaRecordar_fh>getdate()
    and day(fechaRecordar_fh)=day(getdate())
    and datepart(hour,fechaRecordar_fh)=datepart(hour,getdate()) then
   id
  else
   null
  end) as minEstaHora,
 count (id) as total
 from doc_documentoComentario where '
end

exec (@sentencia + @p_filtro +'  and cerrada=0')

END
