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
Rabu, 25 Januari 2012
Update subquery
update trmasterincentive set average_incentivenp=incentivenp
from trmasterincentive a
inner join (
select a.idstructure, incentive, cast(incentive*score as int) incentiveNP
from (
select idstructure, percentage, sum(incentive) incentive from _incentiveNPPercentage
group by idstructure, percentage
)a inner join trmasterincentive b on (a.idstructure=b.idstructure)
inner join mscore c on (status=10 and a.percentage between fromrange and torange)
) b on (a.idstructure=b.idstructure)
from trmasterincentive a
inner join (
select a.idstructure, incentive, cast(incentive*score as int) incentiveNP
from (
select idstructure, percentage, sum(incentive) incentive from _incentiveNPPercentage
group by idstructure, percentage
)a inner join trmasterincentive b on (a.idstructure=b.idstructure)
inner join mscore c on (status=10 and a.percentage between fromrange and torange)
) b on (a.idstructure=b.idstructure)
Senin, 23 Januari 2012
Sub Query
select * from trmasterincentive x
inner join (
select idstructure, average_incentive, fromrange, torange, score,
case when average_incentive between fromrange and torange then score
else average_incentive
end new_average_incentive
from _average_incentive a
inner join mscore b on (a.idscheme=b.idscheme and status=6 and isactive='T')
) y on (x.idstructure=y.idstructure)
inner join (
select idstructure, average_incentive, fromrange, torange, score,
case when average_incentive between fromrange and torange then score
else average_incentive
end new_average_incentive
from _average_incentive a
inner join mscore b on (a.idscheme=b.idscheme and status=6 and isactive='T')
) y on (x.idstructure=y.idstructure)
Kamis, 19 Januari 2012
Query Iseng
update TrMasterIncentive set idscheme=idscheme2 from TrMasterIncentive aaa inner join (
select *
from (
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=0 and ('2012-01-01' between c.StartDate and c.EndDate))
where idcat=10 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=1 and ('2012-01-01' between c.StartDate and c.EndDate))
where b.iscombo=1 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and ('2012-01-01' between c.StartDate and c.EndDate))
where b.idjob in(1,24) and a.idlogincentive=162
) xyz
) bbb on (aaa.idstructure=bbb.idstructure)
select *
from (
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=0 and ('2012-01-01' between c.StartDate and c.EndDate))
where idcat=10 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=1 and ('2012-01-01' between c.StartDate and c.EndDate))
where b.iscombo=1 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and ('2012-01-01' between c.StartDate and c.EndDate))
where b.idjob in(1,24) and a.idlogincentive=162
) xyz
) bbb on (aaa.idstructure=bbb.idstructure)
Langganan:
Postingan (Atom)
Blog Archive
About Me
- Koral Web
- Kami adalah web developer. Beberapa produk yang pernah kami buat antara lain website, aplikasi klinik, aplikasi apotik, aplikasi EDMS (Electronic Database Management System), Energy Consumption Management System, RKBI (Rencana Kunjungan Barang Import) dan lain-lain sesuai dengan request dari client kami. Jika Anda tertarik untuk membuat system atau aplikasi, jangan sungkan-sungkan menghubungi kami.
Bahasa Pemrogramanmu?
Nasihat
Barangsiapa capek lelah dan letihnya bukan karena Allah maka celakalah dia
Diberdayakan oleh Blogger.
Web Sunnah
Blog Archieve
- Info (3)
- Kajian (3)
- My Program (1)
- Orang Terkenal (1)
- scrip (2)
- SQL (23)
- Subquery (9)
- Trik (13)