declare @USERID nvarchar(10)
set @USERID = 39162131

with marcaje as

(SELECT * 
 from marcaje3
 WHERE USERID = @USERID OR @USERID = '*'),

OrderTime as
(SELECT *, ROW_NUMBER() OVER (PARTITION BY USERID, DATEADD(day, DATEDIFF(day, 0, SCHEDULETIME), 0) ORDER BY SCHEDULETIME) AS rn
 From marcajes)

SELECT *
FROM OrderTime T1 
    left outer Join OrderTime T2 on DATEADD(day, DATEDIFF(day, 0, T1.SCHEDULETIME), 0) = DATEADD(day, DATEDIFF(day, 0, T2.SCHEDULETIME), 0) and 
	                                                                       T1.USERID = T2.USERID and 
									                           T1.rn = T2.rn - 1

with marcaje as

(SELECT * 
 from marcaje3
 WHERE USERID = @USERID OR @USERID = '*'),

OrderTime as
(SELECT *, ROW_NUMBER() OVER (PARTITION BY USERID, DATEADD(day, DATEDIFF(day, 0, SCHEDULETIME), 0) ORDER BY SCHEDULETIME) AS rn
 From marcajes)-- Should be only marcaje as in the previous cte, otherwise the table.

SELECT *
FROM OrderTime T1 
    left outer Join OrderTime T2 on DATEADD(day, DATEDIFF(day, 0, T1.SCHEDULETIME), 0) = DATEADD(day, DATEDIFF(day, 0, T2.SCHEDULETIME), 0) and 
	                                                                       T1.USERID = T2.USERID and 
									                           T1.rn = T2.rn - 1

SELECT a.USERID

FROM Marcaje3
      left outer join Usuario2_ConaccNis2 a on Marcaje3.nis = a.nis and 
                  Marcaje3.SCHEDULETIME between a.fechaAct and 
                                                CASE WHEN DATEADD(day, DATEDIFF(day, 0, a.fechaAct), 0) = DATEADD(day, DATEDIFF(day, 0, a.fechaAnu), 0) and 
                                                          a.estaAnulado = 0 
                                                     THEN getdate() 
                                                     ELSE a.fechaAnu
                                                     END

quote:

---

In fact one of the nonclustered indexes is related to USERID through another table

