View most current sql server 2008 record

Asked

Viewed 56 times

0

I am trying to get the only last record (last sale) of each customer and I am not succeeding. I tried several ways, but nothing.. Only returns all sales from the date. Follow my query. I appreciate the help.

use banco
select m.cli_forn,c.nome,c.telefone,c.fax,m.num_nf,max(convert(date,m.dt_mov))as Data,m.valor_mov,m.cod_vendedor
from tb_movimentos m inner join tb_cliente c on(c.cod_cli=m.cli_forn)

where m.cod_vendedor = '178' and c.cod_cli !='5000' and status_clifor = 'C'and m.dt_mov >='2015-01-01' 
group by  m.cli_forn,c.nome,c.telefone,c.fax,m.num_nf,m.valor_mov,m.cod_vendedor

inserir a descrição da imagem aqui

  • something in group by is not grouping as hold, can put an example of the data that returns?

  • How the column is declared dt_mov? Can there be more than one sale to the same customer on the same day? The column status_clifor is which table?

1 answer

1

Here’s a classic solution:

-- código #1
USE banco;

with UltMov as (
SELECT *,
       seq= row_number() over (partition by cli_forn order by dt_mov desc)
  from tb_movimentos
  where status_clifor = 'C' 
        and dt_mov >= '20150101'
)  
SELECT C.cod_cli, C.nome, C.telefone, C.fax, 
       M.num_nf, M.dt_mov, M.valor_mov, M.cod_vendedor
  from tb_cliente as C
       inner join UltMov as M on M.cli_forn = C.cod_cli
  where M.seq = 1;

I did not test; there may be some error.

  • I only took the and from the first Where and Filtrei per seller. PREFECT!! Thank you!!!

  • Oops! There was an "and" lost there...

Browser other questions tagged

You are not signed in. Login or sign up in order to post.