title: Parse Json and calculate values in SQL Server 2008R2
body: <p>I am using SQL Server 2008 R2 version. I have a table called Attendance which has the following values:</p>
  
  <pre><code>Id   Date         Status
  1    2017-12-20   [{"id":1,"Status":"Available"},{"id":2,"Status":"Sick Leave"},{"id":3,"Status":"Available"},{"id":4,"Status":"Available"}]
  2    2017-12-21   [{"id":1,"Status":"Sick Leave"},{"id":2,"Status":"Casual Leave"},{"id":3,"Status":"Available"},{"id":4,"Status":"Available"}]
  </code></pre>
  
  <p>What I need is </p>
  
  <pre><code>Date        Available   SickLeave  CasualLeave  Morning Permission Evening Permission 
  2017-12-20    3           1           0               0               0
  2017-12-21    3           1           1               0               0
  </code></pre>
  
  <p>I am really stuck. Can someone please help me with this.</p>
  
  <p>Thanks in advance           </p>