Senin, 30 Januari 2012
Penggunaan Sub Query untuk mengghitung incentive perkategori
select c.* from (
select idstructure, sum(salesgrosspharma1) salesgrosspharma1, sum(targetpharma1) targetpharma1, sum(PercentagePharma1)PercentagePharma1,
sum(salesgrosspharma2) salesgrosspharma2, sum(targetpharma2) targetpharma2, sum(PercentagePharma2) PercentagePharma2,
sum(salesgrosspharma3) salesgrosspharma3, sum(targetpharma3) targetpharma3, sum(PercentagePharma3) PercentagePharma3
from (
select idstructure,
case
when idsubcat=21 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end salesgrossPharma1,
case
when idsubcat=21 then (select target from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end targetPharma1,
case
when idsubcat=21 then (select percentage from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end PercentagePharma1,
case
when idsubcat=22 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end salesgrossPharma2,
case
when idsubcat=22 then (select target from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end targetPharma2,
case
when idsubcat=22 then (select percentage from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end PercentagePharma2,
case
when idsubcat=23 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end salesgrossPharma3,
case
when idsubcat=23 then (select target from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end targetPharma3,
case
when idsubcat=23 then (select percentage from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end PercentagePharma3
from TrMasterIncentiveSubCat a
)b
group by idstructure
) c inner join trrekapidcovjob d on (c.idstructure=d.idstructure)
where idlogincentive=167
select idstructure, sum(salesgrosspharma1) salesgrosspharma1, sum(targetpharma1) targetpharma1, sum(PercentagePharma1)PercentagePharma1,
sum(salesgrosspharma2) salesgrosspharma2, sum(targetpharma2) targetpharma2, sum(PercentagePharma2) PercentagePharma2,
sum(salesgrosspharma3) salesgrosspharma3, sum(targetpharma3) targetpharma3, sum(PercentagePharma3) PercentagePharma3
from (
select idstructure,
case
when idsubcat=21 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end salesgrossPharma1,
case
when idsubcat=21 then (select target from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end targetPharma1,
case
when idsubcat=21 then (select percentage from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end PercentagePharma1,
case
when idsubcat=22 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end salesgrossPharma2,
case
when idsubcat=22 then (select target from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end targetPharma2,
case
when idsubcat=22 then (select percentage from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end PercentagePharma2,
case
when idsubcat=23 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end salesgrossPharma3,
case
when idsubcat=23 then (select target from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end targetPharma3,
case
when idsubcat=23 then (select percentage from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end PercentagePharma3
from TrMasterIncentiveSubCat a
)b
group by idstructure
) c inner join trrekapidcovjob d on (c.idstructure=d.idstructure)
where idlogincentive=167
Langganan:
Posting Komentar (Atom)
0 comments:
Posting Komentar