asked on

SQL query Tweak

Hi,

Many thanks for taking a look at my question.

Given the below SQL query that works

;with cte as
(
      select tr.tr_rowid_debtor as lp_rowdebtor_id, tr.tr_posted_date,
      (0-tr.tr_to_agency+tr.tr_to_client) as amount,tr.tr_account,
      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 = 16
)
Select d.de_number, lp_rowdebtor_id, tr_posted_date, amount, tr_account 
from cte 
inner join debtor as d on d.de_rowid  = lp_rowdebtor_id
where idx = 1

Open in new window

I need it to do exactly what it does above but also - 1 additional item I need, is that somehow I need a count of the each transaction it finds per by "tr.tr_rowid_debtor" and only give back a result for the files that have multiple instances of where tr.tr_account = 16.

many thanks!!

Raja Jegan R

Hope the cnt column is what you are looking for.. If need any modifications kindly provide some sample result set and expected one to understand your requirement better.

;with cte as
(
      select tr.tr_rowid_debtor as lp_rowdebtor_id, tr.tr_posted_date,
      (0-tr.tr_to_agency+tr.tr_to_client) as amount,tr.tr_account,
      ROW_NUMBER() over (partition by tr.tr_rowid_debtor order by tr.tr_posted_date desc) idx,
	  COUNT(*) OVER (PARTITION BY tr.tr_rowid_debtor) cnt
      from de_transaction as tr
    where tr.tr_account = 16
)
Select d.de_number, lp_rowdebtor_id, tr_posted_date, amount, tr_account, cnt
from cte 
inner join debtor as d on d.de_rowid  = lp_rowdebtor_id
where idx = 1

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James Murphy

ASKER

Thank you - this worked perfectly!