Getting Most Recent Order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting Most Recent Order

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2007-01-15 : 05:09:55

I have a Customers table and an Orders table. The Customers table has fields... CustomerId, and CustomerName. The Orders table has Fields CustomerId, OrderDate, and ProductId.

I wish to execute a query that will return a single Customer and Order record for a given customer ID that reflects the most recent order. So, a typical join like...

SELECT c.customerId, c.customerName, o.orderdate,o.productId

FROM Customers C LEFT OUTER JOIN Orders o ON (o.customerId=c.customerId)

WHERE c.customerId=SOMEVALUE

would return all the orders for a given customer but I only want the most recent order. Can anyone help?

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-15 : 05:13:25

[code]
SELECT c.customerId, c.customerName, o.orderdate,o.productId
FROM Customers C LEFT OUTER JOIN Orders o ON (o.customerId=c.customerId)
AND o.orderdate = (select max(orderdate) from Orders x where x.customerId = o.customerId)
WHERE c.customerId=SOMEVALUE
[/code]

akashenk
Posting Yak Master

111 Posts

Posted - 2007-01-15 : 05:23:26

Thanks a lot!

tikiguy
Starting Member

6 Posts

Posted - 2007-05-08 : 18:29:29

I'm trying to do a similar thing but with 3 tables:
Ticket, Ticket_CrewPerson, Employee

Here's my query so far - can someone point me in the right direction. Just a hint.

It doesn't narrow it down to the most recent ticket.

SELECT E.employeeLastName, E.employeeFirstName, T.ticketDate
FROM Employee E INNER JOIN
Ticket_CrewPerson TCP ON E.employeeID = TCP.employeeID LEFT OUTER JOIN
Ticket T ON TCP.ticketID = T.ticketID
AND T.ticketDate = (SELECT MAX(ticketDate) FROM Ticket x WHERE x.ticketID = TCP.ticketID)
WHERE T.ticketDate <= GETDATE() AND (T.ticketDate >= GETDATE() - 50)
GROUP BY E.employeeLastName, E.employeeFirstName, T.ticketDate
ORDER BY E.employeeLastName, E.employeeFirstName, T.ticketDate

akashenk
Posting Yak Master

111 Posts

Posted - 2007-05-08 : 18:59:27

I'm not quite sure what you want to do. The query I used was looking for the "Most recent" item.. in other words, it returned only a single record. Your query, at least the clause "WHERE T.ticketDate <= GETDATE() AND (T.ticketDate >= GETDATE() - 50)" appears to be searching for a range of items... in other words, more than one record returned. Perhaps you can explain more what the three tables represent and what you are trying to retrieve.

tikiguy
Starting Member

6 Posts

Posted - 2007-05-09 : 09:57:57

Hi,

What I'm trying to do is find Employees who have worked at least one day between now and 50 days ago. I'd like to return just the most recent record for each employee to shorten the report.

The tables I have are:
Employee
Ticket
Ticket_CrewPerson

Ticket holds the ticket date.
Ticket_CrewPerson relates back to the Ticket and has the EmployeeID
Employee has the names and relates to the Ticket_CrewPerson by EmployeeID

I'm not sure I explained that well.

tikiguy
Starting Member

6 Posts

Posted - 2007-05-09 : 10:01:35

I think I found part of my problem- my query does work! The problem is that each ticket is unique so it finds every ticket within the range.

One job can have many tickets, so I need to find the max of the date within the JOB not the Ticket. Which means I have to introduce the Jobs table into my query.

tikiguy
Starting Member

6 Posts

Posted - 2007-05-09 : 10:09:59

That shaved the result set down to 143 records - I still get multiples of the same employee, so now I have to figure out how to get just the most recent job.

It's been 2 years since I've done any SQL.

akashenk
Posting Yak Master

111 Posts

Posted - 2007-05-09 : 16:53:55

If you're trying to find just those employees who have worked in the last 50 days, I think you should first just find all the tikets for the last 50 days and select the distinct Ticket_CrewPerson.EmployeeId, and then join this to your employees table. I think something like the following might work...

SELECT DISTINCT EmployeeID FROM

(SELECT tcp.employee_id AS EmployeeID FROM (Ticket T LEFT JOIN Ticket_CrewPerson TCP ON (TCP.ticketID=T.ticketID) WHERE T.ticketDate <= GETDATE() AND T.ticketDate >= (GETDATE() - 50)) AS Result

LEFT JOIN Employee E ON (Result.EmployeeId = E.EmployeeId)

I'm also no SQL expert so you might have to play around with the syntax a little, but I think this apporach should work, and you won't have to bother getting the most recent entry for an employee. I am also not sure if there is a better way of doing this (performance-wise), but I would imagine you would want to get it working first before worrying about that.

tikiguy
Starting Member

6 Posts

Posted - 2007-05-09 : 17:15:50

I see what you are getting at. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources