April 11, 2006 at 11:56 pm
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?
April 12, 2006 at 2:29 am
declare @table1 TABLE (
GLID INT,
GL_NAME VARCHAR(100),
AMOUNT MONEY,
PERIOD TINYINT,
YEAR_SI INT
)
INSERT INTO @TABLE1
select 1000,'Sales',20000,1,2005
union all
select 1000,'Sales',10000,2,2005
union all
select 1001,'Cost',50000,1,2005
union all
select 1001,'Cost',50000,2,2005
declare @table2 TABLE (
[Description] varchar(100),
period1 int,
period2 int,
year_to_Date as period1+ period2
)
insert into @table2 ([Description])
SELECT 'Sales'
update @table2 set period1 = (select sum(amount) from @table1 where period = 1 and GL_NAME='Sales'),
period2 = (select sum(amount) from @table1 where period = 2 and GL_NAME='Sales')
where [Description]= 'Sales'
insert into @table2 ([Description])
SELECT 'Cost'
update @table2 set period1 = (select sum(amount) from @table1 where period = 1 and GL_NAME='Cost' ),
period2 = (select sum(amount) from @table1 where period = 2 and GL_NAME='Cost')
where [Description]= 'Cost'
insert into @table2 ([Description])
SELECT 'Profit'
update @table2 set period1 = (select sum(period1) from @table2 where [Description]='Sales') -
(select sum(period1) from @table2 where [Description]='Cost')
,
period2 = (select sum(period2) from @table2 where [Description]='Sales') -
(select sum(period2) from @table2 where [Description]='Cost')
WHERE [DESCRIPTION]='Profit'
SELECT [Description],period1,period2, year_to_Date from @table2 where [Description]='Sales'
union all
select 'Total Sales' ,sum(period1),sum(period2), sum(year_to_Date) from @table2 where [Description]='Sales'
union all
select [Description],period1,period2, year_to_Date from @table2 where [Description]='Cost'
union all
select 'Total Cost' ,sum(period1),sum(period2), sum(year_to_Date) from @table2 where [Description]='Cost'
union all
select [Description],period1,period2, year_to_Date from @table2 where [Description]='Profit'
-- select 'Total Cost' ,sum(period1),sum(period2), sum(year_to_Date) from @table2 where [Description]='Cost'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply