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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Pivot table data and get sum for each activity.

Author  Topic 

shahid00704
Starting Member

11 Posts

Posted - 2013-09-08 : 15:49:53
Hello all,

i am trying to get a query but not getting how to write. I have 2 tables namely activity and guarantee and its columns and data will be like this.

create table #activity
(
ID int,
Activity varchar(50)
)

insert into #activity (ID,Activity)
select 1,'Industry' union all
select 2,'Construction' union all
select 3,'commerce' union all
select 4,'Tourism and recreation' union all
select 5,'Transport, storage and cooling' union all
select 6,'Food industry'

select * from #activity

drop table #activity

create table #guarantee
(
Guarantee_no int,
ActivityID int,
Approved_gurantee_amount int,
Approved_funding_amount int,
date_gurantee datetime
)

insert into #guarantee
select 100,1,20000,22000,'2011-10-05' union all
select 101,1,15000,20000,'2011-08-05' union all
select 102,2,30000,35000,'2011-09-04' union all
select 103,2,50000,50000,'2011-10-10' union all
select 104,3,15000,18000,'2011-10-15' union all
select 105,3,60000,65000,'2011-10-20' union all

select 100,1,10000,12000,'2012-10-05' union all
select 101,1,8000,10000,'2012-08-05' union all
select 102,2,20000,20000,'2012-09-04' union all
select 103,2,30000,40000,'2012-10-10' union all
select 104,3,15000,18000,'2012-10-15' union all
select 105,3,60000,65000,'2012-10-20' union all

select 100,1,12000,15000,'2013-01-05' union all
select 101,1,30000,40000,'2013-01-05' union all
select 102,2,15000,20000,'2013-03-04' union all
select 103,2,30000,30000,'2013-03-10' union all
select 104,3,15000,18000,'2013-05-15' union all
select 105,3,60000,65000,'2013-06-20'

select * from #guarantee

drop table #guarantee




from my page i will pass only year.

here in example if i send 2011 then i should get data for 2011,2012 and current year 2013 separated by each activity data. activityid is foreign key in my guarantee table. i want to calculate each activity data yearly. the result will be something like this.


Year industry construction .... remaining activities.... Total
No.of gurantees approved_gurantees approved_funding No.of gurantees approved_gurantees approved_funding No.of gurantees approved_gurantees approved_funding
2011 2 35000 42000 2 80000 85000 6 190000 210000
2012 2 18000 22000 2 50000 60000 6 143000 165000
2013 2 42000 55000 2 45000 50000 6 162000 188000


can somebody help me in writing this query.

Thanks,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-05 : 06:28:19
No. Multidimensional Report data can not be produced in SQL. Use a reporting tool to achieve the result.

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -