Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   How to group by date (http://www.go4expert.com/forums/date-t706/)

fdtoo 12Apr2006 06:22

How to group by date
 
I have the following fields in table A:

Code:

GL_ID|GL_Name_VC|    Amount    |Period_TI|Year_SI|
===================================================
  1000|  Software|    2,000.00  |  01    |  2005
===================================================
  1000|  Software|    2,000.00  |  01    |  2005
===================================================
  1000|  Software|    5,000.00  |  02    |  2005
===================================================
  1000|  Software|    5,000.00  |  02    |  2005
==================================================
  1001|  Hardware|  10,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      | Hardware  |  Software  | Total
====================================================
Opening Balance  |    0.00  |  4,000.00  | 4,000.00
Period 02        | 10,000.00 | 10,000.00  |20,000.00
====================================================
Closing Balance  | 10,000.00 | 14,000.00  |24,000.00

The above report has 4 columns, with 1st & last column being a calculated field.
Notice that the Opening Balance description is a sum of all amount under period 01,
with software having a total Opening Balance of 4,000.00 (2,000.00 + 2,000.00).
The amount for both Hardware & software are group by period 02, and a Closing Balance
is derived by adding the sum of Period 01 & Period 02.Total column is a summation of
both Hardware & Software amount.

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

coderzone 12Apr2006 15:15

Re: How to group by date
 
Reply to How to split a field into two fields will solve this query as well.


All times are GMT +5.5. The time now is 07:58.