Join nearest date (no subquery)

Question

I have this not-so-normalized table:

ItemName    Type    DateTransferred

Hand Drill  IN  2012-01-16 11:06:10.077
Hand Drill  OUT 2012-01-16 11:06:16.563
Hand Drill  IN  2012-01-16 11:06:26.780
Grinder     IN  2012-01-16 11:06:33.917
Hand Drill  OUT 2012-01-16 11:06:45.443

Create query:

CREATE TABLE [dbo].[TransferLog](
    [ItemName] [nvarchar](50) NOT NULL,
    [Type] [nvarchar](3) NOT NULL,
    [DateTransferred] [datetime] NOT NULL
) ON [PRIMARY]


ALTER TABLE [dbo].[TransferLog] 
ADD  CONSTRAINT [DF_TransferLog_DateTransferred]  
    DEFAULT (getdate()) FOR [DateTransferred]

Basically, the table above logs the items borrowed (type: IN) and items returned (type: OUT) by a project team from a warehouse.

What I want to achieve is get all borrowed equipments, when it was borrowed(IN) and when it was returned(OUT). The problem occurs when trying to match a "borrow transaction" to it's corresponding "return transaction" since the only relation they have is the ItemName:

Selecting all "borrow transactions":

select tIn.ItemName, tIn.DateTransferred as DateBorrowed
from transferLog as tIn
where tIn.[type] = 'IN'

Result:

ItemName    DateBorrowed

Hand Drill  2012-01-16 11:06:10.077
Hand Drill  2012-01-16 11:06:26.780
Grinder     2012-01-16 11:06:33.917

Attempt to select all "borrowed transactions" and their corresponding "return transaction":

select tIn.ItemName, tIn.DateTransferred as DateBorrowed, 
    tOut.DateTransferred as   DateReturned
from transferLog as tIn
left join transferLog as tOut
on tIn.ItemName = tOut.ItemName
    and tOut.[type] = 'OUT'
where tIn.[type] = 'IN'

Result:

ItemName    DateBorrowed                    DateReturned

Hand Drill  2012-01-16 11:06:10.077     2012-01-16 11:06:16.563
Hand Drill  2012-01-16 11:06:10.077     2012-01-16 11:06:45.443
Hand Drill  2012-01-16 11:06:26.780     2012-01-16 11:06:16.563
Hand Drill  2012-01-16 11:06:26.780     2012-01-16 11:06:45.443
Grinder     2012-01-16 11:06:33.917     NULL

Note that, each "borrowed transaction" should only have one or no corresponding "return transaction", the above result match each "borrowed transaction" to every "return transaction" as long as they have the same ItemName. The result should be:

ItemName    DateBorrowed                    DateReturned

Hand Drill  2012-01-16 11:06:10.077     2012-01-16 11:06:16.563
Hand Drill  2012-01-16 11:06:26.780     2012-01-16 11:06:45.443
Grinder     2012-01-16 11:06:33.917     NULL

Now, I'm thinking of how can I match the "return transaction" with a DateTransferred greater than and nearest to the "borrow transaction"'s DateTransferred. Something like:

select tIn.ItemName, tIn.DateTransferred as DateBorrowed, 
    tOut.DateTransferred as DateReturned
from transferLog as tIn
left join transferLog as tOut
on tIn.ItemName = tOut.ItemName
and tOut.[type] = 'OUT'
and 
        tOut.DateTransferred > tIn.DateTransferred 
        -- AND NEAREST tOut.DateTransferred TO tIn.DateTransferred
where tIn.[type] = 'IN'

I read this ( SQL Join on Nearest less than date ) and this ( Join tables on nearest date in the past, in MySQL ) but subquery is a difficult chioce for me since the result of the query I need is just a part of another query, I'm afraid it will affect the performance.

score 4 · Accepted Answer · answered Jan 16 '12 at 06:33

The following does use a subquery (more exactly, a common table expression), but it should be effecient enough:

;
WITH ranked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (PARTITION BY ItemName, Type ORDER BY DateTransferred)
  FROM TransferLog
)
SELECT
  r_in.ItemName,
  r_in.DateTransferred AS DateBorrowed,
  r_out.DateTransferred AS DateReturned
FROM ranked r_in
  LEFT JOIN ranked r_out ON r_out.Type = 'OUT'
    AND r_in.ItemName = r_out.ItemName
    AND r_in.rnk = r_out.rnk
WHERE r_in.Type = 'IN'

As you can see, the idea is to rank IN and OUT rows separately, then match the former with the latter (using an outer join, because the last IN item can have no match).

References:

I think you're right, your subquery will just execute few times so it'll be okay. I'll test ranking... — kazinix, Jan 16 '12 at 07:38

score 0 · Answer 2 · answered Feb 11 '12 at 06:18

it seems late to answer this question, but i think i should answer it since i have seen it. there's something wrong in the answer up. if there's a new record as following inserted into the table: Hand Drill 2012-01-16 11:06:10.077 2010-01-16 00:00:00.563 it will result in a wrong consequence. This is my answer.

select 
tIn.ItemName,
tIn.DateTransferred as DateBorrowed,
tOut.DateTransferred as DateReturned
from TransferLog as tIn
left join TransferLog as tOut
on tIn.ItemName=tOut.ItemName 
and tOut.[Type]='out' 
and tOut.DateTransferred=
(
    select 
    top 1 DateTransferred 
    from TransferLog as temp
    where temp.DateTransferred>tIn.DateTransferred 
    and temp.ItemName=tIn.ItemName 
    and tIn.[Type]='in' 
    and temp.[Type]='out'
    order by temp.DateTransferred asc
)
where tIn.[Type]='in'

Forgiving my poor English and giving me your opinion

Join nearest date (no subquery)

2 Answers2