Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   How to create a Calculated Field (http://www.go4expert.com/forums/create-calculated-field-t704/)

fdtoo 12Apr2006 06:19

How to create a Calculated Field
 
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?

coderzone 12Apr2006 06:47

Re: How to create a Calculated Field
 
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


fdtoo 12Apr2006 12:45

Re: How to create a Calculated Field
 
The liability that are extracted should be from Period 08 in Year 2005,
can we query with date criteria?

coderzone 12Apr2006 13:21

Re: How to create a Calculated Field
 
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



All times are GMT +5.5. The time now is 21:55.