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)
Kamis, 19 Januari 2012
Query Iseng
update TrMasterIncentive set idscheme=idscheme2 from TrMasterIncentive aaa inner join (
select *
from (
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=0 and ('2012-01-01' between c.StartDate and c.EndDate))
where idcat=10 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=1 and ('2012-01-01' between c.StartDate and c.EndDate))
where b.iscombo=1 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and ('2012-01-01' between c.StartDate and c.EndDate))
where b.idjob in(1,24) and a.idlogincentive=162
) xyz
) bbb on (aaa.idstructure=bbb.idstructure)
select *
from (
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=0 and ('2012-01-01' between c.StartDate and c.EndDate))
where idcat=10 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=1 and ('2012-01-01' between c.StartDate and c.EndDate))
where b.iscombo=1 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and ('2012-01-01' between c.StartDate and c.EndDate))
where b.idjob in(1,24) and a.idlogincentive=162
) xyz
) bbb on (aaa.idstructure=bbb.idstructure)
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
Langganan:
Postingan (Atom)