none
Sub-select RRS feed

  • Pergunta

  • Prezados, preciso melhorar essa Sql, pois a mesma me retorna a informação que preciso, porem fiz algumas gambiarras, vejam se podem me ajudar..

    CASE WHEN C.CODCCUSTO IS NOT NULL THEN C.CODCCUSTO + ' - ' +G1.NOME
    ELSE BJ.CODCCUSTO + ' - ' + G.NOME END AS 'OBRA$',
        OF1.IDOBJOF                             AS 'EQ',

    OF1.DATACOLETA,
    C.DATASAIDA  ,
     
     CASE
       WHEN OFTIPOOBJ.USAINDICADORUSO2 = 1 THEN
        OF1.VALORMEDIDOR2
       WHEN OFTIPOOBJ.USAINDICADORUSO2 = 0 THEN
        OF1.VALORMEDIDOR1
       ELSE
        0
      END AS INICIAL,

    (CONVERT(INT,isnull(OF2.HORA1,0),1))  AS 'HT',
    (CONVERT(INT,ISNULL(OF2.HORA ,0),1))  AS 'HD',
    (CONVERT(INT,ISNULL(OF2.HORA2 ,0),1)) AS 'HM',
    (CONVERT(INT,ISNULL(OF2.HORA3 ,0),1))  AS 'HO',
    (CONVERT (INT,ISNULL(OF2.DIA,0),1))    AS 'DIA',
     '0'  AS 'FINAL',
     (
      SELECT
       MAX(H2.VALORMEDIDOR1)
      FROM
       OFHISTINDICADOR H2
      WHERE
       OF1.IDOBJOF = H2.IDOBJOF
       AND OF1.CODCOLIGADA = H2.CODCOLIGADA
       AND H2.DATACOLETA < OF1.DATACOLETA
      ) AS FINAL1

     

    FROM OFHISTINDICADOR  AS OF1
     LEFT JOIN OFHISTINDICADORCOMPL AS OF2 ON
    OF2.IDHISTINDICADOR = OF1.IDHISTINDICADOR AND OF2.CODCOLIGADA = OF1.CODCOLIGADA
    AND OF2.IDOBJOF = OF1.IDOBJOF
    LEFT JOIN OFOBJOFICINA AS BJ ON BJ.CODCOLIGADA = OF1.CODCOLIGADA AND BJ.IDOBJOF = OF1.IDOBJOF
    LEFT JOIN OFOBJOFICINACCUSTO AS C ON C.CODCOLIGADA = OF1.CODCOLIGADA AND C.IDOBJOF = OF1.IDOBJOF
    AND OF1.DATACOLETA <= c.DATASAIDA
    LEFT JOIN GCCUSTO   AS G ON G.CODCOLIGADA = BJ.CODCOLIGADA AND G.CODCCUSTO = BJ.CODCCUSTO
    LEFT JOIN GCCUSTO   AS G1 ON G1.CODCOLIGADA = C.CODCOLIGADA AND G1.CODCCUSTO = C.CODCCUSTO
    LEFT JOIN OFTIPOOBJ ON OFTIPOOBJ.IDTIPOOBJ = BJ.IDTIPOOBJ
    WHERE OF1.DATACOLETA >= '2011-01-31' AND OF1.DATACOLETA < DateAdd(day, 1, '2011-02-28')

     AND OF1.IDOBJOF = 'AC-02'

    )
    H(OBRA$,EQ,DATACOLETA,DATASAIDA,INICIAL,HT,HD,HO,HM,DIA,FINAL,FINAL1)

    vejam essa parte

    (CONVERT (INT,ISNULL(OF2.DIA,0),1))    AS 'DIA',
     '0'  AS 'FINAL',
     (
      SELECT
       MAX(H2.VALORMEDIDOR1)
      FROM
       OFHISTINDICADOR H2
      WHERE
       OF1.IDOBJOF = H2.IDOBJOF
       AND OF1.CODCOLIGADA = H2.CODCOLIGADA
       AND H2.DATACOLETA < OF1.DATACOLETA
      ) AS FINAL1

    eu preciso da informação DIA E O RESULTADO DO sUB-Select eu preciso que se chame Hora.

    nao estou conseguindo fazer, pois a cada vez que eu mexo complicada mais..

    Agradeço a todos pela ajuda..

    segunda-feira, 18 de abril de 2011 21:41

