Gente, estou com problema com uma consulta sql, onde deve listar os dias da semana com as batidas de determinado horário, porém quando o horário tem o sábado com apenas 2 batidas, a sentença lista somente de segunda a sexta, ignorando o sábado. Abaixo a sentença sql, peço ajuda, pois não consigo ver o erro!
Grato.
-----------
SELECT CASE a.indice
WHEN 1 THEN 'Segunda'
WHEN 2 THEN 'Terça'
WHEN 3 THEN 'Quarta'
WHEN 4 THEN 'Quinta'
WHEN 5 THEN 'Sexta'
WHEN 6 THEN 'Sábado'
WHEN 7 THEN 'Domingo'END,
ISNULL(a1.batida,'-'),
ISNULL(a2.batida,'-'),
ISNULL(a3.batida,'-'),
ISNULL(a4.batida,'-')
FROM abathor a
LEFT OUTER JOIN
( SELECT TOP 1 x.*
FROM
( SELECT TOP 1 b.codcoligada, b.batida, b.indice,b.codhorario
FROM abathor b
WHERE b.codcoligada=:espelho#2
AND b.codhorario=:espelho#1 and b.batida is not null and b.indice in (1,2,3,4,5,6)
ORDER BY indice,batida,natureza
) x
where x.batida is not null ORDER BY x.indice,x.batida
) a1 ON a1.codhorario = a.codhorario and a.batida=a1.batida and a1.batida is not null
LEFT OUTER JOIN
( SELECT TOP 1 x.*
FROM
( SELECT TOP 2 b.codcoligada, b.batida, b.indice,b.codhorario
FROM abathor b
WHERE b.codcoligada=:espelho#2
AND b.codhorario=:espelho#1 and b.batida is not null and b.indice in (1,2,3,4,5,6)
ORDER BY indice,batida,natureza
) x
where x.batida is not null ORDER BY x.indice,x.batida desc
) a2 ON a2.codhorario = a.codhorario and a1.batida<>a2.batida and a.batida<>a2.batida and a2.batida is not null
LEFT OUTER JOIN
( SELECT TOP 1 x.*
FROM
( SELECT TOP 3 b.codcoligada, b.batida, b.indice,b.codhorario
FROM abathor b
WHERE b.codcoligada=:espelho#2
AND b.codhorario=:espelho#1 and b.batida is not null and b.indice in (1,2,3,4,5,6)
ORDER BY indice,batida,natureza
) x
where x.batida is not null ORDER BY x.indice,x.batida desc
) a3 ON a3.codhorario = a.codhorario and a2.batida<>a3.batida and a1.batida<>a3.batida and a.batida<>a3.batida and a3.batida is not null
LEFT OUTER JOIN
( SELECT TOP 1 x.*
FROM
( SELECT TOP 4 b.codcoligada, b.batida, b.indice,b.codhorario
FROM abathor b
WHERE b.codcoligada=:espelho#2
AND b.codhorario=:espelho#1 and b.batida is not null and b.indice in (1,2,3,4,5,6)
ORDER BY indice,batida,natureza
) x
where x.batida>0 ORDER BY x.indice,x.batida desc
) a4 ON a4.codhorario = a3.codhorario and a3.batida<>a4.batida and a2.batida<>a4.batida and a1.batida<>a4.batida and a.batida<>a4.batida and a4.batida is not null
WHERE a.codhorario = :espelho#1 and a.codcoligada=:espelho#2 and a.indice in (1,2,3,4,5,6)
GROUP BY a.indice,a1.batida,a2.batida,a3.batida,a4.batida
HAVING a1.batida>0
---------------------------------