---

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Marcaje3](
	[idOrden] [int] IDENTITY(0,1) NOT NULL,
	[idCli] [int] NOT NULL,
	[idInst] [int] NOT NULL,
	[nis] [bigint] NOT NULL,
	[fechaHora] [datetime] NOT NULL,
	[st] [int] NOT NULL,
	[idCentro] [int] NOT NULL,
	[idNodo] [int] NOT NULL,
	[idTerm] [int] NOT NULL,
	[idInci] [int] NOT NULL,
	[esEntrada] [bit] NOT NULL,
	[esSalida] [bit] NOT NULL,
	[esManual] [bit] NOT NULL,
	[estaModificado] [bit] NOT NULL,
	[idOElab] [int] NOT NULL,
	[idOp] [int] NOT NULL,
	[cantidad] [int] NOT NULL,
	[tipo] [int] NOT NULL,
	[error] [int] NOT NULL,
	[jornada] [datetime] NULL,
	[otros] [text] NOT NULL DEFAULT (''),
	[esEntradaCondicional] [bit] NULL DEFAULT ((0)),
	[esSalidaCondicional] [bit] NULL DEFAULT ((0)),
	[esAlertaLocal] [bit] NULL DEFAULT ((0)),
	[zonaPanel] [int] NULL,
	[naturaleza] [int] NULL,
	[login] [varchar](64) NULL,
	[aplicacion] [int] NULL,
	[str1] [varchar](255) NULL,
	[str2] [varchar](255) NULL,
	[str3] [varchar](255) NULL,
	[Especial] [int] NULL DEFAULT ((0)),
	[esEntradaUsoEspecial2] [bit] NULL DEFAULT ((0)),
	[esSalidaUsoEspecial2] [bit] NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED 
(
	[idOrden] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

CREATE NONCLUSTERED INDEX [Marcaje_Nis] ON [dbo].[Marcaje3]
(
	[idCli] ASC,
	[idInst] ASC,
	[nis] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [Marcaje3_NIS_fechaHora] ON [dbo].[Marcaje3]
(
	[idCli] ASC,
	[idInst] ASC,
	[nis] ASC,
	[fechaHora] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Marcaje3] ADD PRIMARY KEY CLUSTERED 
(
	[idOrden] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

with marcajes as
	              (
	               SELECT Marcaje3.idOrden, Marcaje3.nis, Marcaje3.fechahora, Marcaje3.idInci, Usuario2_ConaccNis2.idUsr, idSeccion, Nombre + ' ' + Apellido1 as Nombre,
                          CASE WHEN Marcaje3.idInci <> 0 
                               THEN (SELECT idSeccion 
                                     FROM Incidencia
                                     WHERE Incidencia.idInci = Marcaje3.idInci)
                               ELSE idSeccion
                               END as Seccion
                   FROM Marcaje3
                        left outer join Usuario2_ConaccNis2 on Marcaje3.nis = Usuario2_ConaccNis2.nis and fechaHora between fechaAct and 
		                                CASE WHEN DATEADD(day, DATEDIFF(day, 0, fechaAct), 0) = DATEADD(day, DATEDIFF(day, 0, fechaAnu), 0) and estaAnulado = 0 
		                                     THEN getdate() 
			                                 ELSE fechaAnu END
		                left outer join Usuario2 on Usuario2_ConaccNis2.idusr = Usuario2.idUsr
		           WHERE Marcaje3.error = 0 and 
	                     (CASE WHEN len(@timeframe) = 10 then Convert(Varchar(20),Marcaje3.fechahora,104) ELSE @timeframe end) = @timeframe and
	                     (CASE WHEN left(@timeframe,1) = 'm' THEN cast(right(@timeframe,2) as int) ELSE month(Marcaje3.fechaHora) END) = month(Marcaje3.fechaHora) and
	                     (CASE WHEN left(@timeframe,1) = 'w' THEN dbo.fnISOWeek(Marcaje3.fechaHora) ELSE cast(right(@timeframe,2) as int) END) = cast(right(@timeframe,2) as int) and
	                     (CASE WHEN len(@timeframe) <> 10 THEN cast(left(right(@timeframe,6),4) as int) ELSE year(Marcaje3.fechaHora) END) = year(Marcaje3.fechaHora) and 
	                     (Usuario2_ConaccNis2.idUsr = @Setnis or @Setnis = 1000000)
	               ),
                   OrderTime as 
                   (
                   SELECT *, ROW_NUMBER() OVER (PARTITION BY idUsr, DATEADD(day, DATEDIFF(day, 0, fechaHora), 0) ORDER BY fechahora) AS rn 
                   From marcajes
                   )
                   
                   SELECT CASE WHEN @checkbox1='1' THEN NULL ELSE nis END as nis, 
	   					  CASE WHEN @checkbox1='1' THEN NULL ELSE fechaHora END as fechaHora, 
	                      CASE WHEN @checkbox1='1' THEN NULL ELSE fechaFinal END as fechaFinal,
						  CASE WHEN @checkbox1='1' THEN NULL ELSE horario END as horario,
	                      CASE WHEN @checkbox1='1' THEN NULL ELSE idInci END as idInci, idUsr, Nombre, SUM(Minutes) as Minutes,
	                      SeccionDescrip, Seccion,
						  convert(varchar(15), sum(minutes) / 60 ) + ':' + REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (sum(minutes) % 60)))) + convert(varchar(2), (sum(minutes) % 60)) as minhoras

				   FROM (
                         SELECT *, 
                                (SELECT SUM(
											CASE WHEN horaFin < datepart(hh,fechaHora)*60+datepart(mi,fechaHora) OR horaIni > datepart(hh,fechaFinalCalc)*60 + datepart(mi,fechaFinalCalc) THEN 0 ELSE 
										    CASE WHEN horaFin < datepart(hh,fechaFinalCalc)*60+datepart(mi,fechaFinalCalc) THEN horaFin ELSE datepart(hh,fechaFinalCalc)*60 + datepart(mi,fechaFinalCalc) END -
										    CASE WHEN horaIni > datepart(hh,fechaHora)*60+datepart(mi,fechaHora) THEN horaIni ELSE datepart(hh,fechaHora)*60 + datepart(mi,fechaHora) END
										    END)
							     FROM (SELECT COALESCE(Horario_Especial.horaIni, COALESCE(HorarioTeorico3.horaIni, HorarioTeorico2.horaIni)) as horaIni,
						    		          COALESCE(Horario_Especial.horaFin, COALESCE(HorarioTeorico3.horaFin, HorarioTeorico2.horaFin)) as horaFin,
						    		          tot.fechaHora,
						    		          tot.fechaFinalCalc
  									   FROM Usuario2_Calendario
  									   			LEFT OUTER JOIN Horario_Especial on
						    		                	((Horario_Especial.idUsr = tot.idUsr and Horario_Especial.aplica = 0) or 
						    		                	(Horario_Especial.idUsr = tot.idSeccion and Horario_Especial.aplica = 1)) and               	
						    		                	(tot.fechaHora between Horario_Especial.FechaIni and Horario_Especial.FechaFin) and
						    		                	((datepart(dw,tot.fechaHora) <> 6 and Horario_Especial.sabado = 0) or Horario_Especial.sabado = 1) and
						    		                	((datepart(dw,tot.fechaHora) <> 7 and Horario_Especial.domingo = 0) or Horario_Especial.domingo = 1)				    		                	
  									   			LEFT OUTER JOIN Diario2 on 
  									   			        Diario2.idUsr = tot.idUsr and 
														DATEADD(day, DATEDIFF(day, 0, tot.fechaHora), 0) = DATEADD(day, DATEDIFF(day, 0, Diario2.fecha), 0) and 
  									   			        Horario_Especial.horaIni IS NULL 
  									   			LEFT OUTER JOIN HorarioTeorico as HorarioTeorico3 on 
  									   			        Diario2.iDHorario = HorarioTeorico3.idHorario and 
  									   			        Horario_Especial.horaIni IS NULL  									    									     									   
  									   			LEFT OUTER JOIN dbo.Calendario_has_Semanario ON 
  									   			        Usuario2_Calendario.idCalendario = dbo.Calendario_has_Semanario.idCalendario AND 
  									   			        Calendario_has_Semanario.dia = DAY(tot.fechaHora) AND 
  									   			        Calendario_has_Semanario.mes = MONTH(tot.fechaHora) and 
  									   			        Horario_Especial.horaIni IS NULL and 
  									   			        HorarioTeorico3.horaIni IS NULL
  									   			LEFT OUTER JOIN dbo.Semanario_has_Horario ON
  									   			        Calendario_has_Semanario.idSemanario = dbo.Semanario_has_Horario.idSemanario AND 
  									   			        DATEPART(dw, tot.fechaHora) - 1 = dbo.Semanario_has_Horario.diaSemana and 
  									   			        Horario_Especial.horaIni IS NULL and 
  									   			        HorarioTeorico3.horaIni IS NULL
  									   			LEFT OUTER JOIN dbo.HorarioTeorico AS HorarioTeorico2 ON
  									   			        Semanario_has_Horario.idHorario = HorarioTeorico2.idHorario and
  									   			        Horario_Especial.horaIni IS NULL and 
  									   			        HorarioTeorico3.horaIni IS NULL
   									   WHERE  dbo.Usuario2_Calendario.idUsr = tot.idUsr
							           )Cal) as Minutes,
						        (SELECT stuff((select ' ' + '<a href=Calendario_horario.aspx?fecha='+ Convert(Varchar(20), tot.FechaHora,104) +'&idUsr='+ CAST(tot.idUsr as nvarchar) +' target=#34;main#34; style=#34;text-decoration: none#34; >' + COALESCE(Horario_Especial.Marker,'') + CAST(COALESCE(Horario_Especial.horaIni,COALESCE(HorarioTeorico3.horaIni, HorarioTeorico2.horaIni)) / 60 AS nvarchar)
								      + ':' + LEFT(CAST(COALESCE(Horario_Especial.horaIni, COALESCE(HorarioTeorico3.horaIni, HorarioTeorico2.horaIni)) - 60 * (COALESCE(Horario_Especial.horaIni, COALESCE(HorarioTeorico3.horaIni, HorarioTeorico2.horaIni)) / 60) AS nvarchar) + '0', 2) + ' - ' + CAST(COALESCE(Horario_Especial.horaFin, COALESCE(HorarioTeorico3.horaFin, HorarioTeorico2.horaFin)) / 60 AS nvarchar) 
								      + ':' + LEFT(CAST(COALESCE(Horario_Especial.horaFin, COALESCE(HorarioTeorico3.horaFin, HorarioTeorico2.horaFin)) - 60 * (COALESCE(Horario_Especial.horaFin, COALESCE(HorarioTeorico3.horaFin, HorarioTeorico2.horaFin)) / 60) AS nvarchar) + '0', 2) +'</a> '

  									   FROM Usuario2_Calendario
  									   			LEFT OUTER JOIN Horario_Especial on
						    		                	((Horario_Especial.idUsr = tot.idUsr and Horario_Especial.aplica = 0) or 
						    		                	(Horario_Especial.idUsr = tot.idSeccion and Horario_Especial.aplica = 1)) and               	
						    		                	(fechaHora between Horario_Especial.FechaIni and Horario_Especial.FechaFin) and
						    		                	((datepart(dw,FechaHora) <> 6 and Horario_Especial.sabado = 0) or Horario_Especial.sabado = 1) and
						    		                	((datepart(dw,FechaHora) <> 7 and Horario_Especial.domingo = 0) or Horario_Especial.domingo = 1)				    		                	
  									   			LEFT OUTER JOIN Diario2 on 
  									   			        Diario2.idUsr = tot.idUsr and 
  									   			        Convert(Varchar(20),FechaHora,104) = Convert(Varchar(20),Diario2.fecha,104) and 
  									   			        Horario_Especial.horaIni IS NULL 
  									   			LEFT OUTER JOIN HorarioTeorico as HorarioTeorico3 on 
  									   			        Diario2.idHorario = HorarioTeorico3.idHorario and 
  									   			        Horario_Especial.horaIni IS NULL  									    									     									   
  									   			LEFT OUTER JOIN dbo.Calendario_has_Semanario ON 
  									   			        Usuario2_Calendario.idCalendario = dbo.Calendario_has_Semanario.idCalendario AND 
  									   			        Calendario_has_Semanario.dia = DAY(fechaHora) AND 
  									   			        Calendario_has_Semanario.mes = MONTH(fechaHora) and 
  									   			        Horario_Especial.horaIni IS NULL and 
  									   			        HorarioTeorico3.horaIni IS NULL
  									   			LEFT OUTER JOIN dbo.Semanario_has_Horario ON
  									   			        Calendario_has_Semanario.idSemanario = dbo.Semanario_has_Horario.idSemanario AND 
  									   			        DATEPART(dw, fechaHora) - 1 = dbo.Semanario_has_Horario.diaSemana and 
  									   			        Horario_Especial.horaIni IS NULL and 
  									   			        HorarioTeorico3.horaIni IS NULL
  									   			LEFT OUTER JOIN dbo.HorarioTeorico AS HorarioTeorico2 ON
  									   			        Semanario_has_Horario.idHorario = HorarioTeorico2.idHorario and
  									   			        Horario_Especial.horaIni IS NULL and 
  									   			        HorarioTeorico3.horaIni IS NULL
  									   			        
									WHERE  dbo.Usuario2_Calendario.idUsr = tot.idUsr
									ORDER BY COALESCE(HorarioTeorico2.horaIni,Horario_Especial.horaIni)
       								for  xml path(''),type).value('.', 'nvarchar(max)'), 1, 1, '')) as horario        
									    
                         FROM (
                               SELECT T1.*, T2.fechaHora as fechaFinal, 
                                      CASE WHEN DATEADD(day, DATEDIFF(day, 0, T1.fechaHora), 0) = DATEADD(day, DATEDIFF(day, 0, COALESCE(T2.fechaHora, getdate())), 0) and T1.idInci <> 2 and T1.idInci <> 9
	                                       THEN COALESCE (T2.fechaHora, CASE WHEN T1.rn = 1 or T1.idInci <> 0 THEN getdate() ELSE T1.fechaHora END)
			                               ELSE T1.fechaHora
			                               END as fechaFinalCalc,
										   COALESCE ((SELECT descrip from Seccion WHERE IDSeccion = T1.Seccion), (SELECT descrip from Incidencia WHERE Incidencia.idInci = T1.idInci)) as SeccionDescrip
			                   FROM OrderTime T1 
			                             left outer Join OrderTime T2 on DATEADD(day, DATEDIFF(day, 0, T1.fechaHora), 0) = DATEADD(day, DATEDIFF(day, 0, T2.fechaHora), 0) and 
	                                                                                                            T1.idUsr = T2.IdUsr and 
														                                                           T1.rn = T2.rn - 1
		                       WHERE (T2.fechaHora IS NOT NULL OR (T2.fechahora IS NULL and T1.idInci <> 0) OR (T2.fechahora IS NULL and T1.idInci = 0 and T1.rn = 1)) 
								)Tot
					    )SUM
						GROUP BY CASE WHEN @checkbox1='1' THEN NULL ELSE nis END,
							     CASE WHEN @checkbox1='1' THEN NULL ELSE fechaHora END,
							     CASE WHEN @checkbox1='1' THEN NULL ELSE fechaFinal END,
							     CASE WHEN @checkbox1='1' THEN NULL ELSE idInci END,
								 CASE WHEN @checkbox1='1' THEN NULL ELSE horario END,
							     Seccion,
								 SeccionDescrip,
								 idUsr, Nombre
						ORDER by fechaHora desc, Minutes desc

USE CONACC_BD;
GO
EXEC sp_helpindex 'Usuario2_ConaccNis2'
GO

index_name                         index_description                                      index_keys
------------------------------     -------------------------------------------------      -----------------------------------
PK__Usuario2_ConaccN__0DCF0841     clustered, unique, primary key located on PRIMARY      idCli, idInst, idUsr, nis, fechaAct

          left outer join Usuario2 on Usuario2_ConaccNis2.idusr = Usuario2.idUsr

		           WHERE Marcaje3.error = 0 and 
	                     (CASE WHEN len(@timeframe) = 10 then Convert(Varchar(20),Marcaje3.fechahora,104) ELSE @timeframe end) = @timeframe and
	                     (CASE WHEN left(@timeframe,1) = 'm' THEN cast(right(@timeframe,2) as int) ELSE month(Marcaje3.fechaHora) END) = month(Marcaje3.fechaHora) and
	                     (CASE WHEN left(@timeframe,1) = 'w' THEN dbo.fnISOWeek(Marcaje3.fechaHora) ELSE cast(right(@timeframe,2) as int) END) = cast(right(@timeframe,2) as int) and
	                     (CASE WHEN len(@timeframe) <> 10 THEN cast(left(right(@timeframe,6),4) as int) ELSE year(Marcaje3.fechaHora) END) = year(Marcaje3.fechaHora) and 
	                     (Usuario2_ConaccNis2.idUsr = @Setnis or @Setnis = 1000000)

CASE WHEN left(@timeframe,1) = 'm' 

CASE WHEN @timeframe_TYPE = 'm' 

cast(right(@timeframe,2) as int)

@int_timeframe_VALUE

WHERE Marcaje3.error = 0 and Month(Marcaje3.fechaHora)= 1 and year(Marcaje3.fechaHora) = 2015 and
	                     (Usuario2_ConaccNis2.idUsr = @Setnis)

WHERE Marcaje3.error = 0 and Month(Marcaje3.fechaHora)= 1 and year(Marcaje3.fechaHora) = 2015 and
	                     (Usuario2_ConaccNis2.idUsr = @Setnis or @Setnis = 1000000)

WHERE Marcaje3.error = 0 and Marcaje3.fechaHora >= '20150101' AND Marcaje3.fechaHora < '20150201' AND
	                     (Usuario2_ConaccNis2.idUsr = @Setnis)

SELECT ... FROM ... WHERE MyColumn LIKE 'FOOBAR%'
or
SELECT ... FROM ... WHERE MyColumn > 10000 AND MyColumn < 20000

SELECT ... FROM ... WHERE MyKey1 = 'XXX' AND MyColumn LIKE 'FOOBAR%'
or
SELECT ... FROM ... WHERE MyKey1 = 'XXX' AND MyColumn > 10000 AND MyColumn < 20000

quote:

---

... and I have to admit that I'm still kind of proud of the complex WHERE clause, as I couldn't think of anything more compact to search for records in a timeframe, that is defined by one single input parameter which can specify day, week or month

---