Respostas

  • Prezados resolvido..

    SELECT

    H

    .OBRA$,

    H

    .EQ,
     

    H

    .DATACOLETA,
     

    H

    .DATASAIDA,
     

    H

    .INICIAL,

    H

    .FINAL,

    H

    .INICIAL - H.FINAL AS 'HORA',

    H

    .KM,

    H

    .KMFINAL,

    H

    .KM - H.KMFINAL AS 'KM',

    H

    .HT,

    H

    .HD,

    H

    .HO,

    H

    .HM,

    H

    .DIA,

    H

    .FATURAR/*,

    H.INICIAL - H.FINAL1*/

    FROM

    (

    SELECT

    CASE

    WHEN C.CODCCUSTO IS NOT NULL THEN C.CODCCUSTO + ' - ' +G1.NOME

    ELSE

    BJ.CODCCUSTO + ' - ' + G.NOME END AS 'OBRA$'

    ,

    OF1

    .IDOBJOF AS 'EQ',

    OF1

    .DATACOLETA,

    C

    .DATASAIDA ,

     

     

    CASE

     

    WHEN OFTIPOOBJ.USAINDICADORUSO2 = 1 THEN

    OF1

    .VALORMEDIDOR1

     

    WHEN OFTIPOOBJ.USAINDICADORUSO2 = 0 THEN

    OF1

    .VALORMEDIDOR1

     

    ELSE

    0

     

    END AS INICIAL,

     

    (

     

    SELECT

     

    MAX(H2.VALORMEDIDOR1)

     

    FROM

    OFHISTINDICADOR H2

     

    WHERE

    OF1

    .IDOBJOF = H2.IDOBJOF

     

    AND OF1.CODCOLIGADA = H2.CODCOLIGADA

     

    AND H2.DATACOLETA < OF1.DATACOLETA

     

    ) AS FINAL1,

     

    CASE

     

    WHEN OFTIPOOBJ.USAINDICADORUSO2 = 1 THEN

    OF1

    .VALORMEDIDOR2

     

    WHEN OFTIPOOBJ.USAINDICADORUSO2 = 0 THEN

    OF1

    .VALORMEDIDOR2

     

    ELSE

    0

     

    END AS KM,

    (

     

    SELECT

     

    MAX(H2.VALORMEDIDOR2)

     

    FROM

    OFHISTINDICADOR H2

     

    WHERE

    OF1

    .IDOBJOF = H2.IDOBJOF

     

    AND OF1.CODCOLIGADA = H2.CODCOLIGADA

     

    AND H2.DATACOLETA < OF1.DATACOLETA

     

    ) AS KM_FINAL,

    (

    CONVERT(INT,isnull(OF2.HORA1,0),1)) AS 'HT'

    ,

    (

    CONVERT(INT,ISNULL(OF2.HORA ,0),1)) AS 'HD'

    ,

    (

    CONVERT(INT,ISNULL(OF2.HORA2 ,0),1)) AS 'HM',

    (

    CONVERT(INT,ISNULL(OF2.HORA3 ,0),1)) AS 'HO'

    ,

    (

    CONVERT(INT,ISNULL(OF2.DIA,0),1)) AS 'DIA'

    ,

    P

    .FATURAR

    FROM

    OFHISTINDICADOR AS OF1

     

    LEFT JOIN OFHISTINDICADORCOMPL AS OF2 ON

    OF2

    .IDHISTINDICADOR = OF1.IDHISTINDICADOR AND OF2.CODCOLIGADA = OF1.CODCOLIGADA

    AND

    OF2.IDOBJOF = OF1.IDOBJOF

    LEFT

    JOIN OFOBJOFICINA AS BJ ON BJ.CODCOLIGADA = OF1.CODCOLIGADA AND BJ.IDOBJOF = OF1.IDOBJOF

    LEFT

    JOIN OFOBJOFICINACCUSTO AS C ON C.CODCOLIGADA = OF1.CODCOLIGADA AND C.IDOBJOF = OF1.IDOBJOF

    AND

    OF1.DATACOLETA <= c.DATASAIDA

    LEFT

    JOIN GCCUSTO AS G ON G.CODCOLIGADA = BJ.CODCOLIGADA AND G.CODCCUSTO = BJ.CODCCUSTO

    LEFT

    JOIN GCCUSTO AS G1 ON G1.CODCOLIGADA = C.CODCOLIGADA AND G1.CODCCUSTO = C.CODCCUSTO

    LEFT

    JOIN OFTIPOOBJ ON OFTIPOOBJ.IDTIPOOBJ = BJ.IDTIPOOBJ

    LEFT

    JOIN OFOBJOFICINACOMPL AS P ON P.CODCOLIGADA = BJ.CODCOLIGADA AND P.IDOBJOF = BJ.IDOBJOF

    WHERE

    OF1.DATACOLETA >= '2011-01-31' AND OF1.DATACOLETA < DateAdd(day, 1, '2011-02-28'

    )

     

    AND OF1.IDOBJOF = 'AC-02'

    )

    H

    (OBRA$,EQ,DATACOLETA,DATASAIDA,INICIAL,FINAL,KM,KMFINAL,HT,HD,HO,HM,DIA,FATURAR)
    • Marcado como Resposta Ronnie Von terça-feira, 19 de abril de 2011 16:56
    terça-feira, 19 de abril de 2011 16:55