title: SQL Server 2012: Categorizing NULL values as missing and pending based on occurence
body: <p>I have a data related to student week wise attendance in a study center. It will have Centerid, studentid, week1, week2 and so on.</p>
  
  <p>This is the <a href="http://rextester.com/PTWT2984" rel="nofollow noreferrer">Pivoted Schema link</a> for my data and this is the <a href="http://rextester.com/YHFM77163" rel="nofollow noreferrer">link</a> for my original data.</p>
  
  <p>The student may have missed few weeks in between or few weeks yet to come in future.</p>
  
  <p>What I need is to categorize the missing dates into Missed weeks and Futuree Weeks.</p>
  
  <p><a href="https://i.stack.imgur.com/Xuifb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Xuifb.png" alt="Screenshot sample"></a></p>
  
  <p>If you check the above screenshot, the NULL values highlighted in red are the missing ones and the NULL values in green are the future ones. When The student attends the class, we will have the date when he attended the class for each week. Hence when he misses the class, there wont be date and will be null. similarly when the week is of future, it will be null as he is yet to attend the class.</p>
  
  <p>I want to classify the red ones as missing and the green ones as future. I was able to do this using a case comparison like</p>
  
  <pre><code>WHEN WEEK1 IS NULL AND (WEEK2 is NOT NULL OR WEEK3 is NOT NULL OR WEEK 4 is NOT NULL OR WEEK 5 is NOT NULL OR WEEK6is NOT NULL OR WEEK7 is NOT NULL THEN 'WEEK1_Missing'
  WHEN WEEK7 IS NULL AND (WEEK2 is NOT NULL OR WEEK3 is NOT NULL OR WEEK 4 is NOT NULL OR WEEK 5 is NOT NULL OR WEEK6is NOT NULL OR WEEK1 is NOT NULL THEN 'WEEK7_Future'
  </code></pre>
  
  <p>Is there any better approach to this? Please suggest me.</p>
  
  <p>Thanks in advance.</p>