Selasa, 26 Juli 2011
Query Iseng
Gara2 salah design database gwe harus buat query cukup panjang... :(
select employeeid, employeename, asmid, asmname, smid, smname, teamid, teamname, year, joindate,
sum(jan) jan, sum(feb) feb, sum(mar) mar, sum(apr) apr, sum(may) may, sum(jun) jun,
sum(jul) jul, sum(aug) aug, sum(sep) sep, sum(oct) oct, sum(nov) nov, sum(dec) dec,
sum(jan)+sum(feb)+sum(mar)+sum(apr)+sum(may)+sum(jun)+sum(jul)+sum(aug)+sum(sep)+sum(oct)+sum(nov)+sum(dec) total, region, areaname
from (
select employeeid, employeename, asmid, asmname, smid, smname, teamid, teamname, year, joindate,
case when month=1 then sum(jan) else 0 end jan,
case when month=2 then sum(feb) else 0 end feb,
case when month=3 then sum(mar) else 0 end mar,
case when month=4 then sum(apr) else 0 end apr,
case when month=5 then sum(may) else 0 end may,
case when month=6 then sum(jun) else 0 end jun,
case when month=7 then sum(jul) else 0 end jul,
case when month=8 then sum(aug) else 0 end aug,
case when month=9 then sum(sep) else 0 end sep,
case when month=10 then sum(oct) else 0 end oct,
case when month=11 then sum(nov) else 0 end nov,
case when month=12 then sum(dec) else 0 end dec,
(select distinct regionname from vstructorg x where x.tahun=yy.year and x.bulan=bulan and employeeid=psrid and x.asmid=yy.asmid) region,
(select distinct areaname from vstructorg x where x.tahun=yy.year and x.bulan=bulan and employeeid=psrid and x.asmid=yy.asmid) areaname
from jupiter_clubmember_summary yy
group by employeeid, employeename, asmid, asmname, smid, smname, teamid, teamname, month, year, joindate
) a
group by employeeid, employeename, asmid, asmname, smid, smname, teamid, teamname, year, joindate, region, areaname
select employeeid, employeename, asmid, asmname, smid, smname, teamid, teamname, year, joindate,
sum(jan) jan, sum(feb) feb, sum(mar) mar, sum(apr) apr, sum(may) may, sum(jun) jun,
sum(jul) jul, sum(aug) aug, sum(sep) sep, sum(oct) oct, sum(nov) nov, sum(dec) dec,
sum(jan)+sum(feb)+sum(mar)+sum(apr)+sum(may)+sum(jun)+sum(jul)+sum(aug)+sum(sep)+sum(oct)+sum(nov)+sum(dec) total, region, areaname
from (
select employeeid, employeename, asmid, asmname, smid, smname, teamid, teamname, year, joindate,
case when month=1 then sum(jan) else 0 end jan,
case when month=2 then sum(feb) else 0 end feb,
case when month=3 then sum(mar) else 0 end mar,
case when month=4 then sum(apr) else 0 end apr,
case when month=5 then sum(may) else 0 end may,
case when month=6 then sum(jun) else 0 end jun,
case when month=7 then sum(jul) else 0 end jul,
case when month=8 then sum(aug) else 0 end aug,
case when month=9 then sum(sep) else 0 end sep,
case when month=10 then sum(oct) else 0 end oct,
case when month=11 then sum(nov) else 0 end nov,
case when month=12 then sum(dec) else 0 end dec,
(select distinct regionname from vstructorg x where x.tahun=yy.year and x.bulan=bulan and employeeid=psrid and x.asmid=yy.asmid) region,
(select distinct areaname from vstructorg x where x.tahun=yy.year and x.bulan=bulan and employeeid=psrid and x.asmid=yy.asmid) areaname
from jupiter_clubmember_summary yy
group by employeeid, employeename, asmid, asmname, smid, smname, teamid, teamname, month, year, joindate
) a
group by employeeid, employeename, asmid, asmname, smid, smname, teamid, teamname, year, joindate, region, areaname
Langganan:
Posting Komentar (Atom)
0 comments:
Posting Komentar