3

I have two tables Table 1 and Table 2

Table 1:

-------------------------------
| Ser | StartDate  | Activity |  
-------------------------------
|  1  | 2002-10-13 |    1     |  
|  1  | 2002-10-13 |    2     |  
|  1  | 2007-09-04 |    3     |

Table 2:

------------------------
|Ser|DateOfRegistration|  
------------------------
| 1 |  2002-10-12      |  
| 1 |  2007-09-02      |

Now, the result that I want is such that for Activity 1 and 2 the Date of registration should be before the Start Date and difference between the dates must be the least. Similarly, for Activity 3, the date of registration for Activity 3 should be before the start date. The result should look like this.

Table 3: 

--------------------------------------------
|Ser|StartDate |DateofRegistration|Activity|  
--------------------------------------------
| 1 |2002-10-13|  2002-10-12      |   1    |  
| 1 |2002-10-13|  2002-10-12      |   2    |  
| 1 |2002-09-04|  2002-09-02      |   3    |

How can I join Table 1 and 2 to get Table 3?

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Roshan Joe Vincent
  • 301
  • 3
  • 10

1 Answers1

1

You can use outer apply:

select t1.*, t2.dateofregistration
from table1 t1 outer apply
     (select top (1) t2.*
      from table2 t2
      where t2.ser = t1.ser and t2.dateofregistration < t1.startdate
      order by t2.dateofregistration desc
     ) t2
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Do you have a good resource for learning how APPLY works? I've rarely had to use it, but I never fully understand what it is doing. Also, now that you're here I should probably go back to work... – Edward Jul 12 '18 at 21:22
  • @Edward https://stackoverflow.com/questions/9275132/real-life-example-when-to-use-outer-cross-apply-in-sql Has some good example and the links provide use-cases. – Shawn Jul 12 '18 at 22:05
  • This Works! Thanks! – Roshan Joe Vincent Jul 12 '18 at 22:28