Oracle SQL - Getting Max Date with several joins -
i think may overthinking this, i'm having issue when trying find max date several joins in oracle database several clauses. i've found many examples of simple max queries, nothing this. query works fine if add line in find records above specific date (there handful of results returned). however, want automatically recent date records bill table. database has additional table actual bill amount stored, adds layer.
select p.purchase_id, p.account_id, b.bill_date, bp.current_amount purchases p join bill_purchases bp on p.purchase_id = bp.purchase_id join bills b on bp.bill_id = b.bill_id --need recent date bill table each bill p.type != 'cash' , bp.amount > '100.00' , p.status = 'approved' , p.purchase_id in ( ... list of purchases ...);
i have tried doing subqueries max functions in them, not having luck. each query returns same amount of records original query. how rearrange query still retrieve of necessary columns , clauses while still limiting recent purchase billed?
try below this
select p.purchase_id, p.account_id, b.bill_date, bp.current_amount purchases p join bill_purchases bp on p.purchase_id = bp.purchase_id join ( select bill_id, max(bill_date) bill_date bills group bill_id )b on bp.bill_id = b.bill_id p.type != 'cash' , bp.amount > '100.00' , p.status = 'approved' , p.purchase_id in ( ... list of purchases ...);
Comments
Post a Comment