SUM, MAX, while joining 2 tables

1

Hi all, I greatly need your help with something. I'm trying to produce a list of all unique id's from a table called 'checkhis', with a summation of amount in table 'checkhi2' where code = 'D', for the max(date) recorded in the first table. i.e. select id, sum(amt), max(date)

The joining field is check_no.

This data is in 2 tables like such:

checkhis:

id iss_date check_no
612919 2020-02-19 00:00:00.000 10
612919 2020-02-05 00:00.00.000 20
612919 2020-01-10 00:00:00.000 30
613294 2020-02-19 00:00:00.000 50
613294 2020-02-05 00:00:00.000 60
613294 2020-02-05 00:00:00.000 60
614298 2020-02-19 00:00:00.000 30
614298 2020-02-05 00:00:00.000 25
614298 2020-01-10 00:00:00.000 70

checkhi2:

id code amt check_no
612919 D 15.00 10
612919 D 23.00 10
612919 P 333.00 10
612919 P 11.00 30
613294 P 22.00 50
613294 D 15.00 50
613294 P 98.00 50
613294 D 11.00 60
614298 D 55.00 30
614298 D 44.00 30
614298 D 11.00 30

This is the query I'm playing around with, but it's not working.

SELECT a.id, sum(b.amt),r.maxdate
from (
select id, max(date) as maxdate
from checkhis
group by id) r
inner join checkhis a
on a.id = r.id and a.date = r.maxdate
inner join checkhi2 b on a.id = b.id
where b.code = 'D'
group by a.id

Thanks so much for any help you can provide.

2

hi hope this helps ..

drop create sample data 
drop table checkhis

create table checkhis
(
id int  , iss_date datetime  , check_no int
)

set dateformat ymd
insert into checkhis select 612919,'2020-02-19',10
insert into checkhis select 612919,'2020-02-05',20
insert into checkhis select 612919,'2020-01-10',30
insert into checkhis select 613294,'2020-02-19',50
insert into checkhis select 613294,'2020-02-05',60
insert into checkhis select 613294,'2020-02-05',60
insert into checkhis select 614298,'2020-02-19',30
insert into checkhis select 614298,'2020-02-05',25
insert into checkhis select 614298,'2020-01-10',70

drop table checkhi2

create table checkhi2
(
id int , code varchar(1), amt float,check_no int 
)

insert into checkhi2 select 612919,'D',15.00 ,10
insert into checkhi2 select 612919,'D',23.00 ,10
insert into checkhi2 select 612919,'P',333.00,10
insert into checkhi2 select 612919,'P',11.00,30
insert into checkhi2 select 613294,'P',22.00,50
insert into checkhi2 select 613294,'D',15.00,50
insert into checkhi2 select 613294,'P',98.00,50
insert into checkhi2 select 613294,'D',11.00,60
insert into checkhi2 select 614298,'D',55.00,30
insert into checkhi2 select 614298,'D',44.00,30
insert into checkhi2 select 614298,'D',11.00,30

select * from checkhis
select * from checkhi2

; with cte_maxdate as
(
	select *,ROW_NUMBER() over(partition by id order by iss_date desc ) as rn  from checkhis
) , cte_sumamt as 
( 
     select id,sum(amt) as sumamt from checkhi2 where code = 'D' group by id 
) 
	select 
	      a.*,b.sumamt 
    from 
	    cte_maxdate a join cte_sumamt b on a.id = b.id
	where a.rn = 1

image