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?
The Total Sales is actually a summation of all sales made for a particular period, vice-versa for Total Cost