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:
    736
    Likes Received:
    37
    Trophy Points:
    28
    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:
    736
    Likes Received:
    37
    Trophy Points:
    28
    [thread=710]How to split a field into two fields[/thread] will solve your problem.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice