SQL - NO FILTRA BIEN MIS DATOS

SELECT A.FECHAMENOS1, A.HOLEID,A.SHIFT,

(SELECT VALUE FROM HOLEDETAILS WHERE HOLEID = A.HOLEID AND NAME = 'Nombre_Propuesto') AS PROPUESTO,

(SELECT VALUE FROM HOLEDETAILS WHERE HOLEID = A.HOLEID AND NAME = 'Campana') AS Campaña,

(SELECT TOP 1 CONTRACTCODE FROM DRILLINGDETAIL DD WHERE DD.HOLEID = A.HOLEID ) AS EMPRESA,

(SELECT TOP 1 VALUE FROM DRILLINGDETAIL DD

WHERE DD.HOLEID = A.HOLEID AND

DD.NAME = 'NumeroEquipo' )

AS MAQUINA,

MAx(CASE WHEN left(A.SHIFT,1) = 'A' THEN A.TURNO_A ELSE A.TURNO_B END) AS MTS,

-- HORAS_DEMORA

SUM(CASE WHEN

DETALLE = 'Colación' OR

DETALLE = 'Reunion Empresa de perforacion'

THEN TIEMPO

ELSE 0.0 END)

AS H_DEM,

-- HORAS_MANTENCION

SUM(CASE WHEN

DETALLE = 'Reparación de Equipo' OR

DETALLE = 'Espera de Combustible'

THEN TIEMPO

ELSE 0.0 END)

AS H_MANT

FROM

(

SELECT TT.SHIFT,TT.DRILLINGDATE,TT.Hora_Termino, cast(TT.DRILLINGDATE as DATE) AS DIA,TT.Detalle As DETALLE, TT.NumeroEquipo,TT.CONTRACTCODE,

(SELECT SUM(CAST(ProfFinal AS REAL)-CAST(ProfInicial AS REAL)) FROM DETALLE_PERFORA WHERE HOLEID = TT.HOLEID AND CAST(DRILLINGDATE AS DATE) = CAST(TT.DRILLINGDATE AS DATE) AND LEFT(SHIFT,1) = 'A' )AS TURNO_A,

(SELECT SUM(CAST(ProfFinal AS REAL)-CAST(ProfInicial AS REAL)) FROM DETALLE_PERFORA WHERE HOLEID = TT.HOLEID AND CAST(DRILLINGDATE AS DATE) = CAST(TT.DRILLINGDATE AS DATE) AND LEFT(SHIFT,1) = 'B' )AS TURNO_B,

(cast(DATEDIFF(SECOND,TT.DRILLINGDATE,TT.Hora_Termino)as real)/60/60) AS TIEMPO, TT.HOLEID, TT.SHIFT AS TURNO,

CASE WHEN CAST(DATEPART(HOUR,(TT.DRILLINGDATE)) AS INT) < 8 THEN CONVERT(CHAR(11),(CAST(TT.DRILLINGDATE AS DATETIME ) - 1), 105)

ELSE CONVERT(char(11), (cast(cast(MONTH(TT.DRILLINGDATE) as varchar) + '/' + cast(DAY(TT.DRILLINGDATE) as varchar)+ '/' + cast(YEAR(TT.DRILLINGDATE)as varchar) as datetime)),105) END as FECHAMENOS1,

convert(char(11),cast(TT.DRILLINGDATE as datetime),105) as DRILLINGDATE2

FROM DETALLE_PERFORA TT

WHERE TT.PROJECTCODE = 'SpenceDH' AND

TT.COSTCODE = 'Detalle_Perfora' AND

TT.NumeroEquipo = 'RC-018' AND

TT.DRILLINGDATE >= cast('01-Jun-2014 08:00:00' as datetime) AND

TT.DRILLINGDATE < cast('30-jun-2014 08:00:00' as datetime)

)A

GROUP BY NumeroEquipo, FECHAMENOS1,HOLEID, SHIFT

ORDER BY FECHAMENOS1, SHIFT