Simple SQL query to get the maximum date and associated amount

Hi,

I am trying to write a relatively simple query.

given this table of data:

row_id      tr_rowid_debtor      tr_account      tr_to_client      tr_to_agency      tr_posted_date      tr_payment_date
1      2      1      90            1/06/2018      1/06/2018
2      2      1            40      2/06/2018      2/06/2018
3      3      16            30      2/06/2018      2/06/2018
4      4      16            20      2/06/2018      2/06/2018
5      5      16            10      1/06/2018      1/06/2018
6      7      1            12      1/07/2018      1/07/2018
7      7      1      23            1/06/2018      1/06/2018
8      7      1      25            1/08/2018      1/08/2018
9      2      16            12      1/06/2018      1/06/2018
10      3      16            30      1/06/2018      1/06/2018
11      4      16            450      1/06/2018      1/06/2018
12      5      16            459      1/05/2018      1/05/2018
13      7      16            654      1/05/2018      1/05/2018

and this is my current query:

select tr.tr_rowid_debtor,max(tr.tr_posted_date)
from de_transaction as tr
where tr.tr_account = 1
group by tr.tr_rowid_debtor

i am trying to add this in as well:
 (tr.tr_to_agency+tr.tr_to_client) as amount,

what I am trying to achieve is for each tr_rowid_debtor I am trying to get the latest transaction - which is determined by the highest tr_posted_date  - and I want the associated amount as well.  Thus the  (tr.tr_to_agency+tr.tr_to_client) as amount.

Could someone please assist?

many thanks!!
James MurphyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try something like this:

;with cte as
(
	select tr.tr_rowid_debtor, tr.tr_posted_date,
	(tr.tr_to_agency+tr.tr_to_client) as amount,
	ROW_NUMBER() over (partition by tr.tr_account order by tr.tr_posted_date desc) idx
	from de_transaction as tr
	where tr.tr_account = 1
)
Select tr_rowid_debtor, tr_posted_date, amount from cte
where idx = 1

Open in new window

0
awking00Information Technology SpecialistCommented:
Can you post the expected results from your sample data? Also, it appears that some of the tr_to_agency amounts are null when the tr_to_client amounts are not and vice versa . Is that true?
0
James MurphyAuthor Commented:
Hi awking00,

Yes that is correct, only the tr_to_client or tr_to_agency will be filled not both.

this is what I would expect back, given the table originally mentioned.

tr_rowid_debtor      tr_posted_date      amount
7      1/08/2018      25
2      2/06/2018      40

many thanks!
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

James MurphyAuthor Commented:
Thank you Ryan, I will try playing with that now!

many thanks!!
0
James MurphyAuthor Commented:
Hi Ryan,

It is only give one row of results. - by rights I would expect a single row for every "tr_rowid_debtor"
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
It is only give one row of results. - by rights I would expect a single row for every "tr_rowid_debtor"

I guess you need this:

;with cte as
(
      select tr.tr_rowid_debtor, tr.tr_posted_date,
      (tr.tr_to_agency+tr.tr_to_client) as amount,
      ROW_NUMBER() over (partition by tr.tr_rowid_debtor order by tr.tr_posted_date desc) idx
      from de_transaction as tr
      where tr.tr_account = 1
)
Select tr_rowid_debtor, tr_posted_date, amount from cte
where idx = 1

see if that works?
0
James MurphyAuthor Commented:
that seems to work great - I have to dig into some data to check a few things as the results of the live data are giving me a few transactions where there is a date but a 0 amount for the transaction - I am just ensuring that this is correct in DB (can't see why there would be transactions with a 0 amount but it could be possible.

many thanks!
0
James MurphyAuthor Commented:
if I wanted to add a join to this request - can i do it on the same question - or do I need to create a new one?

the join is simply this:

The table name is: debtor

the column name to do the join between is debtor.de_rowid  = tr.tr_rowid_debtor

many thanks
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
the column name to do the join between is debtor.de_rowid  = tr.tr_rowid_debtor

yup of course, just like general case where we join the tables in the select statement, like:

;with cte as
(
      select tr.tr_rowid_debtor, tr.tr_posted_date,
      (tr.tr_to_agency+tr.tr_to_client) as amount,
      ROW_NUMBER() over (partition by tr.tr_rowid_debtor order by tr.tr_posted_date desc) idx
      from de_transaction as tr inner join debtor on debtor.de_rowid  = tr.tr_rowid_debtor
      where tr.tr_account = 1
)
Select tr_rowid_debtor, tr_posted_date, amount from cte
where idx = 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James MurphyAuthor Commented:
Thank you very much - your help is very much appreciated!! This site is awesome!!
0
awking00Information Technology SpecialistCommented:
tr.tr_to_agency+tr.tr_to_client will return null if one of the values is null. To prevent this -
coalesce(tr.tr_to_agency,0) +coalesce(tr.tr_to_client,0) as amount
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.