March 1, 2017 at 9:18 am
Hi
I have two tables orders and companyrefferals
I am trying to return a ReferralId from the companyrefferals table for eachcustomer based looking at nearest or closest between the two table single datefields
companyrefferals DateReferred column and Orders OrderDatecolumn
I Have the following working query.
select top 1 r.ReferralId, o.CustomerID, o.OrderID
from companyrefferalsr
left join orders o
on r.CustomerID = o.CustomerID
where r.DateReferred <=o.OrderDate
AND o.CustomerID = 'ABC1'
which is great when this customer has only 1 ReferralId record in his / herhistory records, from the companyrefferals table
the issue I have is when a customer has 2 or more
I have tried to amend the above with no luck
please help
March 1, 2017 at 9:29 am
No sample,or DDL, so untested, and no expected result set, but maybe...:SELECT cr.ReferralId, o.CustomerID, o.OrderID
FROM orders o
CROSS APPLY (SELECT TOP 1 *
FROM companyrefferalsr ca
WHERE ca.CustomerID = CustomerID
AND ca.DateReferred <= o.OrderDate
ORDER BY ca.DateReferred DESC) cr
WHERE o.CustomerID = 'ABC1';
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 1, 2017 at 9:31 am
Well, hard to say without having more information. It would help if you could provide the DDL (CREATE TABLE statement) for the two tables, some sample data (not production data) (INSERT INTO statements) for both tables, and expected results based on the sample data.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply