Curhat SQL akhir pekan

Wow data yang tersimpan didatabase tidaklah berguna jika tidak bisa mengelolanya . misalkan manajemen mau minta laporan detail dari tahun 2011 s/d 2012 ,diurutkan pertahun, persemester, perquartal, perbulan dan perminggu.. belum lagi master distributornya terpisah, harganya juga terpisah cuma diambil yang paling update aja, trus ada tabel discount juga per item beda2 tiap distributor
Nah loh... gimana ya querynya ??

dengan transact sql di sql server semuanya menjadi mungkin saja , berikut sedikit dari kemampuan t-sql disql server.....

select distinct cast(year(c.dtran)as varchar(4))as tahun,
cast(case
when month(c.dtran)<=6 then '1'
when month(c.dtran)>=7 then '2'
end as varchar(1))as semester,
case
when month(c.dtran)<=3 then '1'
when month(c.dtran)>3 and month(c.dtran)<=6 then '2'
when month(c.dtran)>6 and month(c.dtran)<=9 then '3'
when month(c.dtran)>9 then '4'
end as quartal,
cast(month(c.dtran)as varchar(2)) as bulan,
DATEPART(week,c.dtran) - DATEPART(week,(c.dtran-DATEPART(day,c.dtran)+1)) as minggu,
cast(day(c.dtran)as varchar(2))as tgl,
c.cflag,a.parent_area,a.cregion,a.ccompany,a.ccompanynm,c.cgroup,b.cbunit,
b.csubbnm,b.cclassnm,b.cvariantnm,c.coutlet,c.coutletnm,c.ctran,c.citem,b.citemnm,c.

cgudang,d.cpulau,
case
when c.cflag='C' then sum(c.njumlah*c.nisi/d.nisi)
when c.cflag='D' then sum(c.njumlah*c.nisi/d.nisi)*-1
end as qty,d.nharga4,e.ndisc,
case
when c.cflag='C' then    sum((c.njumlah*c.nisi/d.nisi*d.nharga4*(100-

e.ndisc))/100/1.1)
when c.cflag='D' then    sum((c.njumlah*c.nisi/d.nisi*d.nharga4*(100-

e.ndisc))/100/1.1)*-1
end as subtotal,
case
when month(c.dtran)=1 then 'jan'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=2 then 'feb'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=3 then 'mar'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=4 then 'apr'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=5 then 'may'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=6 then 'jun'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=7 then 'jul'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=8 then 'aug'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=9 then 'sep'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=10 then 'oct'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=11 then 'nov'+substring(cast(year(c.dtran)as varchar(4)),3,2)
when month(c.dtran)=12 then 'dec'+substring(cast(year(c.dtran)as varchar(4)),3,2)
end as tabel,c.csales,c.csalesnm
from distributor a ,item b,sep12 c,pricelist d,ksi_aro.dbo.discount e
where a.ccompany=c.ccompany and b.citem=c.citem and b.cbunit not in('GIMMICK','UNCLE

JACK')and c.cflag in ('C','D')
and c.citem=d.citem and c.citem=e.citem and a.ccompany=c.ccompany and

a.ccompany=e.ccompany
and d.dtanggal is null and d.cpulau=a.region and c.lbonus=0
group by

a.parent_area,a.cregion,a.ccompany,a.ccompanynm,c.cgroup,b.cbunit,b.csubbnm,c.coutle

t,c.coutletnm,c.cflag,
c.dtran,c.citem,b.citemnm,c.cgudang,d.cpulau,d.nharga4,e.ndisc,b.cclassnm,b.cvariant

nm,csales,csalesnm,c.ctran
having sum((c.njumlah*c.nisi/d.nisi*d.nharga4*(100-e.ndisc))/100/1.1)<60000
order by a.ccompanynm,b.cbunit,b.csubbnm,c.coutletnm,c.citem

selamat weekend