Temporary Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Temporary Table

Author

Topic

fdtoo
Starting Member

28 Posts

Posted - 2006-04-11 : 20:12:26

I have the following fields in table A:

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:

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:

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?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-11 : 21:08:31

Can't you do this in your front end reporting tool ?

Sales | 20,000.00 | 10,000.00 | 30,000.00
Cost | 5,000.00 | 5,000.00 | 10,000.00

This should give you the above result you want.


select GL_Name_VC,
       sum(case when Period_TI = '01' then Amount else 0 end) as [Period 01],
       sum(case when Period_TI = '02' then Amount else 0 end) as [Period 02],
       sum(Amount)                                            as [Year to Date]
from   tableA
group by GL_Name_VC

You should be able to work out the rest base on the above query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-12 : 04:26:39

Which reporting too are you using?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources