How to create a Calculated Field

• dave-318819

  SSC-Addicted

  Points: 411

  More actions

  April 11, 2006 at 11:55 pm

  #116876

  I have the following fields in table A:

   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:

   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:

  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?

• Marshall Smith

  SSCrazy

  Points: 2041

  More actions

  April 12, 2006 at 5:53 am

  #632054

  First, I would highly recommend that you leave the "Net Asset" line to your front-end reporting solution. SQL is decidedly sub-optimal for sophisticated data display. In Crystal or suchlike, displaying this sum is trivial.

  So, otherwise, the query looks pretty basic.

  SELECT A.GL_Name_VC AS [Description], SUM(A.[Amount]) AS [Amount]

  FROM A

  GROUP BY A.GL_ID, A.Year_SI, A.Period_TI, A.GL_Name_VC

  ORDER BY A.GL_ID, A.Year_SI, A.Period_TI, A.GL_Name_VC

  Is that what you're looking for?

  If you really, really need the Net Asset line in your query, check out ROLLUP in BOL.