FrankK
Reputation: 47
SQL Server 2012 - Break apart a running total
In a SQL Server 2012 database I have the following table with a running total (RunAmount)
<table><TBODY>
<TR>
<TH>BUDataId</TH>
<TH>BU</TH>
<TH>AccountNum</TH>
<TH>Category</TH>
<TH>CurrAmt</TH>
<TH>BUMonth</TH>
<TH>BUYear</TH></TR>
<TR>
<TD>1</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $9,700.00 </TD>
<TD>1</TD>
<TD>2015</TD></TR>
<TR>
<TD>2</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $19,437.00 </TD>
<TD>2</TD>
<TD>2015</TD></TR>
<TR>
<TD>3</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $29,560.00 </TD>
<TD>3</TD>
<TD>2015</TD></TR>
<TR>
<TD>4</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $38,465.00 </TD>
<TD>4</TD>
<TD>2015</TD></TR>
<TR>
<TD>5</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $48,584.00 </TD>
<TD>5</TD>
<TD>2015</TD></TR>
<TR>
<TD>6</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $59,654.00 </TD>
<TD>6</TD>
<TD>2015</TD></TR>
<TR>
<TD>7</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $67,900.00 </TD>
<TD>7</TD>
<TD>2015</TD></TR>
<TR>
<TD>8</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $77,699.00 </TD>
<TD>8</TD>
<TD>2015</TD></TR>
<TR>
<TD>9</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $87,486.00 </TD>
<TD>9</TD>
<TD>2015</TD></TR>
<TR>
<TD>10</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $97,000.00 </TD>
<TD>10</TD>
<TD>2015</TD></TR>
<TR>
<TD>11</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $99,687.00 </TD>
<TD>11</TD>
<TD>2015</TD></TR>
<TR>
<TD>12</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $1,200.00 </TD>
<TD>1</TD>
<TD>2015</TD></TR>
<TR>
<TD>13</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $2,400.00 </TD>
<TD>2</TD>
<TD>2015</TD></TR>
<TR>
<TD>14</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $3,600.00 </TD>
<TD>3</TD>
<TD>2015</TD></TR>
<TR>
<TD>15</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $4,654.00 </TD>
<TD>4</TD>
<TD>2015</TD></TR>
<TR>
<TD>16</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $6,538.00 </TD>
<TD>5</TD>
<TD>2015</TD></TR>
<TR>
<TD>17</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $7,277.00 </TD>
<TD>6</TD>
<TD>2015</TD></TR>
<TR>
<TD>18</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $8,400.00 </TD>
<TD>7</TD>
<TD>2015</TD></TR>
<TR>
<TD>19</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $9,609.00 </TD>
<TD>8</TD>
<TD>2015</TD></TR>
<TR>
<TD>20</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $10,658.00 </TD>
<TD>9</TD>
<TD>2015</TD></TR>
<TR>
<TD>21</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $12,055.00 </TD>
<TD>10</TD>
<TD>2015</TD></TR>
<TR>
<TD>22</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $12,900.00 </TD>
<TD>11</TD>
<TD>2015</TD></TR></TBODY></table>I need to break apart the running amount into monthly amounts so the table will look like
<table><TBODY>
<TR>
<TH>BUDataId</TH>
<TH>BU</TH>
<TH>AccountNum</TH>
<TH>Category</TH>
<TH>CurrAmt</TH>
<TH>BUMonth</TH>
<TH>BUYear</TH>
<TH>MonthlyAmt</TH></TR>
<TR>
<TD>1</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $9,700.00 </TD>
<TD>1</TD>
<TD>2015</TD>
<TD> $9,700.00 </TD></TR>
<TR>
<TD>2</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $19,437.00 </TD>
<TD>2</TD>
<TD>2015</TD>
<TD> $9,737.00 </TD></TR>
<TR>
<TD>3</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $29,560.00 </TD>
<TD>3</TD>
<TD>2015</TD>
<TD> $10,123.00 </TD></TR>
<TR>
<TD>4</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $38,465.00 </TD>
<TD>4</TD>
<TD>2015</TD>
<TD> $8,905.00 </TD></TR>
<TR>
<TD>5</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $48,584.00 </TD>
<TD>5</TD>
<TD>2015</TD>
<TD> $10,119.00 </TD></TR>
<TR>
<TD>6</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $59,654.00 </TD>
<TD>6</TD>
<TD>2015</TD>
<TD> $11,070.00 </TD></TR>
<TR>
<TD>7</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $67,900.00 </TD>
<TD>7</TD>
<TD>2015</TD>
<TD> $8,246.00 </TD></TR>
<TR>
<TD>8</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $77,699.00 </TD>
<TD>8</TD>
<TD>2015</TD>
<TD> $9,799.00 </TD></TR>
<TR>
<TD>9</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $87,486.00 </TD>
<TD>9</TD>
<TD>2015</TD>
<TD> $9,787.00 </TD></TR>
<TR>
<TD>10</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $97,000.00 </TD>
<TD>10</TD>
<TD>2015</TD>
<TD> $9,514.00 </TD></TR>
<TR>
<TD>11</TD>
<TD>12</TD>
<TD>AA</TD>
<TD>N91</TD>
<TD> $99,687.00 </TD>
<TD>11</TD>
<TD>2015</TD>
<TD> $2,687.00 </TD></TR>
<TR>
<TD>12</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $1,200.00 </TD>
<TD>1</TD>
<TD>2015</TD>
<TD> $1,200.00 </TD></TR>
<TR>
<TD>13</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $2,400.00 </TD>
<TD>2</TD>
<TD>2015</TD>
<TD> $1,200.00 </TD></TR>
<TR>
<TD>14</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $3,600.00 </TD>
<TD>3</TD>
<TD>2015</TD>
<TD> $1,200.00 </TD></TR>
<TR>
<TD>15</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $4,654.00 </TD>
<TD>4</TD>
<TD>2015</TD>
<TD> $1,054.00 </TD></TR>
<TR>
<TD>16</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $6,538.00 </TD>
<TD>5</TD>
<TD>2015</TD>
<TD> $1,884.00 </TD></TR>
<TR>
<TD>17</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $7,277.00 </TD>
<TD>6</TD>
<TD>2015</TD>
<TD> $739.00 </TD></TR>
<TR>
<TD>18</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $8,400.00 </TD>
<TD>7</TD>
<TD>2015</TD>
<TD> $1,123.00 </TD></TR>
<TR>
<TD>19</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $9,609.00 </TD>
<TD>8</TD>
<TD>2015</TD>
<TD> $1,209.00 </TD></TR>
<TR>
<TD>20</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $10,658.00 </TD>
<TD>9</TD>
<TD>2015</TD>
<TD> $1,049.00 </TD></TR>
<TR>
<TD>21</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $12,055.00 </TD>
<TD>10</TD>
<TD>2015</TD>
<TD> $1,397.00 </TD></TR>
<TR>
<TD>22</TD>
<TD>12</TD>
<TD>BB</TD>
<TD>N51</TD>
<TD> $12,900.00 </TD>
<TD>11</TD>
<TD>2015</TD>
<TD> $845.00 </TD></TR></TBODY></table>I am sure there is a windowing function in here somewhere but not sure exactly where. Any help would be greatly appreciated
Upvotes: 1
Views: 54
Answers (2)
TheGameiswar
Reputation: 28930
This works for all versions
with cte
as
(
select *,row_number() over (partition by yearr order by id) as rn
from test1_soe
)
select t1.*,isnull(t1.runamount-t2.runamount,t1.runamount) as nxt
from
cte t1
left join
cte t2
on
t1.rn=t2.rn+1
Upvotes: 1
JamieD77
Reputation: 13949
SQL 2012 has a LAG function that can give you the previous RunAmount. Use this and subtract from the current RunAmount to get your MonthAmt
SELECT *,
[RunAmount] - ISNULL(LAG([RunAmount]) OVER (ORDER BY [Year], [Month]),0) MonthAmt
FROM MyTable
the ISNULL will give you a zero value for the first row since there is not a previous record, which would return null.
Upvotes: 2
Related Questions
- How to concatenate text from multiple rows into a single text string in SQL Server
- How to return only the Date from a SQL Server DateTime datatype
- How to add a column with a default value to an existing table in SQL Server?
- Check if table exists in SQL Server
- How do I UPDATE from a SELECT in SQL Server?
- How do I escape a single quote in SQL Server?
- How to check if a column exists in a SQL Server table
- How can I do an UPDATE statement with JOIN in SQL Server?
- LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
- How can I delete using INNER JOIN with SQL Server?