Please copy and run following script
DECLARE @Customers TABLE (CustomerId INT)
DECLARE @Orders TABLE ( OrderId INT, CustomerId INT, OrderDate DATETIME )
DECLARE @Calls TABLE (CallId INT, CallTime DATETIME, CallToId INT, OrderId INT)
-----------------------------------------------------------------
INSERT INTO @Customers SELECT 1
INSERT INTO @Customers SELECT 2
-----------------------------------------------------------------
INSERT INTO @Orders SELECT 10, 1, DATEADD(d, -20, GETDATE())
INSERT INTO @Orders SELECT 11, 1, DATEADD(d, -10, GETDATE())
-----------------------------------------------------------------
INSERT INTO @Calls SELECT 101, DATEADD(d, -19, GETDATE()), 1, NULL
INSERT INTO @Calls SELECT 102, DATEADD(d, -17, GETDATE()), 1, NULL
INSERT INTO @Calls SELECT 103, DATEADD(d, -9, GETDATE()), 1, NULL
INSERT INTO @Calls SELECT 104, DATEADD(d, -6, GETDATE()), 1, NULL
INSERT INTO @Calls SELECT 105, DATEADD(d, -5, GETDATE()), 1, NULL
-----------------------------------------------------------------
I want to update @Calls table and need following results.
I am using the following query
UPDATE @Calls
SET OrderId = (
CASE
WHEN (s.CallTime > e.OrderDate)
THEN e.OrderId
END
)
FROM @Calls s INNER JOIN @Orders e ON s.CallToId = e.CustomerId
and the result of my query is not what I need.
Requirement:
As you can see there are two orders. One is on 2010-12-12 and one is on 2010-12-22. I want to update @Calls table with relevant OrderId with respect to CallTime.
In short If subsequent Orders are added, and there are further calls then we assume that a new call is associated with the most recent Order
Note:
This is sample data so this is not the case that I always have two Orders. There might be 10+ Orders and 100+ calls.
Note2
I could not find good title for this question. Please change it if you think of any better.
Thanks.
