1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to create a Calculated Field

Discussion in 'SQL Server' started by fdtoo, Apr 12, 2006.

  1. fdtoo

    fdtoo New Member

    Joined:
    Apr 12, 2006
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    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 a moderator: Apr 12, 2006
  2. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    734
    Likes Received:
    37
    Trophy Points:
    0
    Isnt the liability 19,000 instead of 10,000

    Here is the query
    Code:
    Select GL_Name_VC as description, SUM(Amount) from <TableName> Group By GL_Name_VC
    
     
  3. fdtoo

    fdtoo New Member

    Joined:
    Apr 12, 2006
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    The liability that are extracted should be from Period 08 in Year 2005,
    can we query with date criteria?
     
  4. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    734
    Likes Received:
    37
    Trophy Points:
    0
    Here is the query.
    Code:
    SELECT GL_Name_VC AS description, SUM(Amount) 
    FROM <TableName> 
    WHERE Period_TI = 08
    GROUP BY GL_Name_VC
     

Share This Page