title: SQL Server Date Range Calculation
body: <p>I have table like below. </p>
  
  <pre><code>Workid  StartDate   EndDate
  101     2012-03-01  2013-03-20
  101     2013-03-21  2014-08-01
  102     2014-01-01  2014-12-31
  103     2012-01-01  2012-10-11
  103     2012-10-12  2014-12-31
  104     2012-01-01  2012-12-25
  105     2012-01-01  2014-06-30
  105     2014-07-01  2014-12-31
  </code></pre>
  
  <p>I need to verify whether the workid fully occupied or not for the given year. For example, If I select the year as 2012, then the output should be like below.</p>
  
  <pre><code>WorkID  STATUS
  101     Not Occupied
  102     Not Occupied
  103     Occupied
  104     Not Occupied
  105     Occupied
  </code></pre>
  
  <p>If I select the year as 2013, then the output should be like below.</p>
  
  <pre><code>WorkID  STATUS
  101     Occupied
  102     Not Occupied
  103     Occupied
  104     Not Occupied
  105     Occupied
  </code></pre>
  
  <p>Can some one please help on this.</p>
  
  <p>below is the sample table script.</p>
  
  <pre><code>CREATE TABLE #temp
  (
      Workid INT,
      StartDate DATE,
      EndDate DATE
  )
  go
  INSERT INTO #temp
  (
      Workid,
      StartDate,
      EndDate
  )
  VALUES
  (101, '2012-03-01', '2013-03-20'),
  (101, '2013-03-21', '2014-08-01'),
  (102, '2014-01-01', '2014-12-31'),
  (103, '2012-01-01', '2012-10-11'),
  (103, '2012-10-12', '2014-12-31'),
  (104, '2012-01-01', '2012-12-25'),
  (105, '2012-01-01', '2014-06-30'),
  (105, '2014-07-01', '2014-12-31')
  ;
  </code></pre>