Para buscar los coincidentes con el día de hoy:
Select * From Compras Where CONVERT(varchar(10),fecha,103) = CONVERT(varchar(10),GETDATE(),103)
Agrupar por horas y empleados:
select NOMBRE , COUNT(CODPRENDA) AS TotalPrendas,
count(case when CONVERT(varchar(2),FECHA,108) = ’05’ then 1 else null end) as ’05’,
count(case when CONVERT(varchar(2),FECHA,108) = ’06’ then 1 else null end) as ’06’,
count(case when CONVERT(varchar(2),FECHA,108) = ’07’ then 1 else null end) as ’07’,
count(case when CONVERT(varchar(2),FECHA,108) = ’08’ then 1 else null end) as ’08’,
count(case when CONVERT(varchar(2),FECHA,108) = ’09’ then 1 else null end) as ’09’,
count(case when CONVERT(varchar(2),FECHA,108) = ’10’ then 1 else null end) as ’10’,
count(case when CONVERT(varchar(2),FECHA,108) = ’11’ then 1 else null end) as ’11’,
count(case when CONVERT(varchar(2),FECHA,108) = ’12’ then 1 else null end) as ’12’,
count(case when CONVERT(varchar(2),FECHA,108) = ’13’ then 1 else null end) as ’13’,
count(case when CONVERT(varchar(2),FECHA,108) = ’14’ then 1 else null end) as ’14’,
count(case when CONVERT(varchar(2),FECHA,108) = ’15’ then 1 else null end) as ’15’,
count(case when CONVERT(varchar(2),FECHA,108) = ’16’ then 1 else null end) as ’16’,
count(case when CONVERT(varchar(2),FECHA,108) = ’17’ then 1 else null end) as ’17’,
count(case when CONVERT(varchar(2),FECHA,108) = ’18’ then 1 else null end) as ’18’,
count(case when CONVERT(varchar(2),FECHA,108) = ’19’ then 1 else null end) as ’19’
FROM Lote LEFT JOIN Maestros.dbo.EMPLEADOS on CodEmple = Maestros.dbo.EMPLEADOS.cod
where CONVERT(varchar(10),fecha,103) = CONVERT(varchar(10),GETDATE(),103)
GROUP BY nombre