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_funding2011 2 35000 42000 2 80000 85000 6 190000 2100002012 2 18000 22000 2 50000 60000 6 143000 1650002013 2 42000 55000 2 45000 50000 6 162000 188000
can somebody help me in writing this query.Thanks,
Pivot table data and get sum for each activity.
Posted - 2013-09-08 : 15:49:53
