Rabu, 17 Agustus 2011
Panjang URL
Sekedar mo ngingetin... yang hoby ngelempar parameter di url hati2 dengan panjang urlnya... setelah saya google ternyata IE membatasi panjang URLnya 2048 karakter
Roll Up & Cube
Berikut sampel penggunaan Roll Up dan Cube untuk menganalisis data:
select customername, sum(salesvalue) sales, vsn
from invoices a
inner join customers b on (a.customerid=b.customerid)
inner join products c on (a.vsnid=c.vsnid)
where year(edate)=2011 and month(edate)=8
group by customername, vsn
select
case when grouping(customername)=1 then 'All Customer'
else customername
end customername,
case when grouping(vsn)=1 then 'All product'
else vsn
end vsn, sum(salesvalue)
from invoices a
inner join customers b on (a.customerid=b.customerid)
inner join products c on (a.vsnid=c.vsnid)
where year(edate)=2011 and month(edate)=8
group by customername, vsn
with rollup
select
case when grouping(customername)=1 then 'All Customer'
else customername
end customername,
case when grouping(vsn)=1 then 'All product'
else vsn
end vsn, sum(salesvalue)
from invoices a
inner join customers b on (a.customerid=b.customerid)
inner join products c on (a.vsnid=c.vsnid)
where year(edate)=2011 and month(edate)=8
group by customername, vsn
with cube
Selasa, 26 Juli 2011
Cek Mobile Browser
ini contoh script gwe lupa darimana untuk ngecek browser versi mobile....
http://www.ziddu.com/download/15826372/browser.txt.html
http://www.ziddu.com/download/15826372/browser.txt.html
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
Sabtu, 16 Juli 2011
Query Iseng
select * from (
select a.kodebarang, namabarang, max(tanggal) tanggal
from harga a
inner join barang b on (a.kodebarang=b.kodebarang)
group by a.kodebarang, namabarang
) x
inner join harga y on (x.kodebarang=y.kodebarang and x.tanggal=y.tanggal)
order by x.kodebarang
Rabu, 29 September 2010
Import Excel to Sql Server 2008
Berikut salah satu cara import data dari excell ke sql server 2008 dengan query:
INSERT INTO _0
SELECT customerid, customername
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\tes.xls;HDR=YES', 'select * from [Sheet1$]') AS A;
Jika dalam menjalankan query tersebut menemukan error : "SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."
Jalankan query berikut:
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Semoga bermanfaat
Koral web
INSERT INTO _0
SELECT customerid, customername
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\tes.xls;HDR=YES', 'select * from [Sheet1$]') AS A;
Jika dalam menjalankan query tersebut menemukan error : "SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."
Jalankan query berikut:
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Semoga bermanfaat
Koral web
Langganan:
Postingan (Atom)