CODEWITHSUNDEEP
sqlsql-serverjoinaggregate-functionsmax
dave k
dave k

Reputation: 1389

aggregate function on separate table

I'm trying to select most recent date from one table (invoice) to a customer on another table. I'm not looking for a single customer, but several. What I want to do looks something like this:

select C.[Last Name], C.[First Name], C.[State], I.[Date]  
From myDb.dbo.Customer C  
left join myDb.dbo.Invoice I on I.CustomerID = C.CustomerID  
where c.state=@State and i.date = max(i.date)

I know I can't have the Max() in the where, and I tried using HAVING. I can't assign a local var for the customerID and do a where i.date = (select...). I'm trying to keep this all as one statement as this is being executed on several DB's from a single DB.

update:
I've decided to change my design requirements as this wasn't the most optimal solution.

Upvotes: 2

Views: 124

Answers (4)

Tim Lehner
Tim Lehner

Reputation: 15251

You possibly want to group:

select C.[Last Name], C.[First Name], C.[State], max(I.[Date]) as [Date]
from myDb.dbo.Customer C
    left join myDb.dbo.Invoice I on I.CustomerID = C.CustomerID
where C.state = @State
group by C.[Last Name], C.[First Name], C.[State]

Updated:

select A.[Last Name], A.[First Name], A.[State], B.[Date]
from myDb.dbo.Customer A
    join (
        -- Get Customers by State with their most recent Invoice Date
        select C.[CustomerID], max(I.[Date]) as [Date]
        from myDb.dbo.Customer C
            left join myDb.dbo.Invoice I on I.[CustomerID] = C.[CustomerID]
        where C.[State] = @State
        group by C.[CustomerID]
    ) B on A.[CustomerID] = B.[CustomerID]

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

Derived table returns last invoice date per customer. This is then joined back with customer.

select C.[Last Name], C.[First Name], C.[State], LastInvoice.LastInvoiceDate
from myDb.dbo.Customer C  
inner join
(
    select I.CustomerID, max (I.Date) LastInvoiceDate
      from myDb.dbo.Invoice I
     group by I.CustomerID
) LastInvoice
  on C.CustomerID = LastInvoice.CustomerID

There can be duplication if customer had more invoices on the same day (presumably dates do not contain time component). You might sort it out with distinct.

Upvotes: 1

Diego
Diego

Reputation: 36146

select C.[Last Name], C.[First Name], C.[State], I.[Date] ,
(select top 1 date from invoice I where I.CustomerID = C.CustomerID order by date desc)
From myDb.dbo.Customer C

Upvotes: 0

Royi Namir
Royi Namir

Reputation: 148524

;with cte as
(
select C.[Last Name], C.[First Name], C.[State], I.[Date]  
From myDb.dbo.Customer C  
left join myDb.dbo.Invoice I on I.CustomerID = C.CustomerID  
where c.state=@State 
) select * from cte where cte.Date = (select max (cte.Date) from cte)

Upvotes: 1

Related Questions