ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.SQL query to SUM previous and current row in SQL Server<p>Hi <a class="username" rel="smile">smile</a>,</p> <p>Refer below test query.</p> <p><strong><span style="text-decoration: underline;">SQL</span></strong></p> <pre class="brush: sql">DECLARE @Test AS TABLE(AdmissionNo CHAR(5),Recievable INT,Arrears INT,NetBal INT,Paid INT,RemBal INT,FeesMonth CHAR(3)) INSERT INTO @Test VALUES(&#39;R-001&#39;,700,0,700,500,200,&#39;Aug&#39;) INSERT INTO @Test VALUES(&#39;R-001&#39;,0,200,200,100,100,&#39;Aug&#39;) INSERT INTO @Test VALUES(&#39;R-001&#39;,500,0,500,0,0,&#39;Sep&#39;) SELECT DISTINCT AdmissionNo, (SELECT TOP 1 CAST(RemBal AS VARCHAR(20)) FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = &#39;Aug&#39; ORDER BY t.RowNum DESC) + &#39; + &#39; + (SELECT TOP 1 CAST(NetBal AS VARCHAR(20)) FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = &#39;Sep&#39; ORDER BY t.RowNum ASC) &#39;NetBal&#39;, (SELECT TOP 1 Paid FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = &#39;Sep&#39; ORDER BY t.RowNum DESC) &#39;Paid&#39;, (SELECT TOP 1 RemBal FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = &#39;Aug&#39; ORDER BY t.RowNum DESC) + (SELECT TOP 1 NetBal FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum,* FROM @Test) t where FeesMonth = &#39;Sep&#39; ORDER BY t.RowNum ASC) &#39;RemBal&#39; FROM @Test</pre> <p><strong><span style="text-decoration: underline;">Output</span></strong></p> <table border="1"> <tbody> <tr> <td><strong>AdmissionNo</strong></td> <td><strong>NetBal</strong></td> <td><strong>Paid</strong></td> <td><strong>RemBal</strong></td> </tr> <tr> <td>R-001</td> <td>100 + 500</td> <td>0</td> <td>600</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/209094/SQL-query-to-SUM-previous-and-current-row-in-SQL-Server/https://www.aspforums.net:443/Threads/209094/SQL-query-to-SUM-previous-and-current-row-in-SQL-Server/Fri, 28 Sep 2018 04:54:04 GMT