How to create a Calculated Field

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|liability |  -10,000.00   |  08     |  2005
===================================================
  1001|  asset   |   20,000.00   |  08     |  2005
===================================================
  1000|liability |   -9,000.00   |  09     |  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   Amount
asset         20,000.00
liability    (10,000.00)
             =========== 
Net Asset     10,000.00
             ===========
The above report would list 2 columns as Description & Amount, next it would sort the Description
column by GL_ID, next by Year 2005 & lastly by Period 08, with a net figure of asset minus liability.

Guys, hope someone out there can help me with the sql command for the above report?

Last edited by shabbir; 12Apr2006 at 06:38.. Reason: Better display of GRID
0
coderzone's Avatar, Join Date: Jul 2004
Team Leader
Isnt the liability 19,000 instead of 10,000

Here is the query
Code: SQL
SELECT GL_Name_VC AS description, SUM(Amount) FROM <TableName> GROUP BY GL_Name_VC
0
fdtoo's Avatar, Join Date: Apr 2006
Go4Expert Member
The liability that are extracted should be from Period 08 in Year 2005,
can we query with date criteria?
0
coderzone's Avatar, Join Date: Jul 2004
Team Leader
Here is the query.
Code: SQL
SELECT GL_Name_VC AS description, SUM(Amount)
FROM <TableName>
WHERE Period_TI = 08
GROUP BY GL_Name_VC