1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to create a Temporary Table

Discussion in 'SQL Server' started by fdtoo, Apr 12, 2006.

  1. fdtoo

    fdtoo New Member

    Joined:
    Apr 12, 2006
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    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?
     
  2. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    734
    Likes Received:
    37
    Trophy Points:
    0
    Can you clarify on what is the difference between sales and total sales / cost and totla cost.
     
  3. fdtoo

    fdtoo New Member

    Joined:
    Apr 12, 2006
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    The Total Sales is actually a summation of all sales made for a particular period,
    vice-versa for Total Cost
     
  4. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    734
    Likes Received:
    37
    Trophy Points:
    0
    [thread=710]How to split a field into two fields[/thread] will solve your problem.
     

Share This Page