Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   How to create a Temporary Table (http://www.go4expert.com/forums/create-temporary-table-t705/)

fdtoo 12Apr2006 06:20

How to create a Temporary Table
 
I have the following fields in table A:
Code:

GL_ID|GL_Name_VC|    Amount    |Period_TI|Year_SI|
===================================================
  1000|  Sales  |  -20,000.00  |  01    |  2005
===================================================
  1000|  Sales  |  -10,000.00  |  02    |  2005
===================================================
  1001|  Cost    |    5,000.00  |  01    |  2005
===================================================
  1001|  Cost    |    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:

Description    |  Period 01  |  Period 02 | Year to Date
=========================================================
Sales          |  20,000.00  |  10,000.00 |  30,000.00
Total Sales    |  20,000.00  |  10,000.00 |  30,000.00
   
Cost          |    5,000.00  |  5,000.00 |  10,000.00
Total Cost    |    5,000.00  |  5,000.00 |  10,000.00
=========================================================
Profit        |  15,000.00  |  5,000.00 |  20,000.00

The above report would list 4 columns, with the last column being a calculated field as a sum of
Period01 + Period02 Amount, sorted by GL_ID and group under a summation row called
Total Sales & Total Cost.There would be a net amount appearing as Profit (Total Sales-Total Cost).

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

coderzone 12Apr2006 06:52

Re: How to create a Temporary Table
 
Can you clarify on what is the difference between sales and total sales / cost and totla cost.

fdtoo 12Apr2006 12:39

Re: How to create a Temporary Table
 
The Total Sales is actually a summation of all sales made for a particular period,
vice-versa for Total Cost

coderzone 12Apr2006 15:31

Re: How to create a Temporary Table
 
How to split a field into two fields will solve your problem.


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