View most current record sql server 2008

Navigation
0

I am trying to fetch only the last record (last sale) from each client and I am not succeeding. I've tried it in many ways, but nothing .. Just returns all sales from the date. Follow my query. Thanks for 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

    
asked by anonymous 19.09.2018 / 21:45

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; may have some error.

    
19.09.2018 / 22:18
SELECT with Join Join MYSQL View with two or more unbound tables, with different columns and that need to be merged