Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   How to multiply two value from two fields (http://www.go4expert.com/forums/multiply-value-fields-t709/)

fdtoo 12Apr2006 06:25

How to multiply two value from two fields
 
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 |Period_01|Period_02 |Total_YTD
================================================
1000 | Inventory | 8,000  |  2,000  |10,000

      Percentage|  10%  |  20%    |  0
================================================
      Total    |  800  |    400  | 1,200

The Total row is calculated by multiplying the percentage row by the Inventory amount in
each Period.

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

coderzone 12Apr2006 15:12

Re: How to multiply two value from two fields
 
Code: SQL

SELECT GL_ID, GL_Name_VC,
SELECT Period_TI FROM <tablename> WHERE Period_TI = 1 AS Period_01,
SELECT Period_TI FROM <tablename> WHERE Period_TI = 1 AS Period_02,
(Period_01 + Period_02) AS Total_YTD
FROM <tablename>
GROUP BY GL_ID

Now multiplying the percentage will give you the totla row.


All times are GMT +5.5. The time now is 23:35.