CODEWITHSUNDEEP
sqloracle-databasemax
SausageBuscuit
SausageBuscuit

Reputation: 1286

Oracle SQL - Getting Max Date with several joins

I think I may be overthinking this, but I'm having an issue when trying to find a max date with several joins in an Oracle database as well as several where clauses. I've found many examples of simple max queries, but nothing specifically like this. The query works fine if I add a line in to find all records above a specific date (there are only a handful of results returned). However, I want to automatically get the most recent date for all records from the bill table. This database has an additional table where the actual bill amount is stored, so that adds another layer.

SELECT p.purchase_id, p.account_id, b.bill_date, bp.current_amount 
FROM Purchases p
JOIN Bill_Purchases bp ON p.purchase_id = bp.purchase_id
JOIN Bills b ON bp.bill_id = b.bill_id
--NEED TO GET MOST RECENT DATE FROM BILL TABLE FOR EACH BILL
WHERE p.type != 'CASH'
AND bp.amount > '100.00' 
AND p.status = 'Approved'
AND p.purchase_id IN ( ... list of purchases ...);

I have tried doing subqueries with Max functions in them, but am not having any luck. Each query returns the same amount of records as the original query. How would I rearrange this query to still retrieve all of the necessary columns and where clauses while still limiting this to only the most recent purchase that was billed?

Upvotes: 0

Views: 150

Answers (4)

SausageBuscuit
SausageBuscuit

Reputation: 1286

I got this working pretty much right after posting, was a stupid mistake on my part. Data for the max function needed to be joined on the account_id, not the bill_id.

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

Do either of these work for you?

WITH data as (
    SELECT
        p.purchase_id, p.account_id, b.bill_date, bp.current_amount,
    FROM
        Purchases p
        INNER JOIN Bill_Purchases bp ON p.purchase_id = bp.purchase_id
        INNER JOIN Bills b ON b.bill_id = bp.bill_id
    WHERE p.type <> 'CASH'
    AND bp.amount > '100.00' 
    AND p.status = 'Approved'
    AND p.purchase_id IN ( ... list of purchases ...)
), most_recent as  (
    SELECT max(bill_date) as bill_date FROM data
)
SELECT *
FROM data
WHERE bill_date = (select bill_date from most_recent);


SELECT purchase_id, account_id, bill_date, current_amount
FROM (
    SELECT
        p.purchase_id, p.account_id, b.bill_date, bp.current_amount,
        dense_rank() over (order by b.bill_date desc) as dr
    FROM
        Purchases p
        INNER JOIN Bill_Purchases bp ON p.purchase_id = bp.purchase_id
        INNER JOIN Bills b ON b.bill_id = bp.bill_id
    WHERE p.type <> 'CASH'
    AND bp.amount > '100.00' 
    AND p.status = 'Approved'
    AND p.purchase_id IN ( ... list of purchases ...)
) data
WHERE dr = 1;

Upvotes: 0

rebecca raezada
rebecca raezada

Reputation: 71

You may want to solve this problem using Rank() function, 

SELECT p.purchase_id, p.account_id, , bp.current_amount, RANK() OVER ( partition by b.bill_id order by b.bill_date) as max_bill_date 
FROM Purchases p
JOIN Bill_Purchases bp ON p.purchase_id = bp.purchase_id
JOIN Bills b ON bp.bill_id = b.bill_id
--NEED TO GET MOST RECENT DATE FROM BILL TABLE FOR EACH BILL
WHERE max_bill_date = 1
AND p.type != 'CASH'
AND bp.amount > '100.00' 
AND p.status = 'Approved'`enter code here`
AND p.purchase_id IN ( ... list of purchases ...);

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try like below this

SELECT p.purchase_id, p.account_id, b.bill_date, bp.current_amount 
FROM Purchases p
JOIN Bill_Purchases bp ON p.purchase_id = bp.purchase_id
JOIN (  SELECT bill_id, MAX(bill_date) bill_date
        FROM Bills 
        GROUP BY bill_id
     )b ON bp.bill_id = b.bill_id
WHERE p.type != 'CASH'
AND bp.amount > '100.00' 
AND p.status = 'Approved'
AND p.purchase_id IN ( ... list of purchases ...);

Upvotes: 1

Related Questions