title: Get users who renew the package after expiry
body: <p>I have a table which gives information about which users bought a package, when it started, expired etc.</p>
  
  <p>table:</p>
  
  <pre><code>+------+--------------+------------+------------+---------+
  | user | Package_Type |   Start    |    End     | Status  |
  +------+--------------+------------+------------+---------+
  |  111 | A            | 2019-06-01 | 2019-06-18 | Valid   |
  |  123 | A            | 2019-06-01 | 2019-06-18 | Valid   |
  |  124 | A            | 2019-06-02 | 2019-06-06 | Expired | //Type A expired
  |  124 | C            | 2019-06-02 | 2019-06-18 | Valid   |
  |  125 | B            | 2019-06-03 | 2019-06-09 | Expired |
  |  126 | B            | 2019-06-04 | 2019-06-20 | Valid   |
  |  127 | B            | 2019-06-04 | 2019-06-07 | Expired |  // Type B expired
  |  124 | B            | 2019-06-06 | 2019-06-18 | Valid   |  //Renewal to type B after expiry of type A
  |  127 | B            | 2019-06-08 | 2019-06-18 | Valid   |  // Renwal to Type B
  +------+--------------+------------+------------+---------+
  </code></pre>
  
  <p>In a time frame <em>(let timeframe be <code>WHERE start BETWEEN '2019-06-01' AND '2019-06-10'</code>)</em> I wanted to know how many users whose package expired have renewed it. </p>
  
  <p>Expected output is as follows</p>
  
  <pre><code>+------------+---------+---------+
  |    Date    | Expired | Renewed |
  +------------+---------+---------+
  | 2019-06-01 |       0 |       0 |
  | 2019-06-02 |       0 |       0 |
  | 2019-06-03 |       0 |       0 |
  | 2019-06-04 |       0 |       0 |
  | 2019-06-05 |       0 |       0 |
  | 2019-06-06 |       1 |       1 | // Expiry and renewal of user 124
  | 2019-06-07 |       1 |       0 | // Expiry of user 127
  | 2019-06-08 |       0 |       1 |//  Renewal of user 127
  | 2019-06-09 |       1 |       0 |// Expiry of user 125
  | 2019-06-10 |       0 |       0 |
  +------------+---------+---------+
  </code></pre>
  
  <p>It is not necessary that user has to renew to same kind of package.</p>
  
  <ol>
  <li>For user 124 Type A expired but he renewed to Type B. (Also note that user 124 is still active for type C).</li>
  <li>So there can be cases where a type of package expires, but same user can be active on some other package and he can renew to some different package itself.</li>
  <li>I just want to know after expiry of package when the user renews.(Even if he is active on any other package)</li>
  </ol>
  
  <p><strong>Update 1</strong>
  I also wanted above data to be represented as </p>
  
  <pre><code>+--------------+---------+---------+
  | Package_Type | Expired | Renewed |
  +--------------+---------+---------+
  | A            |       1 |       0 |
  | B            |       2 |       2 |
  | C            |       0 |       0 |
  +--------------+---------+---------+
  </code></pre>
  
  <p>I wanted a query in which if I change 
  <code>WHERE start BETWEEN '2019-06-01' AND '2019-06-10'</code>)*</p>
  
  <p>The above table will be updated according to <code>start</code> filter</p>
  
  <p><a href="http://www.sqlfiddle.com/#!18/c1e8d/1" rel="nofollow noreferrer">SQL Server Link</a></p>