Tampilkan postingan dengan label Subquery. Tampilkan semua postingan
Tampilkan postingan dengan label Subquery. Tampilkan semua postingan
Sabtu, 11 Februari 2012
Parsing data
update profil_dokter
set isi_profil=replace(isi_profil,
(
substring(
substring(isi_profil,instr(isi_profil,"![]()
1,
instr(substring(isi_profil,instr(isi_profil,"![]()
')
)
),"")
where isi_profil !='' and isi_profil like '%src="%'
set isi_profil=replace(isi_profil,
(
substring(
substring(isi_profil,instr(isi_profil,"
1,
instr(substring(isi_profil,instr(isi_profil,"
)
),"")
where isi_profil !='' and isi_profil like '%src="%'
Akal-akalan
create table x as
select id_dokter, substring(gambar,1,instr(gambar,'"')-1) pathgambar
from (
select id_dokter, path, substring(path,instr(path, "src")+6) gambar
from (
select id_dokter, isi_profil, gambar,
substring(isi_profil,instr(isi_profil,"![]()
from profil_dokter where isi_profil !='' and isi_profil like '%src="%'
)a
)x
select id_dokter, substring(gambar,1,instr(gambar,'"')-1) pathgambar
from (
select id_dokter, path, substring(path,instr(path, "src")+6) gambar
from (
select id_dokter, isi_profil, gambar,
substring(isi_profil,instr(isi_profil,"
from profil_dokter where isi_profil !='' and isi_profil like '%src="%'
)a
)x
inner join (
SELECT id_dokter, reverse(substring(REVERSE(pathgambar), 1, instr(REVERSE(pathgambar),'/')-1)) gambar_dokter
from x
)y on (a.id_dokter=y.id_dokter)
set gambar=gambar_dokter
Bermain-main dengan path
select id_dokter, gambar, substring(gambar,1,instr(gambar,'"')-1) pathgambar
from (
select id_dokter, path, substring(path,instr(path, "src")+6) gambar
from (
select id_dokter, isi_profil, gambar,
substring(isi_profil,instr(isi_profil,"![]()
from profil_dokter where isi_profil !='' and isi_profil like '%src="%'
)a
)x
from (
select id_dokter, path, substring(path,instr(path, "src")+6) gambar
from (
select id_dokter, isi_profil, gambar,
substring(isi_profil,instr(isi_profil,"
from profil_dokter where isi_profil !='' and isi_profil like '%src="%'
)a
)x
Senin, 06 Februari 2012
Subquery lagi
update k set incentive=(case when percentage_am=1 then k.incentive else k.incentive/2 end)
from trmasterincentive_summary k
inner join (
select x.idstructure, incentive,
case when (cast(y.jumlahAM_ach as float)) / (cast (y.JumlahAM as float)) > 0.6
then 1
else 0
end percentage_am
from trmasterincentive_summary x
inner join (
select a.idstructure,
(select COUNT(distinct idstructure_am) from structorg where year=2011 and month=11 and idcat=10 and idstructure_sm=a.idstructure)JumlahAM,
(
select COUNT(distinct idstructure_am)
from trmasterincentive_summary p
inner join structorg q on (p.idstructure=q.idstructure_am and q.idstructure_sm=a.idstructure)
where idjob in(3,29) and percentage>=90
)jumlahAM_ach
from trmasterincentive_summary a
where idlogstructure=176 and idjob in(1,24) and percentage>=90
) y on (x.idstructure=y.idstructure)
) l on (k.idstructure=l.idstructure)
from trmasterincentive_summary k
inner join (
select x.idstructure, incentive,
case when (cast(y.jumlahAM_ach as float)) / (cast (y.JumlahAM as float)) > 0.6
then 1
else 0
end percentage_am
from trmasterincentive_summary x
inner join (
select a.idstructure,
(select COUNT(distinct idstructure_am) from structorg where year=2011 and month=11 and idcat=10 and idstructure_sm=a.idstructure)JumlahAM,
(
select COUNT(distinct idstructure_am)
from trmasterincentive_summary p
inner join structorg q on (p.idstructure=q.idstructure_am and q.idstructure_sm=a.idstructure)
where idjob in(3,29) and percentage>=90
)jumlahAM_ach
from trmasterincentive_summary a
where idlogstructure=176 and idjob in(1,24) and percentage>=90
) y on (x.idstructure=y.idstructure)
) l on (k.idstructure=l.idstructure)
Rabu, 01 Februari 2012
Query Terburuk
query terburuk yang pernah gwe buat... execution timenya 30menit... :((
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)
Langganan:
Postingan (Atom)