Possible Circular Reference in View

[Zarobi]

I have two tables with customer order information: Orders and LineItem. I also have a table of employees who help place the orders.

I am trying to create a view that shows the Orders.OrderID and Orders.CreateDate of the FIRST order placed for each Orders.EmpID (so, the first time an employee processed an order, and what the Order ID was).

Here's what I have so far:

SELECT Orders.EmpID, COUNT(distinct Orders.OrderID) AS Orders, MIN(LineItem.CreateDate) AS FirstOrderDate
FROM Orders, LineItem
WHERE LineItem.OrderID = Orders.OrderID
and LineItem.ItemStatus = 'shipped'
GROUP BY Orders.EmpID

This gives me how many Orders each employee has processed, and the date of their first order. But how do I then get the ID of the order with that CreateDate? Because of shipping delays and cancellations, the lowest OrderID isn't always the first Order processed (and so using MIN(Orders.OrderID) wouldn't necessarily match the correct OrderID with the MIN(LineItem.CreateDate).

I could build this into a table using a temp table easily enough, but the goal is to be able to use this information in a weekly Crystal Report. If I can get the information in a view, we won't have to constantly update the table, not to mention the massive amount of Order data I'd rather not store again.

 

[SQLDenis]

Untested but this should work, let me know

SELECT o.EmpID, l.Orders, l.FirstOrderDate
FROM (
SELECT OrderID, MIN(CreateDate) AS FirstOrderDate,COUNT(distinct OrderID) AS Orders
FROM  LineItem
WHERE ItemStatus = 'shipped'
GROUP BY OrderID) l join Orders o on l.OrderID = o.OrderID

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[maswien]

try this:

select t0.EmpID, t0.orders, t0.FirstOrderDate, t1.OrderID as firstProcessedOrder 
( SELECT Orders.EmpID, COUNT(distinct Orders.OrderID) AS Orders,
 MIN(LineItem.CreateDate) AS FirstOrderDate
FROM Orders, LineItem
WHERE LineItem.OrderID = Orders.OrderID
and LineItem.ItemStatus = 'shipped'
GROUP BY Orders.EmpID ) t0 inner join 
( SELECT Orders.EmpID, 
         Orders.OrderID, 
         LineItem.CreateDate AS FirstOrderDate
FROM Orders, LineItem
WHERE LineItem.OrderID = Orders.OrderID
and LineItem.ItemStatus = 'shipped' ) t1
on t0.EmpID = t1.EmpID and t0.FirstOrderDate = t1.FirstOrderDate

 

[Zarobi]

It never crossed my mind to next tables in a view (obviously). Both solutions were helpful, and the view is working. Thank you very much!

 

[maswien]

Zarobi , just curious, do you mean above 2 queries return the same results?

 

[Zarobi]

No, just that I used ideas from both queries for my solution. My original post was a simplified version. The end result ended up using a left join, but was very similar to the second suggestion. Once I realized I could nest table queries in a view, I had everything I needed.

Again, thanks so much!