I have the following query, selected for a single transaction id, to sum up the total of cost of sales from our accounting system. This query will need to sum up costs across all tranid's but I'm just selecting one particular tranid below.
The problem is for every transaction, I'm getting multiple transaction numbers as the system stores vendor bills that are deleted and replaced for a given transaction (or tran id).
- If I remove transaction_number and sum up the data, I get 45k here instead of 22k
- In other tranid's, there may be multiple Cost of Sales lines that need to be added but still only for a single VendBill which is the one with the greatest number for a given tranid.
- A single tranid can have 1 or more Vendbills, all ending in a number with the biggest numbered vendbill being the only one that should be counted for a given tranid (in this case, only vendbill34246 should be counted so that tranid 330167473 would equal 22,867 vs 45k when added up with all other tranids)
The original query is
select ift.tranid, ift.transaction_number, a.full_name, sum(tl.amount)
tranamt
from ns.tbill ift
inner join ns.transaction_lines tl on ift.transaction_id = tl.transaction_id
inner join ns.accounts a on tl.account_id = a.account_id
inner join ns.locations l on l.location_id = ift.location_id
where tranid = '330167473' and a.full_name like '%Cost of Sales%'
group by ift.tranid, l.full_name, ift.transaction_number
and the result I'm getting now is
tranid transaction_number full_name tranamt
330167473 VENDBILL34246 Cost of Sales 22867.92
330167473 VENDBILL34429 Cost of Sales 22867.92
The desired result would be as follows
330167473 VENDBILL34429 Cost of Sales 22867.92
I only want to return one line, but am not sure how to apply Max to transaction_number such that only one line is returned for a given tranid. As you can, it's currently duplicating the amount for this particular record and does so when adding up all tranid's for records that have multiple vendbill's.
ATTEMPTS I'VE MADE SO FAR
Applying MAX in the following way produces the error "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference." so it's unclear how to properly apply max in this case.
select ift.tranid, ift.transaction_number, a.full_name, sum(tl.amount)
tranamt
from ns.tbill ift
inner join ns.transaction_lines tl on ift.transaction_id =
tl.transaction_id
inner join ns.accounts a on tl.account_id = a.account_id
inner join ns.locations l on l.location_id = ift.location_id
where tranid = '330167473' and a.full_name like '%Cost of Sales%' and
ift.transaction_number >= (select max(ift.transaction_number) from
ns.tbill)
group by ift.tranid, a.full_name, ift.transaction_number
If I just add MAX to transaction_number itself, I get a single VENDBILL but the transaction amount is still 44k.
select ift.tranid, ift.transaction_number, a.full_name, sum(tl.amount) tranamt
from ns.tbill ift
inner join ns.transaction_lines tl on ift.transaction_id =
tl.transaction_id
inner join ns.accounts a on tl.account_id = a.account_id
inner join ns.locations l on l.location_id = ift.location_id
where tranid = '330167473' and a.full_name like '%Cost of Sales%'
and ift.transaction_number >= (select max(ift.transaction_number)
from ns.tbill)
group by ift.tranid, a.full_name, ift.transaction_number
Result:
330167473 VENDBILL34429 Cost of Sales 45735.84
Help with the right logic here would be great
