title: Compare multiple columns from 1 database to multiple columns in a different database in SQL
body: <p>In 1 database I have 5 DATE fields and in the other database I have 10 DATE fields.
  Alot of those fields are 1900-01-01 or empty.</p>
  <p>What I want to do is check if one or more columns from the first database match with one of the dates in the second database.</p>
  <p>In the code Below I check if DB1_date1 value matches with the DB2_date1 value.</p>
  <pre><code>     ,case
              when (len([DB1_date1]) = 0 and len([DB2_date1]) = 0) then 1
              when len([DB1_date1]) = 0 and [DB2_date1]= '1900-01-01 00:00:00.000' then 1
              when (right([DB1_date1], 4) = '0000' or right([DB2_date1] , 2) = '00' or right([DB2_date1] , 4) = '0000' or right([DB1_date1], 2) = '00') then 5
              when cast([DB1_date1] as date) = cast([DB2_date1] as date) and (len([DB1_date1]) &gt; 0 and len([DB2_date1]) &gt; 0) then 0
              when len([DB1_date1]) = 0 and cast([DB1_date1] as date) &lt;&gt; cast([DB2_date1] as date) then 2
              when [DB2_date1] = '' and cast([DB1_date1] as date) &lt;&gt; cast([DB2_date1]as date) then 3
              when len([DB1_date1]) &gt; 0 and cast([DB1_date1] as date) &lt;&gt; cast([DB2_date1] as date) then 4
  else 5
              end as DateChk
  </code></pre>
  <p>The problem is for example DB2_date4 can exist in DB1_date1. So if I continue writing my script with this logic I have to create a million when cases.</p>
  <p>Does someone have any idea how I can begin writing a better script? performance doesnt matter.</p>
  <p>Im using SQL server 16</p>
  <p>EDIT:</p>
  <pre><code>DECLARE @Table_1 TABLE(ID INT, T1_Date_1 DATE, T1_Date_2 DATE, T1_Date_3 DATE)
   
  INSERT INTO @Table_1
  VALUES 
  (1,'1900-01-01', '1900-01-01', '1900-01-01')  ,
  (2,'1900-01-01', '1974-01-01', '1900-01-01')  ,
  (3,'1900-01-01', '1900-01-01', '2021-01-01')  ,
  (4,'1900-01-01', '2021-01-01', '1900-01-01')  
  
  SELECT * FROM @Table_1
  
  DECLARE @Table_2 TABLE(ID INT, T2_Date_1 DATE, T2_Date_2 DATE, T2_Date_3 DATE, T2_Date_4 DATE);
   
  INSERT INTO @Table_2
  VALUES 
  (1,'1900-01-01', '1900-01-01', '1900-01-01', '1900-01-01')  ,
  (2,'1900-01-01', '1974-01-01', '1900-01-01', '1900-01-01')  ,
  (3,'2021-01-01', '1900-01-01', '1900-01-01', '1900-01-01')  ,
  (4,'1900-01-01', '2014-01-01', '1900-01-01', '1900-01-01')  
  
  SELECT * FROM @Table_2
  </code></pre>
  <p>1900-01-01 means no date.</p>
  <p>The desired result is:</p>
  <p>ID 1 = nothing.(nothing because there is no date in both tables)</p>
  <p>ID 2 = nothing.(there is 1 date in both tables and they match)</p>
  <p>ID 3 = nothing.(there is 1 date in both tables and they match)</p>
  <p>ID 4 = Check.(there is a date in both tables and they do not match)</p>