title: How to include loan rules in Employee and Loan sql query?
body: <p>I have three tables <code>Employee</code>, <code>Loan</code> and <code>LoanInstallment</code>. </p>
  
  <p><code>Employee</code> table has one to many relation with <code>Loan</code>, and <code>Loan</code> has one to 
  many with <code>LoanInstallment</code>. </p>
  
  <ul>
  <li><code>Employee</code> (EmpId, Name, IsOnProbation)</li>
  <li><code>Loan</code> (LoanId, EmpId, StartDate, EndDate).</li>
  </ul>
  
  <p>Now I need to write a query to fetch the records of employee in following output. </p>
  
  <p>Output records (EmpId, Name, Status, Reason)</p>
  
  <p>Rules</p>
  
  <ul>
  <li><p>if employee has not taken loan never then its status should be Eligible and reason as loan not taken. </p></li>
  <li><p>if employee has taken loan within one year (i.e EndDate is less than one year) then its status should be Not Eligible and reason loan already taken.</p></li>
  <li><p>if employee is on probation then status should be Not Eligible and Reason on Probation</p></li>
  <li><p>if employee has taken laon 1 year ago then status should be Eligible and Reason Loan taken 1 year ago.</p></li>
  </ul>
  
  <p>I have written a simple query but I am unable to understand how to include all the four rules and include Reason column in this single query.</p>
  
  <pre><code>SELECT
      e.EmployeeID, E.FullName,l.EndDate, 
      (CASE
          WHEN DATEDIFF(YEAR, max(l.EndDate), GETDATE()) &lt; 0  
             THEN 'Eligible'
             ELSE 'Not Eligible'
       END) as Status
  FROM 
      Employee e 
  LEFT JOIN
      Loan l ON e.EmployeeID = l.EmployeeID
  GROUP BY
      e.EmployeeID, e.FullName, l.EndDate
  </code></pre>