Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
madlo
Starting Member
41 Posts |
 Posted - 2014-09-26 : 10:25:44
|
| I have a stock transaction table and I need to retrospectively calculate the closing balance per month and show the months as columns.Table StockStockCode,StockName001,Chair002,TableTable StockTransTransId,StockCode,Quantity,TransDate1.001,100,2014/06/012.001,200,2014/06/023.001,50,2014/07/154.002,50,2014/09/10etcDesired Output of the report (show all the months containing the sum Quantity of transactions for that month as columns)Columns: StockCode. June 2014, July 2014, August 2014, Sept 2014Rows Example:001,300, 50, 0, 0002,0, 0, 0, 50 |
|
|
Big_R
Starting Member
7 Posts |
Posted - 2014-09-26 : 11:57:28
|
| In SQL Server 2008, you will either need to use a cursor or something called a "quirky update". http://dba.stackexchange.com/questions/19507/running-total-with-countThe quirky update method is somewhat controversial because it is not supported by Microsoft and may not work in future versions of SQL Server. I myself have used it to successfully process over 140 million rows of inventory transactions, using SQL Server 2012, and found that it was actually faster than using the new 2012 functions. The thing to keep in mind with the quirky update is that updates are performed in order of the clustered index on the table, so you will most likely need to populate a temp table and then create a clustered index on it that matches the order of your running total calculation.Big_R |
 |
|
|
|
|
|
|
|
Running Total query with columns generated