tgsistemas |
04-01-2005 20:31:57 |
Donde esta el error ??
Saludos a todos/as :
Estoy un poco liado con la siguiente consulta :confused:
Código:
SELECT TODO.*, (TODO.H1 + TODO.H2 + TODO.H3 + TODO.H4 + TODO.H5 + TODO.H6 + TODO.H7 + TODO.H8 + TODO.H9 + TODO.H10 +
TODO.H11 + TODO.H12 + TODO.H13 + TODO.H14 + TODO.H15 + TODO.H16 + TODO.H17 + TODO.H18 + TODO.H19 + TODO.H20 +
TODO.H21 + TODO.H22 + TODO.H23 + TODO.H24 + TODO.H25 + TODO.H26 + TODO.H27 + TODO.H28 + TODO.H29 + TODO.H30 +
TODO.H31) AS SUMAHOR,
(TODO.C1 + TODO.C2 + TODO.C3 + TODO.C4 + TODO.C5 + TODO.C6 + TODO.C7 + TODO.C8 + TODO.C9 + TODO.C10 +
TODO.C11 + TODO.C12 + TODO.C13 + TODO.C14 + TODO.C15 + TODO.C16 + TODO.C17 + TODO.C18 + TODO.C19 + TODO.C20 +
TODO.C21 + TODO.C22 + TODO.C23 + TODO.C24 + TODO.C25 + TODO.C26 + TODO.C27 + TODO.C28 + TODO.C29 + TODO.C30 +
TODO.C31) AS SUMACON,
(TODO.V1 + TODO.V2 + TODO.V3 + TODO.V4 + TODO.V5 + TODO.V6 + TODO.V7 + TODO.V8 + TODO.V9 + TODO.V10 +
TODO.V11 + TODO.V12 + TODO.V13 + TODO.V14 + TODO.V15 + TODO.V16 + TODO.V17 + TODO.V18 + TODO.V19 + TODO.V20 +
TODO.V21 + TODO.V22 + TODO.V23 + TODO.V24 + TODO.V25 + TODO.V26 + TODO.V27 + TODO.V28 + TODO.V29 + TODO.V30 +
TODO.V31) AS SUMAVEN
FROM (SELECT DSCRPCIONORDEN,
SUM(CASE DAY(T.FECHA) WHEN 01 THEN HT ELSE 0 END) AS H1,
SUM(CASE DAY(T.FECHA) WHEN 02 THEN HT ELSE 0 END) AS H2,
SUM(CASE DAY(T.FECHA) WHEN 03 THEN HT ELSE 0 END) AS H3,
SUM(CASE DAY(T.FECHA) WHEN 04 THEN HT ELSE 0 END) AS H4,
SUM(CASE DAY(T.FECHA) WHEN 05 THEN HT ELSE 0 END) AS H5,
SUM(CASE DAY(T.FECHA) WHEN 06 THEN HT ELSE 0 END) AS H6,
SUM(CASE DAY(T.FECHA) WHEN 07 THEN HT ELSE 0 END) AS H7,
SUM(CASE DAY(T.FECHA) WHEN 08 THEN HT ELSE 0 END) AS H8,
SUM(CASE DAY(T.FECHA) WHEN 09 THEN HT ELSE 0 END) AS H9,
SUM(CASE DAY(T.FECHA) WHEN 10 THEN HT ELSE 0 END) AS H10,
SUM(CASE DAY(T.FECHA) WHEN 11 THEN HT ELSE 0 END) AS H11,
SUM(CASE DAY(T.FECHA) WHEN 12 THEN HT ELSE 0 END) AS H12,
SUM(CASE DAY(T.FECHA) WHEN 13 THEN HT ELSE 0 END) AS H13,
SUM(CASE DAY(T.FECHA) WHEN 14 THEN HT ELSE 0 END) AS H14,
SUM(CASE DAY(T.FECHA) WHEN 15 THEN HT ELSE 0 END) AS H15,
SUM(CASE DAY(T.FECHA) WHEN 16 THEN HT ELSE 0 END) AS H16,
SUM(CASE DAY(T.FECHA) WHEN 17 THEN HT ELSE 0 END) AS H17,
SUM(CASE DAY(T.FECHA) WHEN 18 THEN HT ELSE 0 END) AS H18,
SUM(CASE DAY(T.FECHA) WHEN 19 THEN HT ELSE 0 END) AS H19,
SUM(CASE DAY(T.FECHA) WHEN 20 THEN HT ELSE 0 END) AS H20,
SUM(CASE DAY(T.FECHA) WHEN 21 THEN HT ELSE 0 END) AS H21,
SUM(CASE DAY(T.FECHA) WHEN 22 THEN HT ELSE 0 END) AS H22,
SUM(CASE DAY(T.FECHA) WHEN 23 THEN HT ELSE 0 END) AS H23,
SUM(CASE DAY(T.FECHA) WHEN 24 THEN HT ELSE 0 END) AS H24,
SUM(CASE DAY(T.FECHA) WHEN 25 THEN HT ELSE 0 END) AS H25,
SUM(CASE DAY(T.FECHA) WHEN 26 THEN HT ELSE 0 END) AS H26,
SUM(CASE DAY(T.FECHA) WHEN 27 THEN HT ELSE 0 END) AS H27,
SUM(CASE DAY(T.FECHA) WHEN 28 THEN HT ELSE 0 END) AS H28,
SUM(CASE DAY(T.FECHA) WHEN 29 THEN HT ELSE 0 END) AS H29,
SUM(CASE DAY(T.FECHA) WHEN 30 THEN HT ELSE 0 END) AS H30,
SUM(CASE DAY(T.FECHA) WHEN 31 THEN HT ELSE 0 END) AS H31,
SUM(CASE DAY(T.FECHA) WHEN 01 THEN CNTCTOS ELSE 0 END) AS C1,
SUM(CASE DAY(T.FECHA) WHEN 02 THEN CNTCTOS ELSE 0 END) AS C2,
SUM(CASE DAY(T.FECHA) WHEN 03 THEN CNTCTOS ELSE 0 END) AS C3,
SUM(CASE DAY(T.FECHA) WHEN 04 THEN CNTCTOS ELSE 0 END) AS C4,
SUM(CASE DAY(T.FECHA) WHEN 05 THEN CNTCTOS ELSE 0 END) AS C5,
SUM(CASE DAY(T.FECHA) WHEN 06 THEN CNTCTOS ELSE 0 END) AS C6,
SUM(CASE DAY(T.FECHA) WHEN 07 THEN CNTCTOS ELSE 0 END) AS C7,
SUM(CASE DAY(T.FECHA) WHEN 08 THEN CNTCTOS ELSE 0 END) AS C8,
SUM(CASE DAY(T.FECHA) WHEN 09 THEN CNTCTOS ELSE 0 END) AS C9,
SUM(CASE DAY(T.FECHA) WHEN 10 THEN CNTCTOS ELSE 0 END) AS C10,
SUM(CASE DAY(T.FECHA) WHEN 11 THEN CNTCTOS ELSE 0 END) AS C11,
SUM(CASE DAY(T.FECHA) WHEN 12 THEN CNTCTOS ELSE 0 END) AS C12,
SUM(CASE DAY(T.FECHA) WHEN 13 THEN CNTCTOS ELSE 0 END) AS C13,
SUM(CASE DAY(T.FECHA) WHEN 14 THEN CNTCTOS ELSE 0 END) AS C14,
SUM(CASE DAY(T.FECHA) WHEN 15 THEN CNTCTOS ELSE 0 END) AS C15,
SUM(CASE DAY(T.FECHA) WHEN 16 THEN CNTCTOS ELSE 0 END) AS C16,
SUM(CASE DAY(T.FECHA) WHEN 17 THEN CNTCTOS ELSE 0 END) AS C17,
SUM(CASE DAY(T.FECHA) WHEN 18 THEN CNTCTOS ELSE 0 END) AS C18,
SUM(CASE DAY(T.FECHA) WHEN 19 THEN CNTCTOS ELSE 0 END) AS C19,
SUM(CASE DAY(T.FECHA) WHEN 20 THEN CNTCTOS ELSE 0 END) AS C20,
SUM(CASE DAY(T.FECHA) WHEN 21 THEN CNTCTOS ELSE 0 END) AS C21,
SUM(CASE DAY(T.FECHA) WHEN 22 THEN CNTCTOS ELSE 0 END) AS C22,
SUM(CASE DAY(T.FECHA) WHEN 23 THEN CNTCTOS ELSE 0 END) AS C23,
SUM(CASE DAY(T.FECHA) WHEN 24 THEN CNTCTOS ELSE 0 END) AS C24,
SUM(CASE DAY(T.FECHA) WHEN 25 THEN CNTCTOS ELSE 0 END) AS C25,
SUM(CASE DAY(T.FECHA) WHEN 26 THEN CNTCTOS ELSE 0 END) AS C26,
SUM(CASE DAY(T.FECHA) WHEN 27 THEN CNTCTOS ELSE 0 END) AS C27,
SUM(CASE DAY(T.FECHA) WHEN 28 THEN CNTCTOS ELSE 0 END) AS C28,
SUM(CASE DAY(T.FECHA) WHEN 29 THEN CNTCTOS ELSE 0 END) AS C29,
SUM(CASE DAY(T.FECHA) WHEN 30 THEN CNTCTOS ELSE 0 END) AS C30,
SUM(CASE DAY(T.FECHA) WHEN 31 THEN CNTCTOS ELSE 0 END) AS C31,
SUM(CASE DAY(T.FECHA) WHEN 01 THEN VENTAS ELSE 0 END) AS V1,
SUM(CASE DAY(T.FECHA) WHEN 02 THEN VENTAS ELSE 0 END) AS V2,
SUM(CASE DAY(T.FECHA) WHEN 03 THEN VENTAS ELSE 0 END) AS V3,
SUM(CASE DAY(T.FECHA) WHEN 04 THEN VENTAS ELSE 0 END) AS V4,
SUM(CASE DAY(T.FECHA) WHEN 05 THEN VENTAS ELSE 0 END) AS V5,
SUM(CASE DAY(T.FECHA) WHEN 06 THEN VENTAS ELSE 0 END) AS V6,
SUM(CASE DAY(T.FECHA) WHEN 07 THEN VENTAS ELSE 0 END) AS V7,
SUM(CASE DAY(T.FECHA) WHEN 08 THEN VENTAS ELSE 0 END) AS V8,
SUM(CASE DAY(T.FECHA) WHEN 09 THEN VENTAS ELSE 0 END) AS V9,
SUM(CASE DAY(T.FECHA) WHEN 10 THEN VENTAS ELSE 0 END) AS V10,
SUM(CASE DAY(T.FECHA) WHEN 11 THEN VENTAS ELSE 0 END) AS V11,
SUM(CASE DAY(T.FECHA) WHEN 12 THEN VENTAS ELSE 0 END) AS V12,
SUM(CASE DAY(T.FECHA) WHEN 13 THEN VENTAS ELSE 0 END) AS V13,
SUM(CASE DAY(T.FECHA) WHEN 14 THEN VENTAS ELSE 0 END) AS V14,
SUM(CASE DAY(T.FECHA) WHEN 15 THEN VENTAS ELSE 0 END) AS V15,
SUM(CASE DAY(T.FECHA) WHEN 16 THEN VENTAS ELSE 0 END) AS V16,
SUM(CASE DAY(T.FECHA) WHEN 17 THEN VENTAS ELSE 0 END) AS V17,
SUM(CASE DAY(T.FECHA) WHEN 18 THEN VENTAS ELSE 0 END) AS V18,
SUM(CASE DAY(T.FECHA) WHEN 19 THEN VENTAS ELSE 0 END) AS V19,
SUM(CASE DAY(T.FECHA) WHEN 20 THEN VENTAS ELSE 0 END) AS V20,
SUM(CASE DAY(T.FECHA) WHEN 21 THEN VENTAS ELSE 0 END) AS V21,
SUM(CASE DAY(T.FECHA) WHEN 22 THEN VENTAS ELSE 0 END) AS V22,
SUM(CASE DAY(T.FECHA) WHEN 23 THEN VENTAS ELSE 0 END) AS V23,
SUM(CASE DAY(T.FECHA) WHEN 24 THEN VENTAS ELSE 0 END) AS V24,
SUM(CASE DAY(T.FECHA) WHEN 25 THEN VENTAS ELSE 0 END) AS V25,
SUM(CASE DAY(T.FECHA) WHEN 26 THEN VENTAS ELSE 0 END) AS V26,
SUM(CASE DAY(T.FECHA) WHEN 27 THEN VENTAS ELSE 0 END) AS V27,
SUM(CASE DAY(T.FECHA) WHEN 28 THEN VENTAS ELSE 0 END) AS V28,
SUM(CASE DAY(T.FECHA) WHEN 29 THEN VENTAS ELSE 0 END) AS V29,
SUM(CASE DAY(T.FECHA) WHEN 30 THEN VENTAS ELSE 0 END) AS V30,
SUM(CASE DAY(T.FECHA) WHEN 31 THEN VENTAS ELSE 0 END) AS V31
FROM
(
SELECT PARTES.CDGOTOP, PARTES.NMROORDEN, PARTES.FECHA, PARTESLIN.HT, PARTESLIN.CNTCTOS, PARTESLIN.VENTAS,
CMPNAS.DSCRPCIONORDEN, (PARTESLIN.CNTCTOS / PARTESLIN.HT) AS CH, (PARTESLIN.VENTAS / PARTESLIN.HT) AS VH
FROM PARTES INNER JOIN
PARTESLIN ON PARTES.CDGO = PARTESLIN.CDGOPARTE INNER JOIN
TPLGIASHORAS ON PARTESLIN.TPLGIA = TPLGIASHORAS.CDGO INNER JOIN
CMPNAS ON PARTES.NMROORDEN = CMPNAS.NMROORDEN
WHERE (PARTES.CDGOTOP = 121 AND
PARTES.FECHA BETWEEN '01/10/04' AND '31/10/04' AND TPLGIASHORAS.PREDINFPROD = 1)
) AS T
GROUP BY DSCRPCIONORDEN) AS TODO
ORDER BY DSCRPCIONORDEN
explico la consulta un poco, se trata de obtener una parrilla de datos (solo de consulta) donde vean los datos correspondientes a un solo trabajador (CDGOTOP), en diferentes servicios (DSCRPCIONORDEN) por tres conceptos (HT son Horas totales, CONTCTOS y VENTAS).
El problema es que por ejemplo el trabajador 121, el día 01/10/04 trabajó 7 horas y esta consulta muestra q trabajó 21 horas, creo que multiplica el primer valor por tres (supongo que son los 3 conceptos).
Alguien puede ayudarme a encontrar el error ???? :confused: :confused: ya que no tengo mucha experiencia con SQL y hasta ahora con vuestra ayuda he conseguido aprender mucho.
Trabajo con D5 y SQL 7.
Gracias y saludos a todos/as.
|