How to group by date

fdtoo's Avatar, Join Date: Apr 2006
Go4Expert Member
I have the following fields in table A:

Code:
 GL_ID|GL_Name_VC|     Amount    |Period_TI|Year_SI|
===================================================
  1000|  Software|    2,000.00   |  01     |  2005
===================================================
  1000|  Software|    2,000.00   |  01     |  2005
===================================================
  1000|  Software|    5,000.00   |  02     |  2005
===================================================
  1000|  Software|    5,000.00   |  02     |  2005
==================================================
  1001|  Hardware|   10,000.00   |  02     |  2005
the fields above have the following datatype:

Code:
 Fields        | Datatype                 
 ===================================
 GL_ID         | Integer            
 GL_Name_VC    | Variable Character
 Amount        | Integer
 Period_TI     | TinyInteger
 Year_SI       | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:

Code:
Description      | Hardware  |  Software  | Total
====================================================
Opening Balance  |    0.00   |  4,000.00  | 4,000.00
Period 02        | 10,000.00 | 10,000.00  |20,000.00
====================================================
Closing Balance  | 10,000.00 | 14,000.00  |24,000.00
The above report has 4 columns, with 1st & last column being a calculated field.
Notice that the Opening Balance description is a sum of all amount under period 01,
with software having a total Opening Balance of 4,000.00 (2,000.00 + 2,000.00).
The amount for both Hardware & software are group by period 02, and a Closing Balance
is derived by adding the sum of Period 01 & Period 02.Total column is a summation of
both Hardware & Software amount.

Guys, hope someone out there can help me with the sql command for the above report?
0
coderzone's Avatar, Join Date: Jul 2004
Team Leader
Reply to How to split a field into two fields will solve this query as well.