Prezados a Sql Abaixo, me retorna o resultado que preciso,
SQl
SELECT
x
.data,
X
.Chapa,
Entrada
=
(CASE
x.SEQ
WHEN 1
THEN X.CargaHoraria
ELSE
'0'
END),
[Saida almoço]
=
(CASE
x.SEQ
WHEN 2
THEN X.CargaHoraria
ELSE
'0'
END),
[Volta Almoço]
=
(CASE
x.SEQ
WHEN 3
THEN X.CargaHoraria
ELSE
'0'
END),
Saída
=
(CASE
x.SEQ
WHEN 4
THEN X.CargaHoraria
ELSE
'0'
END)
FROM
(
SELECT
f
.data,
P
.CHAPA,
A
.BATIDA,
A
.INDICE,
RIGHT('0'
+
CAST((F.BATIDA
/ 60)
As
VARCHAR(2)),2)
+
':'
+
RIGHT('0'
+
CAST((F.BATIDA
% 60)
As
VARCHAR(2)),2)
As CargaHoraria,
Seq
=
(SELECT
COUNT(*)+1
FROM PFUNC
AS P1
WITH(NOLOCK)
INNER
JOIN AHORARIO
AS H1
WITH(NOLOCK)
ON H1.CODCOLIGADA
= P1.CODCOLIGADA
AND H1.CODIGO
= P1.CODHORARIO
INNER
JOIN ABATHOR
AS A1
WITH(NOLOCK)
ON A1.CODCOLIGADA
= H1.CODCOLIGADA
AND H1.CODIGO
= A1.CODHORARIO
INNER
JOIN ABATFUN
AS F1
ON F1.CODCOLIGADA
=P.CODCOLIGADA
AND F1.CHAPA
= P.CHAPA
WHERE P1.CHAPA
= P.CHAPA
AND A1.INDICE
= A.INDICE
AND F.Batida
< F.Batida)
FROM PFUNC
AS P
INNER
JOIN AHORARIO
AS H
ON H.CODCOLIGADA
= P.CODCOLIGADA
AND H.CODIGO
= P.CODHORARIO
INNER
JOIN ABATHOR
AS A
ON A.CODCOLIGADA
= H.CODCOLIGADA
AND H.CODIGO
= A.CODHORARIO
INNER
JOIN ABATFUN
AS F
ON F.CODCOLIGADA
=P.CODCOLIGADA
AND F.CHAPA
= P.CHAPA
WHERE P.CHAPA
=
'009841'
AND F.DATA
>=
'2011-01-01'
AND A.CODHORARIO
=
'0001'
)
AS X
GROUP
BY X.Chapa,X.CargaHoraria,x.SEQ,x.data
ORDER
BY X.Chapa,x.data,X.ENTRADA
Resultado
Data Chapa Entrada Saida almoço Volta almoço saida
2011-01-03 00:00:00.000 009841 07:54 0 0 0
2011-01-03 00:00:00.000 009841 12:19 0 0 0
2011-01-03 00:00:00.000 009841 13:06 0 0 0
2011-01-03 00:00:00.000 009841 18:15 0 0 0
2011-01-04 00:00:00.000 009841 07:46 0 0 0
2011-01-04 00:00:00.000 009841 12:13 0 0 0
2011-01-04 00:00:00.000 009841 13:11 0 0 0
2011-01-04 00:00:00.000 009841 18:50 0 0 0
Porem ele esta me retorando me colunas preciso que me retorne em linhas..
agradeço a ajuda de todos
