Help With SQL Query

[P00HB33R]

Hi Guys,

Really need some help with this one.

So lets say I have a customer table, orders table, and a quotes table. Customers and orders have a one-to-many relationship. So does Customers and Quotes.

Customer 1 -----> Many Orders
Customer 1 -----> Many Quotes

How would I create a view that would return a list of all customers with only one Quote ID whith the earliest QuoteDate and only one Order ID with the latest OrderDate?

eg. Result should look something like this:

Customer Table:
ID Name
1 Test Co
2 Test Pty
3 Testing Co

Result Should Look Like:
CustomerID CustomerName QuoteID Quote Date OrderID OrderDate
1 Test Co 34 2017-10-01 55 2017-10-23
2 Test Pty 24 2017-10-02 57 2017-10-29
3 Testing Co 39 2017-10-01 65 2017-10-27

Thus, all customers should be returned in the view, regardless of the existence of either a quote or order, but if they exist return the earliest quote and the latest order or replace values with null if either does not exist.

Aprreciate any help

 

[shooter69]

Cursor maybe

 

[schuits]

Cursor maybe

oooh nooo you didn't!

 

[Messugga]

Something like this:

SELECT CustomerID, CustomerName, QuoteID, Quote, Date, OrderID, OrderDate
FROM Customer
LEFT OUTER JOIN (CustomerID, QuoteID, Quote, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY QuoteDate ASC) AS ROW_NUM) Quote
ON  Customer.ID = Quote.CustomerID
AND Quote.ROW_NUM = 1
LEFT OUTER JOIN (CustomerID, OrderID, OrderDate, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS ROW_NUM) Ord
ON Customer.ID = Ord.CustomerID
AND Ord.ROW_NUM = 1

 

[schuits]

Not sure if this will work:

;WITH CustomersCTE(CustomerID, CustomerName) AS
(SELECT CustomerID, CustomerName
 FROM Customer
), QuotesCTE AS
	(SELECT TOP 1 CCTE.CustomerID, CustomerName, QuoteID, QuoteDate
	FROM CustomersCTE CCTE
	LEFT OUTER JOIN [Quote2] Q
	ON CCTE.CustomerID = Q.CustomerID
	ORDER BY Q.QuoteDate ASC
	) 
SELECT TOP 1 QCTE.CustomerID, CustomerName, QuoteID, QuoteDate, OrderID, OrderDate
FROM QuotesCTE QCTE
LEFT OUTER JOIN [Order] O
ON O.CustomerID = QCTE.CustomerID
Order by O.OrderDate Desc

Though I don't like CTEs and think Messuga's solution will perform better.

 

[P00HB33R]

Thanks guys. @Messugga Tried your code but could not get it working. Ended up using a select MAX and MIN on the joins.