Get datetime difference group by query results in one row
<p>I have a table <code>Master_History</code> with structure as </p>

<pre><code>Id_History  Created_Date                Subscription_Type   rn
21              1/22/2016 16:31:29             1            1
22              1/22/2016 16:33:11             2            2
23              1/22/2016 16:33:37             1            3
24              1/22/2016 16:33:46             2            4
25              1/22/2016 16:33:53             1            5
26              1/22/2016 16:33:57             3            6
27              1/22/2016 16:34:01             2            7
28              1/22/2016 16:34:04             1            8
29              1/22/2016 16:34:08             3            9
</code></pre>

<p>I want to calculate date difference with adjacent rows which i have succeeded in calculating but results are getting distributed over multiple rows </p>

<pre><code>Standard    Plus    Premium
122         NULL    NULL
NULL          35    NULL
NULL        NULL    3
</code></pre>

<p>I need </p>

<ul>
<li><p>Results in one row like</p>

<p><code>Standard   Plus    Premium
 122        35      3</code></p></li>
<li><p>For the last row (in this <code>Subscription_Type</code> is 3, the date difference should also get calculated on <code>getdate()</code> i.e. whenever i executed my query, seconds in <code>Premium</code> column should get reflected each time</p></li>
</ul>

<p>Query :</p>

<pre><code>WITH CTE
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            ORDER BY Created_Date
            ) AS rn
    FROM Master_History
    WHERE Client_ID = 11072
    )


SELECT CASE 
        WHEN mc.Subscription_Type = 1
            THEN Sum(DATEDIFF(second, mc.Created_Date, mp.Created_Date))
        END AS [Standard]
    ,CASE 
        WHEN mc.Subscription_Type = 2
            THEN Sum(DATEDIFF(second, mc.Created_Date, mp.Created_Date))
        END AS Plus
    ,CASE 
        WHEN mc.Subscription_Type = 3
            THEN Sum(DATEDIFF(second, mc.Created_Date, mp.Created_Date))
        END AS Premium
FROM CTE mc
JOIN CTE mp ON mc.rn = mp.rn - 1
GROUP BY mc.Subscription_Type
</code></pre>