title: Sql Server: Calculate data by each month,year and Group by something
body: <p>First of all, my table structure is something similar like this:</p>
  
  <pre><code>CREATE TABLE Testing(
         [ID]        NVARCHAR(50),
         [DATE]      DATETIME,
         [TOTAL]     INT,
         [ITEM]      NVARCHAR(50),
         [Warehouse] NVARCHAR(50)
  )ON[PRIMARY]
  </code></pre>
  
  <p>I put some sample here:</p>
  
  <pre><code>[ID] [Date]      [Total] [Item] [Warehouse]
  1    2011-04-04  400     A0001   B12
  2    2011-05-04  500     A0001   B13
  3    2011-04-30  400     A0001   B12
  4    2011-04-25  400     A0001   B13
  5    2011-06-05  600     A0001   B12
  6    2011-03-02  300     A0001   B11
  7    2011-05-28  500     A0001   B13   
  </code></pre>
  
  <p>I am trying to group by [Item] and [Warehouse] and [Date] by month as well
  For example output:</p>
  
  <p>The result should be like this </p>
  
  <pre><code>[Date]        [Total]   [Item]  [Warehouse]
  March 2011     300       A0001   B11
  April 2011     800       A0001   B12
  June 2011      500       A0001   B12
  April 2011     400       A0001   B13
  May 2011       1000      A0001   B13
  </code></pre>
  
  <p>I tried the sql something like, that i parse in month and year part to made the selection</p>
  
  <pre><code>SELECT [Item],[Warehouse],SUM(Total) AS Total
  FROM [Testing]
  WHERE Datepart(month,[Date]) = 4 AND DATEPART(year,[Date]) = 2011
  GROUP BY [Item],[Warehouse]
  </code></pre>
  
  <p>I get the expected result?Is there any way to do? i actually trying to came out a close balance for each month and year by distinct of warehouse and item? </p>
  
  <p>Sound to me its need to loop through a prefix table..
  Its that anyway to do so?</p>
  
  <p>Thanks</p>
  
  <p>Regards 
  Liangck</p>