Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   How to create an aggregated field (http://www.go4expert.com/forums/create-aggregated-field-t708/)

fdtoo 12Apr2006 06:24

How to create an aggregated field
 
I have the following fields in table A:

Code:

GL_ID|GL_Name_VC |    Amount    |Period_TI|Year_SI
===================================================
  1000|  Inventory|  8,000.00  |  01    |  2005
===================================================
  1000|  Inventory|  -3,000.00  |  02    |  2005
===================================================
  1000|  Inventory|  5,000.00  |  02    |  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:

GL_ID  | GL_Name_VC |Op Bal|Period_Dr|Period_Cr|Period Bal|Closing Bal
======================================================================
1000  | Inventory  |8,000 |  5,000  | -3,000  |  2,000  |10,000

The above report has an Op Bal column which is the sum of all amount in Period 01 in
Year 2005 carried forward as opening balance in Period 02, Period_Dr Column would contain
all positive amount in Period 02 & Period_Cr Column would contain all negative amount
in Period 02. Period Bal is the summation of both Period_Dr & Period_Cr and Closing Bal
column is the summation of Op Bal + Period Bal.

Guys, hope someone out there can help me with the sql command for the above report?

coderzone 12Apr2006 15:24

Re: How to create an aggregated field
 
Code: sql

SELECT GL_ID, GL_Name_VC ,
SELCT SUM(Amount)  FROM <TableName> WHERE Period_TI = 1 GROUP BY GL_ID AS Op Bal,
SELCT SUM(Amount)  FROM <TableName> WHERE Period_TI = 2 AND Amount >0 GROUP BY GL_ID AS Period_Dr,
SELCT SUM(Amount)  FROM <TableName> WHERE Period_TI = 2 AND Amount <0 GROUP BY GL_ID AS Period_Cr,
(Debit + Credit) AS Period Bal, (OpBal + Period Bal) AS Closing Bal
FROM <tablename>
GROUP BY GL_ID



All times are GMT +5.5. The time now is 17:02.