Roni Castro Roni Castro - 5 months ago 7
SQL Question

Joining two tables on same date to form pairs of rows that do not have repetitions

I need to form pair of rows when they have DATETIME IN COMMON, but the pairs should be formed in increasing order of the code, so the first code of table 1 must match the first code of table 2. Code that was used to form a pair should not be repeated:
Example:

**Table T1:
code dtTime
#4, 10/10/2010 10:00
#5, 10/10/2010 10:00
#6, 10/10/2010 10:00


**Table T2:
code dtTime
#1, 10/10/2010 10:00
#2, 10/10/2010 10:00

**Pairs that must be formed:
T1.code, ISNULL(T1.dtTime,T2.dtTime), T2.code
#4, 10/10/2010 10:00, #1
#5, 10/10/2010 10:00, #2
#6, 10/10/2010 10:00 NULL


In the example above I cannot make the combination of #4 with #2 because the code #1 is smaller than #2, and also i cannot have #5 with #1, because #1 was already used to form a pair with #4.
I tried using joining on dtTime, but it will result in all the possibilities(#4 with #1, #4 with #2, #5 with #1 and #5 with 2)

Answer

It seems like you can use a query like the following:

;WITH Table1 AS (
   SELECT code, dtTime,
          ROW_NUMBER() OVER (PARTITION BY dtTime ORDER BY code) AS rn
   FROM T1
), Table2 AS (
   SELECT code, dtTime,
          ROW_NUMBER() OVER (PARTITION BY dtTime ORDER BY code) AS rn
   FROM T2
)
SELECT t1.code, 
       COALESCE(t1.dtTime, t2.dtTime) AS dtTime, 
       t2.code
FROM Table1 AS t1
FULL OUTER JOIN Table2 AS t2 ON t1.dtTime = t2.dtTime AND t1.rn = t2.rn

The query joins together records having the same date. If more than one records with the same date exist, then records having the smallest code value for the date are joined together, followed by the records having the next biggest code value, etc.