Kamis, 13 Oktober 2011
Cetak Tanggal Dalam Sebulan
declare @month int, @year int
set @month = 2
set @year = 2009
SELECT
CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) + number
FROM master..spt_values
WHERE type = 'P'
AND (CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) + number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) )
set @month = 2
set @year = 2009
SELECT
CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) + number
FROM master..spt_values
WHERE type = 'P'
AND (CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) + number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@month AS VARCHAR) + '-01' AS DATETIME) )
Rabu, 12 Oktober 2011
Hari minggu
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fget_sunday_of_year](@year int)
RETURNS @tabel TABLE (date datetime)
AS
BEGIN
DECLARE @date datetime, @n int, @i int;
SET @i=1;
SET @date=(SELECT cast(year(cast(@year as varchar(4))) as varchar(4))+'-12-31');
SET @n=(SELECT DATEPART(wk, @date));
SET @date=(SELECT DATEADD(DAY, (7 - DATEDIFF(DAY, '17530101', DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), '19000101')) % 7) % 7, DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), '19000101'))-1 AS date)
INSERT @tabel
SELECT @date
WHILE(@i<@n-1)
BEGIN
SET @date=(SELECT @date+7);
INSERT @tabel
SELECT @date;
SET @i=@i+1;
END
RETURN;
END;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fget_sunday_of_year](@year int)
RETURNS @tabel TABLE (date datetime)
AS
BEGIN
DECLARE @date datetime, @n int, @i int;
SET @i=1;
SET @date=(SELECT cast(year(cast(@year as varchar(4))) as varchar(4))+'-12-31');
SET @n=(SELECT DATEPART(wk, @date));
SET @date=(SELECT DATEADD(DAY, (7 - DATEDIFF(DAY, '17530101', DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), '19000101')) % 7) % 7, DATEADD(YEAR, DATEDIFF(YEAR, 0, @date), '19000101'))-1 AS date)
INSERT @tabel
SELECT @date
WHILE(@i<@n-1)
BEGIN
SET @date=(SELECT @date+7);
INSERT @tabel
SELECT @date;
SET @i=@i+1;
END
RETURN;
END;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
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
Langganan:
Postingan (Atom)