1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

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