I have the following fields in table A:

 GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
================================================== =
  1000| Sales_HW| -20,000.00 | 01 | 2005
================================================== =
  1000| Sales_SW| -10,000.00 | 01 | 2005
================================================== =
  1001| Cost_HW | 5,000.00 | 01 | 2005
================================================== =
  1001| Cost_SW | 5,000.00 | 01 | 2005

the fields above have the following datatype:

 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:

Sales Category | Sales | Cost | Profit
=================================================
     HW |-20,000.00 |5,000.00| -15,000.00
     SW |-10,000.00 |5,000.00| -5,000.00
=================================================
    Total |-30,000.00 |10,000.00|-20,000.00


 The above report have 4 columns, with last column being a calculated field (Sales-Cost)

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

Hi there,

You're not asking us to do your homework, are you? You have posted a number of questions that all look like homework to me.

I suggest you Google a bit, and look in the SQL Server 2000 Books Online....


Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi!

You case is really strange. But, I have tried the insert statement that will create a Temporary Table 'Sales_rep'.

Sales_rep
=========
Category varchar(10)
Sales int ,
Cost int
Profit int

------------------------------------------------------
insert into #sales_rep
select 'HW' , (select amount from sales where GL_Name_VC like 'Sales_HW'),
(select amount from sales where GL_Name_VC like 'Cost_HW') ,
(select sum(amount) from sales where GL_Name_VC like 'Sales_HW' or GL_Name_VC like 'Cost_HW')
union
select 'SW' , (select amount from sales where GL_Name_VC like 'Sales_SW'),
(select amount from sales where GL_Name_VC like 'Cost_SW') ,
(select sum(amount) from sales where GL_Name_VC like 'Sales_SW' or GL_Name_VC like 'Cost_SW')
union
select 'Total' ,
(select sum(amount) from sales where GL_name_VC like 'Sales%') ,
(select sum(amount) from sales where GL_Name_VC like 'Cost%'),
(select sum(amount) from sales)

-------------------------------------------------------

Although, I am not sure with the schema what you have. But, I think this will work well in your case. Reply if this helps.


- Som Dutt