F�rum Duvida no select #388806

19/10/2010

Tenho o seguinte select
select
SW2030.W2_PO_DT,
SW3030.W3_PO_NUM,
SW3030.W3_COD_I,
SW3030.W3_QTDE,
SW3030.W3_PRECO,
SW3030.W3_QTDE * SW3030.W3_PRECO VLR_TOTAL
from SW3030
LEFT JOIN SW2030 on SW2030.W2_PO_NUM = SW3030.W3_PO_NUM
WHERE
SW3030.W3_NR_CONT <> 0
AND
SW3030.D_E_L_E_T_=''
ORDER BY SW3030.W3_COD_I

O resultado do select acima: W2_PO_DT  W3_PO_NUM W3_COD_I W3_QTDE W3_PRECO VLR_TOTAL
15/02/2010  BMM 179930     ALDB0.1FOO11BC 800 5,1 4080
15/03/2010  BMM 179940     ALDB0.1FOO11BC 850 5,1 4335
15/04/2010  BMM 179950     ALDB0.1FOO11BC 800 5,1 4080
15/05/2010  BMM 179960     ALDB0.1FOO11BC 850 5,1 4335
15/06/2010  BMM 179940     ALDB0.1FOO11OL 650 5,1 3315
15/07/2010  BMM 179930     ALDB0.1FOO11OL 650 5,1 3315 Gostaria de um select que o resultado saisse assim: W2_PO_DT  W3_PO_NUM W3_COD_I W3_QTDE W3_PRECO VLR_TOTAL
15/05/2010  BMM 179960     ALDB0.1FOO11BC 850 5,1 4335
15/07/2010  BMM 179930     ALDB0.1FOO11OL 650 5,1 3315 Sempre o ultimo da data W2_po_dt
Como eu posso fazer isso em um unico select

Evandro Abreu

Curtir t�pico

+ 0

Responder

Posts

19/10/2010

William Galleti

Ol� Evandro.
Se voc� quiser agrupar sem somar, basta usar o Group By. Ex:

SELECT SW2030.W2_PO_DT,       SW3030.W3_PO_NUM,       SW3030.W3_COD_I,       SW3030.W3_QTDE,       SW3030.W3_PRECO,       SW3030.W3_QTDE * SW3030.W3_PRECO VLR_TOTAL  FROM SW3030  LEFT JOIN SW2030 ON SW2030.W2_PO_NUM = SW3030.W3_PO_NUM WHERE SW3030.W3_NR_CONT <> 0   AND SW3030.D_E_L_E_T_ = '' GROUP BY SW2030.W2_PO_DT,          SW3030.W3_PO_NUM,          SW3030.W3_COD_I,          SW3030.W3_QTDE,          SW3030.W3_PRECO,          SW3030.W3_QTDE * SW3030.W3_PRECO ORDER BY SW3030.W3_COD_I

Espero ter ajudado!

Responder

Gostei + 0

19/10/2010

Leonardo Xavier

LEFT JOIN SW2030 on SW2030.W2_PO_NUM = SW3030.W3_PO_NUM
WHERE
SW3030.W3_NR_CONT <> 0
AND
SW3030.D_E_L_E_T_=''
ORDER BY SW3030.W3_COD_I order by W2_po_dt desc

Responder

Gostei + 0

19/10/2010

Wilson Junior

Teste assim

SELECT
  SW2030.W2_PO_DT,
  SW3030.W3_PO_NUM,
  SW3030.W3_COD_I,
  SW3030.W3_QTDE,
  SW3030.W3_PRECO,
  (SW3030.W3_QTDE * SW3030.W3_PRECO) AS VLR_TOTAL
FROM
  SW3030
    LEFT JOIN SW2030 ON (SW2030.W2_PO_NUM = SW3030.W3_PO_NUM)
WHERE
  SW3030.W3_NR_CONT <> 0
  AND SW3030.D_E_L_E_T_ = ''
  AND SW2030.W2_PO_DT =
    ( SELECT
        MAX(SW2030a.W2_PO_DT)
      FROM
        SW2030 SW2030a
      WHERE
        SW2030a.W2_PO_NUM = SW3030.W3_PO_NUM
    )
ORDER BY
  SW3030.W3_COD_I

Espero ter colaborado.

Responder

Gostei + 0

19/10/2010

Evandro Abreu

no exemplo que passou abaixo....esta aparecendo todos os registros. no exemplo que passei, gostaria que aparecesse assim: W2_PO_DT W3_PO_NUM W3_COD_I W3_QTDE W3_PRECO VLR_TOTAL
15/05/2010 BMM 179960 ALDB0.1FOO11BC 850 5,1 4335
15/07/2010 BMM 179930 ALDB0.1FOO11OL 650 5,1 3315

Responder

Gostei + 0

19/10/2010

Bruno Batista

Boa tarde,

tente da seguinte maneira.

http://www.activedelphi.com.br/forum/viewtopic.php?p=311127#311127

Responder

Gostei + 0

19/10/2010

Bruno Batista

select SW2030.W2_PO_DT,


       SW3030.W3_PO_NUM,


       SW3030.W3_COD_I,


       SW3030.W3_QTDE,


       SW3030.W3_PRECO,


       SW3030.W3_QTDE * SW3030.W3_PRECO VLR_TOTAL


  from SW3030


  LEFT JOIN SW2030 on SW2030.W2_PO_NUM = SW3030.W3_PO_NUM


 WHERE SW3030.W3_NR_CONT <> 0


   AND SW3030.D_E_L_E_T_=''


   and (SW2030.W2_PO_DT, SW3030.W3_COD_I) in (select Max(s.W2_PO_DT) as data,


                                                     s.W3_COD_I


                                                from SW3030 s


                                               group by s.W3_COD_I)


 ORDER BY SW3030.W3_COD_I

Boa tarde,

tente da seguinte maneira.

http://www.activedelphi.com.br/forum/viewtopic.php?p=311127#311127

Responder

Gostei + 0

19/10/2010

Evandro Abreu

Esta dando erro na seguinte linha and (SW2030.W2_PO_DT, SW3030.W3_COD_I) in (select Max(s.W2_PO_DT) as data, s.W3_COD_I from SW3030 s group by s.W3_COD_I) Msg 102, Level 15, State 1, Line 11 Incorrect syntax near ','.

Responder

Gostei + 0

19/10/2010

Bruno Batista

Boa tarde,

verifica se funciona assim.

select SW2030.W2_PO_DT,


       SW3030.W3_PO_NUM,


       SW3030.W3_COD_I,


       SW3030.W3_QTDE,


       SW3030.W3_PRECO,


       SW3030.W3_QTDE * SW3030.W3_PRECO VLR_TOTAL


  from SW3030


  LEFT JOIN SW2030 on SW2030.W2_PO_NUM = SW3030.W3_PO_NUM


 WHERE SW3030.W3_NR_CONT <> 0


   AND SW3030.D_E_L_E_T_=''


   and (SW2030.W2_PO_DT, SW3030.W3_COD_I) in (select Max(s1.W2_PO_DT) as data,


                                                     s.W3_COD_I


                                                from SW3030 s

                                               LEFT JOIN SW2030 s1 on s1.W2_PO_NUM = s.W3_PO_NUM


                                               group by s.W3_COD_I)


 ORDER BY SW3030.W3_COD_I

Responder

Gostei + 0