April 11, 2006 at 11:55 pm
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?
April 12, 2006 at 5:53 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply