Ilmu Sql

--------------------- Declare Variable -------------------
Declare @month int
declare    @year int
declare    @idlogstructure int, @idlogtarget int, @idlogsales int, @idlogproductfocus int, @idlogproduct_semitrading int, @idlogproduct_trading int, @idlogincentive int
declare @idcovjobsm varchar(100)
declare @statusemp varchar(100)
declare @BOM datetime, @firstdate datetime, @lastdate datetime
declare @idcat int
declare @UserCreated varchar(100)
declare @status_productfocus as int, @status_product_semitrading as int, @status_product_trading as int
DECLARE @start_time DATETIME, @end_time DATETIME
-----------------------------------------------------------

---------------------- Set Variable -----------------------
begin
set @year=2012
set @month=8
set @idcat =10
set @UserCreated = 'Andy Reza'
SET @BOM =  cast(cast(@month as varchar) + '/16/' + cast(@year as varchar) as datetime)
SET @firstdate =  cast(cast(@month as varchar) + '/1/' + cast(@year as varchar) as datetime)
SET @lastdate =  cast(@month as varchar) + '/' + cast(DateDiff(day,@firstdate,DateAdd(mm, 1, @firstdate)) as varchar) + '/' + cast(@year as varchar)
set @status_productfocus =201331
set @status_product_semitrading = 201341
set @status_product_trading = 201351
end
-----------------------------------------------------------

---------------------- Select id log ----------------------
begin
select @idlogstructure=IdLogIncentive from MLogIncentive where Year=@year and Month=@month and IdModule=201301 and IsDelete=0
select @idlogtarget=IdLogIncentive from MLogIncentive where Year=@year and Month=@month and IdModule=201302 and IsDelete=0
select @idlogsales=IdLogIncentive from MLogIncentive where Year=@year and Month=@month and IdModule=201303 and IsDelete=0
select @idlogproductfocus=IdLogIncentive from MLogIncentive where Year=@year and Month=@month and IdModule=201304 and IsDelete=0
select @idlogproduct_semitrading=IdLogIncentive from MLogIncentive where Year=@year and Month=@month and IdModule=201305 and IsDelete=0
select @idlogproduct_trading=IdLogIncentive from MLogIncentive where Year=@year and Month=@month and IdModule=201306 and IsDelete=0
select @idlogincentive=IdLogIncentive from MLogIncentive where Year=@year and Month=@month and IdModule=201399 and IsDelete=0
end
-----------------------------------------------------------

------------------- Delete Data Transaksi -----------------
begin
delete from TrRekapIdCovJob where idlogincentive=@idlogstructure
delete from trrekapemployee where idlogincentive=@idlogstructure
delete from TrRekapIdCovJobdetail where idlogincentive=@idlogstructure
delete from structorg where year=@year and month=@month and idcat=@idcat
delete from TrRekapTarget where IdLogIncentive=@idlogtarget
delete from TrRekapSales where IdLogIncentive=@idlogsales
delete from TrRekapProductFocus where idlogproductfocus=@idlogproductfocus
delete from TrRekapProductSemiTrading where idlogproduct_semitrading=@idlogproduct_semitrading
delete from TrRekapProductTrading where idlogproduct_trading=@idlogproduct_trading
delete from TrRekapActualCall where YEAR=@year and MONTH=@month
delete from TrRekapActiveOutlet where YEAR=@year and MONTH=@month
delete from trmasterincentive where IdLogIncentive=@idlogincentive
end
-----------------------------------------------------------

------------- Create Temp Structure Organisation ----------
begin
Print 'Start Create Temp Structure Organisation'
SET @start_time = CURRENT_TIMESTAMP
--------------------------- SM ---------------------------
select a.IdCovJob,idjob, idcov, idemp, a.IdSubCat into #smdsm
From MCoverjobhistory a
inner join msubcategory b on b.idsubcat = a.idsubcat
inner join MCovJobEmployee c on (a.IdCovJob=c.IdCovJob and @BOM between startdate and c.EndDate and c.IsActive ='T')
where idjob in(24,1) and a.isactive='T' and @bom  between activeddate and a.EndDate
and b.idcat =@idcat

insert into TrRekapIdCovJob (idlogincentive, idemp, idcov, idjob, idcovjob, id_parent, iscombo, year, month, IdSubCat, isEnter)
select distinct @idlogstructure, idemp, idcov, idjob, substring(d.idcovjob, 0, LEN(d.idcovjob)) idcovjob, 0 id_parent, case when d.idcovjob like '%;%' then 1 else 0 end iscombo, @year, @month, case when d.idcovjob like '%;%' then 0 else idsubcat end idsubcat, 1
from #smdsm a
cross apply ( select idcovjob + ';' from #smdsm b where (a.idemp=b.idemp) order by idcovjob for xml path('') ) d (idcovjob)
--------------------------- EO SM ---------------------------

--------------------------- AM SC ---------------------------
select c.IdEmp, b.IdCov, b.IdJob, b.IdCovJob, idstructure id_parent, a.IdSubCat into #amsc
from (
    select idstructure, Value idcovjob, idemp, idcov, IdSubCat
    from TrRekapIdCovJob a
    cross  apply(select  *  from  dbo.fn_split(a.idcovjob,';')) b
    where IdLogIncentive=@idlogstructure
)a inner join MCoverjobhistory b on (a.idcovjob=b.IdCovJobPar)
inner join MCovJobEmployee c on (b.IdCovJob=c.IdCovJob)
where
@BOM between b.ActivedDate and b.EndDate and b.IsActive='T'
and @BOM between c.StartDate and c.EndDate and c.IsActive='T'
and IdJob in(3,29)
order by IdEmp

insert into TrRekapIdCovJob (idlogincentive, idemp, idcov, idjob, idcovjob, id_parent, iscombo, year, month, IdSubCat, isEnter)
select distinct @idlogstructure, idemp, idcov, idjob, substring(d.idcovjob, 0, LEN(d.idcovjob)) idcovjob, id_parent, case when d.idcovjob like '%;%' then 1 else 0 end iscombo, @year, @month, case when d.idcovjob like '%;%' then 0 else idsubcat end idsubcat, 1
from #amsc a
cross apply ( select idcovjob + ';' from #amsc b where (a.idemp=b.idemp and a.id_parent=b.id_parent) order by idcovjob for xml path('') ) d (idcovjob)
order by 1

--------------------------- EO AM SC ---------------------------

-------------------------- MR -------------------------- 
select * into #mr_notvacant
from (
    select a.idstructure id_parent, a.idcovjob idcovjob_am, a.idemp idemp_am, a.idcov idcov_am, a.idjob idjob_am,
    b.IdCov IdCov_mr, b.IdJob idjob_mr, b.IdCovJob IdCovJob_mr, c.IdEmp, b.IdSubCat
    from (
        select idstructure, Value idcovjob, idemp, idcov, idjob
        from TrRekapIdCovJob a
        cross  apply(select  *  from  dbo.fn_split(a.idcovjob,';')) b
        where IdLogIncentive=@idlogstructure
    )a
    inner join MCoverjobhistory b on (a.idcovjob=b.IdCovJobPar)
    inner join MCovJobEmployee c on (b.IdCovJob=c.IdCovJob)
    where
    b.IdJob=4 and c.IdEmp<>835
    and @bom between b.ActivedDate and b.EndDate and b.IsActive='T'
    and @bom between c.StartDate and c.EndDate and c.IsActive='T'
) mr_notvacant
   
select * into #mr_vacantcombo from (
    select distinct a.idstructure id_parent, a.idcovjob idcovjob_am, a.idemp idemp_am, a.idcov idcov_am, a.idjob idjob_am,
    b.IdCov IdCov_mr, b.IdJob idjob_mr, b.IdCovJob IdCovJob_mr, c.IdEmp, b.IdSubCat
    from (
        select idstructure, Value idcovjob, idemp, idcov, idjob
        from TrRekapIdCovJob a
        cross  apply(select  *  from  dbo.fn_split(a.idcovjob,';')) b
        where IdLogIncentive=@idlogstructure
    )a
    inner join MCoverjobhistory b on (a.idcovjob=b.IdCovJobPar)
    inner join MCovJobEmployee c on (b.IdCovJob=c.IdCovJob)
    inner join MCovJobBayangan d on (b.IdCovJob=d.IdCovJob or b.IdCovJob=d.IdCovJobBayangan)
    where
    b.IdJob=4 and c.IdEmp=835
    and @bom between b.ActivedDate and b.EndDate and b.IsActive='T'
    and @bom between c.StartDate and c.EndDate and c.IsActive='T'
    and @bom between d.StartDate And d.EndDate and d.IsDeleted=0
)mr_vacantcombo

select x.* into #mr_vacantnotcombo from (
    select distinct a.idstructure id_parent, a.idcovjob idcovjob_am, a.idemp idemp_am, a.idcov idcov_am, a.idjob idjob_am,
    b.IdCov IdCov_mr, b.IdJob idjob_mr, b.IdCovJob IdCovJob_mr, c.IdEmp, b.IdSubCat
    from (
        select idstructure, Value idcovjob, idemp, idcov, idjob
        from TrRekapIdCovJob a
        cross  apply(select  *  from  dbo.fn_split(a.idcovjob,';')) b
        where IdLogIncentive=@idlogstructure
    )a
    inner join MCoverjobhistory b on (a.idcovjob=b.IdCovJobPar)
    inner join MCovJobEmployee c on (b.IdCovJob=c.IdCovJob)
    where
        b.IdJob=4 and c.IdEmp=835
        and @bom between b.ActivedDate and b.EndDate and b.IsActive='T'
        and @bom between c.StartDate and c.EndDate and c.IsActive='T'
)x left join #mr_vacantcombo y on (x.IdCovJob_mr=y.idcovjob_mr)
where y.id_parent is null

insert into TrRekapIdCovJob (idlogincentive, idemp, idcov, idjob, idcovjob, id_parent, iscombo, year, month, IdSubCat, isEnter)
select distinct @idlogstructure, idemp, idcov_mr idcov, idjob_mr idjob, substring(d.idcovjob_mr, 0, LEN(d.idcovjob_mr)) idcovjob, id_parent, case when d.idcovjob_mr like '%;%' then 1 else 0 end iscombo, @year, @month, case when d.idcovjob_mr like '%;%' then 0 else idsubcat end idsubcat, 1
from #mr_notvacant a
cross apply ( select idcovjob_mr + ';' from #mr_notvacant b where (a.idemp=b.idemp and a.id_parent=b.id_parent) order by idcovjob_mr for xml path('') ) d (idcovjob_mr)
union
select distinct @idlogstructure, idemp, idcov_mr idcov, idjob_mr idjob, substring(d.idcovjob_mr, 0, LEN(d.idcovjob_mr)) idcovjob, id_parent, case when d.idcovjob_mr like '%;%' then 1 else 0 end iscombo, @year, @month, case when d.idcovjob_mr like '%;%' then 0 else idsubcat end idsubcat, 1
from #mr_vacantcombo a
cross apply ( select idcovjob_mr + ';' from #mr_vacantcombo b where (a.idemp=b.idemp and a.id_parent=b.id_parent and a.idcov_mr=b.idcov_mr) order by idcovjob_mr for xml path('') ) d (idcovjob_mr)
union
select distinct @idlogstructure, idemp, idcov_mr idcov, idjob_mr idjob, substring(d.idcovjob_mr, 0, LEN(d.idcovjob_mr)) idcovjob, id_parent, case when d.idcovjob_mr like '%;%' then 1 else 0 end iscombo, @year, @month, case when d.idcovjob_mr like '%;%' then 0 else idsubcat end idsubcat, 1
from #mr_vacantnotcombo a
cross apply ( select idcovjob_mr + ';' from #mr_vacantnotcombo b where (a.idemp=b.idemp and a.id_parent=b.id_parent and a.idcovjob_mr=b.idcovjob_mr) order by idcovjob_mr for xml path('') ) d (idcovjob_mr)

-------------------------- EO MR --------------------------

insert into trrekapemployee(idlogincentive, idstructure, idemp, officialname, nik, joindate, assigndate, partusstartdate, partusenddate, resigndate, status, statusemp, year, month)
select idlogincentive, idstructure, b.IdEmp, OfficialName, Nik, JoinDate,
    CASE WHEN year(AssignDate)='1900' THEN NULL else AssignDate END AssignDate,
    CASE
        WHEN @bom between isnull(PartusStartDate,'1/1/2000') and isnull(PartusEndDate,'1/1/2000')
        THEN PartusStartDate
        else NULL 
    END PartusStartDate,
    CASE
        WHEN @bom between isnull(PartusStartDate,'1/1/2000') and isnull(PartusEndDate,'1/1/2000')
        THEN PartusEndDate
        else NULL 
    END PartusEndDate,
    CASE WHEN month(@bom)=month(resigndate) and year(@bom)=year(resigndate) and status in(3,4,5,6) THEN ResignDate else NULL END ResignDate,
Status,
case
    when
        @bom between isnull(joindate,'1/1/2000') and isnull(assigndate,'1/1/2000')
        or (@bom >= joindate and assigndate = '1/1/1900' )
        And Status NOT IN(3,4,5,6)
        then 'PROBATION'
    when @bom between isnull(partusStartdate,'1/1/2000') and isnull(partusenddate,'1/1/2000') then 'CUTI'   
    when month(@bom)=month(resigndate) and year(@bom)=year(resigndate) and status=3 then 'RESIGN 1 BULAN'
    when month(@bom)=month(resigndate) and year(@bom)=year(resigndate) and status=4 then 'RESIGN KURANG 1 BULAN'
    when month(@bom)=month(resigndate) and year(@bom)=year(resigndate) and status=5 then 'RESIGN PELANGGARAN'
    when month(@bom)=month(resigndate) and year(@bom)=year(resigndate) and status=6 then 'TIDAK LULUS'
    else ''   
end statusemp, @year year, @month month
from TrRekapIdCovJob a
inner join MEmployee b on (a.idemp=b.IdEmp)
where IdLogIncentive=@idlogstructure

update TrRekapIdCovJob set statusemp=b.statusemp
from TrRekapIdCovJob a
inner join trrekapemployee b on (a.IdStructure=b.idstructure)
where a.IdLogIncentive=@idlogstructure and b.idlogincentive=@idlogstructure

insert into TrRekapIdCovJobdetail(idlogincentive, idstructure, idcovjob, idemp, idjob, idcov, iscombo, id_parent, isenter, idsubcat, isdeleted, year, month)
select a.idlogincentive, a.idstructure, b.value idcovjob, a.idemp, a.idjob, a.idcov, a.iscombo, a.id_parent, a.isenter, a.idsubcat, a.isdeleted, @year, @month
From  trrekapidcovjob a
cross  apply(select  *  from  dbo.fn_split(a.idcovjob,';')) b
where idlogincentive=@idlogstructure and isdeleted=0
order by 2

insert into structorg (idstructure_sm, idstructure_am, idstructure_mr, year, month, idcat)
select distinct sm, am, mr, @year year, @month month, @idcat idcat
from (
    select a.idstructure sm, b.idstructure am, c.idstructure mr
    from trrekapidcovjob a
    inner join trrekapidcovjob b on (a.idstructure=b.id_parent and b.idjob in(3,29))
    left join trrekapidcovjob c on (b.idstructure=c.id_parent)
    where a.idjob in(1,24) and a.Idlogincentive=@idlogstructure
   
    union
   
    select a.idstructure sm, 0 am, b.idstructure mr
    from trrekapidcovjob a
    inner join trrekapidcovjob b on (a.idstructure=b.id_parent and b.idjob in(4))
    where a.idjob in(1,24) and a.Idlogincentive=@idlogstructure
) a order by 1,3

select a.IdCovJob, a.IdCovJobPar AMSC, b.IdCovJobPar GSM_DSM, a.IdSubCat, c.IdEmp into #covjob
from MCoverJobHistory a
left join MCoverJobHistory b on (a.IdCovJobPar=b.IdCovJob)
inner join MCovJobEmployee c on (a.IdCovJob=c.IdCovJob)
where @lastdate between a.ActivedDate and a.EndDate and a.IsActive='T'
and @lastdate between b.ActivedDate and b.EndDate and b.IsActive='T'
and @lastdate between c.StartDate and c.EndDate and c.IsActive='T'

select distinct amsc into #covjob_amsc from #covjob

SET @end_time = CURRENT_TIMESTAMP
print ''
print '______________________________________________'
print 'Execute time Create Temp Structure Organisation : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' second'
print '______________________________________________'
print ''
-------------------------- EO Create Temp Structure Organisation -------------------------
end
-----------------------------------------------------------

-------------------------- TARGET -------------------------
begin
Print 'Start Generate Rekap Target'
SET @start_time = CURRENT_TIMESTAMP
insert into TrRekapTarget(IdLogIncentive, IdProd, IdCat, IdSubCat, IdCovJob, GSM_DSM, AM_SC,
    Target, Quantity, Price, IsDeleted, UserCreated, DateCreated)

select @idlogtarget, a.IdProd, d.IdCat, b.IdSubCat, a.IdCovJob, gsm_dsm, amsc, a.Amount target, a.Quantity, a.Price, 0, @UserCreated, GETDATE()
FROM MTargetDetail a
LEFT JOIN MProductToSubCategory b ON (b.IdProd = a.IdProd        and ((@lastdate between b.StartDate and b.EndDate) or b.idprod in(410, 411)))
LEFT JOIN MSubCategory c ON (c.IdSubCat = b.IdSubCat AND c.IsActive = 'T')
LEFT JOIN MCategory d ON (d.IdCat = c.IdCat AND d.IsActive = 'T')
left join #covjob e on (a.IdCovJob=e.idcovjob)
where Year=@year and Month=@month    and d.IdCat=10--44728
and a.Quantity>0 and gsm_dsm is not null
--order by 1,2,3,4

SET @end_time = CURRENT_TIMESTAMP
print ''
print '______________________________________________'
print 'Execute time Generate Rekap Target : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' second'
print '______________________________________________'
print ''
end
-----------------------------------------------------------

-------------------------- SALES --------------------------
begin
Print '------------- Start Generate Rekap Sales -------------'
SET @start_time = CURRENT_TIMESTAMP
select IdDistSales, IdProduct, a.Quantity, a.Total, a.Price, a.IdOutlet into #sales
FROM TrDistributorSales a where YEAR(trxdate)=@year and MONTH(trxdate)=@month
order by IdDistSales desc

INSERT INTO TrRekapSales(IdLogIncentive,IdProd,IdCat,IdSubCat,IdCovJob,GSM_DSM,AM_SC,Sales_Gross,Quantity,Prins,PrinsHNA,Prine,PrinXtra,PrinPNel,IsDeleted, DateCreated,UserCreated,IdEmp,price)
select @idlogsales, a.idproduct, c.IdCat, b.IdSubCat, d.IdCovJob, gsm_dsm, amsc, d.Subtotal Sales_Gross, d.Quantity, d.PRINS, d.PRINSHNA, d.PRINE, d.PRINXTRA, d.PRINPNEL, 0 isdeleted, GETDATE(), @UserCreated, e.IdEmp, a.price
from #sales a
LEFT JOIN MProductToSubCategory b ON (a.idproduct=b.IdProd AND (@lastdate between b.StartDate AND b.EndDate))
LEFT JOIN MSubCategory c ON c.IdSubCat = b.IdSubCat
inner join TrClaimSales d on (a.iddistsales=d.IdDistSales)
left join #covjob e on (d.IdCovJob=e.idcovjob)
where b.IsActive = 'T' AND c.IsActive = 'T' AND c.IdCat = 10
and IsRelease = 'F' and ClaimType = 'A'

SET @end_time = CURRENT_TIMESTAMP
print ''
print '______________________________________________'
print 'Execute time Generate Rekap Sales : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' detik, ' + cast(DATEDIFF(mi, @start_time, @end_time) as varchar(1000)) + ' menit'
print '______________________________________________'
print ''
end
-----------------------------------------------------------

----------------------- PRODUCT FOCUS ---------------------
begin
Print 'Start Generate Rekap Product Focus'
SET @start_time = CURRENT_TIMESTAMP
------------------------------ MR --------------------------------
select b.idcovjob, total sales, b.Quantity, IdProd into #sales_productfocus_mr
from #sales a
inner join TrClaimSales b on (a.iddistsales=b.IdDistSales)
inner join MScore c on (a.idproduct=c.IdProd)
inner join #covjob d on (b.IdCovJob=d.idcovjob)
where IsRelease='F' and ClaimType='A' and c.Status=@status_productfocus

select a.IdCovJob, a.Amount Target, quantity, a.IdProd into #target_productfocus_mr
from MTargetDetail a
inner join MScore b on (a.IdProd=b.IdProd)
inner join #covjob c on (a.IdCovJob=c.idcovjob)
where Year=2012 and Month=8 and b.Status=@status_productfocus
and gsm_dsm is not null

------------------------------ AM --------------------------------
select b.idcovjob, total sales, b.Quantity, IdProd into #sales_productfocus_am
from #sales a
inner join TrClaimSales b on (a.iddistsales=b.IdDistSales)
inner join MScore c on (a.idproduct=c.IdProd)
inner join #covjob_amsc d on (b.IdCovJob=d.amsc)
where IsRelease='F' and ClaimType='A' and c.Status=@status_productfocus

select a.IdCovJob, a.Amount Target, quantity, a.IdProd into #target_productfocus_am
from MTargetDetail a
inner join MScore b on (a.IdProd=b.IdProd)
inner join #covjob_amsc c on (a.IdCovJob=c.amsc)
where Year=2012 and Month=8 and b.Status=@status_productfocus
and a.Quantity>0
----------------------- Gabungin MR - AMSC ------------------------
insert into TrRekapProductFocus(idlogproductfocus, idcovjob, idprod, sales, target, ach)
select @idlogproductfocus idlogproductfocus, a.idcovjob, a.idprod, isnull(SUM(sales),0) sales, isnull(SUM(Target),0) target,
case when isnull(SUM(Target),0)=0 then 0 else isnull(SUM(sales),0)/isnull(SUM(Target),0)*100 end ach
from #sales_productfocus_mr a
left join #target_productfocus_mr b on (a.idcovjob=b.idcovjob and a.idprod=b.idprod)
group by a.idcovjob, a.idprod
union
select @idlogproductfocus idlogproductfocus, a.idcovjob, a.idprod, isnull(SUM(sales),0) sales, isnull(SUM(Target),0) target,
case when isnull(SUM(Target),0)=0 then 0 else isnull(SUM(sales),0)/isnull(SUM(Target),0)*100 end ach
from #sales_productfocus_am a
left join #target_productfocus_am b on (a.idcovjob=b.idcovjob and a.idprod=b.idprod)
group by a.idcovjob, a.idprod

SET @end_time = CURRENT_TIMESTAMP
print ''
print '______________________________________________'
print 'Execute time Generate Rekap Product Focus : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' second'
print '______________________________________________'
print ''
end
-----------------------------------------------------------

----------------------- SEMI TRADING ----------------------
begin
Print 'Start Generate Rekap Product Semi Trading'
SET @start_time = CURRENT_TIMESTAMP
--------------------------------- MR --------------------------------
select b.idcovjob, total sales, b.Quantity, IdProd into #sales_product_semitrading_mr
from #sales a
inner join TrClaimSales b on (a.iddistsales=b.IdDistSales)
inner join MScore c on (a.idproduct=c.IdProd)
inner join #covjob d on (b.IdCovJob=d.idcovjob)
where IsRelease='F' and ClaimType='A' and c.Status=@status_product_semitrading--201341

select a.IdCovJob, a.Amount Target, quantity, a.IdProd into #target_product_semitrading_mr
from MTargetDetail a
inner join MScore b on (a.IdProd=b.IdProd)
inner join #covjob c on (a.IdCovJob=c.idcovjob)
where Year=@year and Month=@month and b.Status=@status_product_semitrading--201341
and gsm_dsm is not null
------------------------------ AM --------------------------------
select b.idcovjob, total sales, b.Quantity, IdProd into #sales_product_semitrading_am
from #sales a
inner join TrClaimSales b on (a.iddistsales=b.IdDistSales)
inner join MScore c on (a.idproduct=c.IdProd)
inner join #covjob_amsc d on (b.IdCovJob=d.amsc)
where IsRelease='F' and ClaimType='A' and c.Status=@status_product_semitrading--201341

select a.IdCovJob, a.Amount Target, quantity, a.IdProd into #target_product_semitrading_am
from MTargetDetail a
inner join MScore b on (a.IdProd=b.IdProd)
inner join #covjob_amsc c on (a.IdCovJob=c.amsc)
where Year=@year and Month=@month and b.Status=@status_product_semitrading--201341
and a.Quantity>0

----------------------- Gabungin MR - AMSC ------------------------
insert into TrRekapProductSemiTrading(idlogproduct_semitrading, idcovjob, idprod, sales, target, ach)
select @idlogproduct_semitrading idlogproduct_semitrading, a.idcovjob, a.idprod, isnull(SUM(sales),0) sales, isnull(SUM(Target),0) target,
case when isnull(SUM(Target),0)=0 then 0 else isnull(SUM(sales),0)/isnull(SUM(Target),0)*100 end ach
from #sales_product_semitrading_mr a
left join #target_product_semitrading_mr b on (a.idcovjob=b.idcovjob and a.idprod=b.idprod)
group by a.idcovjob, a.idprod
union
select @idlogproduct_semitrading idlogproduct_semitrading, a.idcovjob, a.idprod, isnull(SUM(sales),0) sales, isnull(SUM(Target),0) target,
case when isnull(SUM(Target),0)=0 then 0 else isnull(SUM(sales),0)/isnull(SUM(Target),0)*100 end ach
from #sales_product_semitrading_am a
left join #target_product_semitrading_am b on (a.idcovjob=b.idcovjob and a.idprod=b.idprod)
group by a.idcovjob, a.idprod

SET @end_time = CURRENT_TIMESTAMP
print ''
print '______________________________________________'
print 'Execute time Generate Rekap Product Semi Trading : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' second'
print '______________________________________________'
print ''
end
-----------------------------------------------------------

------------------------- TRADING -------------------------
begin
Print 'Start Generate Rekap Product Trading'
SET @start_time = CURRENT_TIMESTAMP
--------------------------------- MR --------------------------------
select b.idcovjob, total sales, b.Quantity, IdProd into #sales_product_trading_mr
from #sales a
inner join TrClaimSales b on (a.iddistsales=b.IdDistSales)
inner join MScore c on (a.idproduct=c.IdProd)
inner join #covjob d on (b.IdCovJob=d.idcovjob)
where IsRelease='F' and ClaimType='A' and c.Status=@status_product_trading--201351

select a.IdCovJob, a.Amount Target, quantity, a.IdProd into #target_product_trading_mr
from MTargetDetail a
inner join MScore b on (a.IdProd=b.IdProd)
inner join #covjob c on (a.IdCovJob=c.idcovjob)
where Year=@year and Month=@month and b.Status=@status_product_trading--201351
and gsm_dsm is not null

------------------------------ AM --------------------------------
select b.idcovjob, total sales, b.Quantity, IdProd into #sales_product_trading_am
from #sales a
inner join TrClaimSales b on (a.iddistsales=b.IdDistSales)
inner join MScore c on (a.idproduct=c.IdProd)
inner join #covjob_amsc d on (b.IdCovJob=d.amsc)
where IsRelease='F' and ClaimType='A' and c.Status=@status_product_trading--201351

select a.IdCovJob, a.Amount Target, quantity, a.IdProd into #target_product_trading_am
from MTargetDetail a
inner join MScore b on (a.IdProd=b.IdProd)
inner join #covjob_amsc c on (a.IdCovJob=c.amsc)
where Year=@year and Month=@month and b.Status=@status_product_semitrading--201351
and a.Quantity>0

----------------------- Gabungin MR - AMSC ------------------------
insert into TrRekapProductTrading(idlogproduct_trading, idcovjob, idprod, sales, target, ach)
select /*@idlogproduct_trading*/ 732 idlogproduct_trading, a.idcovjob, a.idprod, isnull(SUM(sales),0) sales, isnull(SUM(Target),0) target,
case when isnull(SUM(Target),0)=0 then 0 else isnull(SUM(sales),0)/isnull(SUM(Target),0)*100 end ach
from #sales_product_trading_mr a
left join #target_product_trading_mr b on (a.idcovjob=b.idcovjob and a.idprod=b.idprod)
group by a.idcovjob, a.idprod
union
select /*@idlogproduct_trading*/ 732 idlogproduct_trading, a.idcovjob, a.idprod, isnull(SUM(sales),0) sales, isnull(SUM(Target),0) target,
case when isnull(SUM(Target),0)=0 then 0 else isnull(SUM(sales),0)/isnull(SUM(Target),0)*100 end ach
from #sales_product_trading_am a
left join #target_product_trading_am b on (a.idcovjob=b.idcovjob and a.idprod=b.idprod)
group by a.idcovjob, a.idprod

SET @end_time = CURRENT_TIMESTAMP
print ''
print '______________________________________________'
print 'Execute time Generate Rekap Product Trading : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' second'
print '______________________________________________'
print ''
end
-----------------------------------------------------------

----------------------- Actual Call -----------------------
begin
Print 'Start Generate Rekap Actual Call'
SET @start_time = CURRENT_TIMESTAMP
insert into TrRekapActualCall(year, month, idcovjob, totalplan, actual, remaining)
select @year year, @month month, idcovjob, SUM(totalplan) totalplan, sum(actual) actual, SUM(remaining) remaining
from (
    select IdCovJob, COUNT(idcall) totalplan, 0 actual, 0 remaining
    from TrCallPlan where YEAR(calldate)=@year and MONTH(calldate)=@month and IsDeleted=0 --and CallDate='2012-08-15 00:00:00.000' and IdCovJob=5352   
    group by IdCovJob
    union
    select IdCovJob, 0 totalplan, COUNT(idcall) actual, 0 remaining
    from TrCallPlan where YEAR(calldate)=@year and MONTH(calldate)=@month and IsDeleted=0 --and CallDate='2012-08-15 00:00:00.000' and IdCovJob=5352
    and status=3
    group by IdCovJob
    union
    select IdCovJob, 0 totalplan, 0 actual, COUNT(idcall) remaining
    from TrCallPlan where YEAR(calldate)=@year and MONTH(calldate)=@month and IsDeleted=0 --and CallDate='2012-08-15 00:00:00.000' and IdCovJob=5352
    and status=1
    group by IdCovJob
)x group by IdCovJob

SET @end_time = CURRENT_TIMESTAMP
print ''
print '______________________________________________'
print 'Execute time Generate Rekap Actual Call : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' detik, ' + cast(DATEDIFF(mi, @start_time, @end_time) as varchar(1000)) + ' menit'
print '______________________________________________'
print ''
end
-----------------------------------------------------------

---------------------- Active Outlet ----------------------
begin
insert into TrRekapActiveOutlet(year, month, idcovjob, jumlahoutlet, targetAO, achAO)
select x.year, x.month, x.IdCovJob, jumlahoutlet, y.ActiveOutlet targetAO, (jumlahoutlet/y.ActiveOutlet)*100 achAO
from (
    select @year year, @month month,
    b.IdCovJob, count(idoutlet) jumlahoutlet
    from #sales a
    inner join TrClaimSales b on (a.iddistsales=b.IdDistSales)
    where total>0
    group by b.IdCovJob
)x inner join MTargetAO y on (x.IdCovJob=y.IdCovJob and x.year=y.Year and x.month=y.Month)

SET @end_time = CURRENT_TIMESTAMP
print ''
print '______________________________________________'
print 'Execute time Generate Rekap Actual Call : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' detik, ' + cast(DATEDIFF(mi, @start_time, @end_time) as varchar(1000)) + ' menit'
print '______________________________________________'
print ''
end
-----------------------------------------------------------

-------------- Generate Sales, Target, Ach ----------------
begin
Print 'Start Generate Sales, Target, Ach'
SET @start_time = CURRENT_TIMESTAMP

select GSM_DSM, AM_SC, a.IdCovJob, IdEmp, IdSubCat, a.IdProd, Quantity, Sales_Gross, Prins, PrinsHNA, PrinXtra, Prine, price, PrinPnel into #salescovjob
from trrekapsales a
left join mscore b on (a.idprod=b.idprod and status=8 and @lastdate between Startdate and Enddate)--Product yang ngga dijual lagi
where IdLogIncentive=@idlogsales and b.status is null--@idlogsales

select GSM_DSM, AM_SC, a.IdCovJob, IdSubCat, a.IdProd, Quantity, Target, price into #targetcovjob
from TrRekapTarget a
left join mscore b on (a.idprod=b.idprod and status=8 and @lastdate between Startdate and Enddate)--Product yang ngga dijual lagi
where IdLogIncentive=@idlogtarget and b.status is null--@idlogsales

-------------------------- MR --------------------------
select idstructure, a.idcovjob, sum(sales_gross) sales_gross into #salescovjob_mr
from #salescovjob a
inner join TrRekapIdCovJobdetail b on (a.idcovjob=b.idcovjob)
where b.idlogincentive=@idlogstructure --and a.idcovjob in('4932','4945','4958')
group by idstructure, a.idcovjob

select idstructure, a.idcovjob, sum(target) target into #targetcovjob_mr
from #targetcovjob a
inner join TrRekapIdCovJobdetail b on (a.idcovjob=b.idcovjob)
where b.idlogincentive=@idlogstructure --and a.idcovjob in('4932','4945','4958')
group by idstructure, a.idcovjob

select idstructure, a.idcovjob, SUM(Prins*isnull(price,0)) + sum(PrinsHNA) + sum(PrinXtra) + sum(PrinPnel) + sum(PrinE) discount into #discountcovjob_mr
from #salescovjob a
inner join TrRekapIdCovJobdetail b on (a.idcovjob=b.idcovjob)
where b.idlogincentive=@idlogstructure --and a.idcovjob in('4932','4945','4958')
group by idstructure, a.idcovjob

-------------------------- AMSC --------------------------
select idstructure, a.am_sc, sum(sales_gross) sales_gross into #salescovjob_am
from #salescovjob a
inner join TrRekapIdCovJobdetail b on (a.am_sc=b.idcovjob)
where b.idlogincentive=@idlogstructure --and a.idcovjob in('4932','4945','4958')
group by idstructure, a.am_sc

select idstructure, a.am_sc, sum(target) target into #targetcovjob_am
from #targetcovjob a
inner join TrRekapIdCovJobdetail b on (a.am_sc=b.idcovjob)
where b.idlogincentive=@idlogstructure --and a.idcovjob in('4932','4945','4958')
group by idstructure, a.am_sc

select idstructure, a.am_sc, SUM(Prins*isnull(price,0)) + sum(PrinsHNA) + sum(PrinXtra) + sum(PrinPnel) + sum(PrinE) discount into #discountcovjob_am
from #salescovjob a
inner join TrRekapIdCovJobdetail b on (a.am_sc=b.idcovjob)
where b.idlogincentive=@idlogstructure --and a.idcovjob in('4932','4945','4958')
group by idstructure, a.am_sc

-------------------------- GSM DSM --------------------------
select idstructure, a.gsm_dsm, sum(sales_gross) sales_gross into #salescovjob_gsm
from #salescovjob a
inner join TrRekapIdCovJobdetail b on (a.gsm_dsm=b.idcovjob)
where b.idlogincentive=@idlogstructure --and a.idcovjob in('4932','4945','4958')
group by idstructure, a.gsm_dsm

select idstructure, a.gsm_dsm, sum(target) target into #targetcovjob_gsm
from #targetcovjob a
inner join TrRekapIdCovJobdetail b on (a.gsm_dsm=b.idcovjob)
where b.idlogincentive=@idlogstructure --and a.idcovjob in('4932','4945','4958')
group by idstructure, a.gsm_dsm

select idstructure, a.gsm_dsm, SUM(Prins*isnull(price,0)) + sum(PrinsHNA) + sum(PrinXtra) + sum(PrinPnel) + sum(PrinE) discount into #discountcovjob_gsm
from #salescovjob a
inner join TrRekapIdCovJobdetail b on (a.gsm_dsm=b.idcovjob)
where b.idlogincentive=@idlogstructure --and a.idcovjob in('4932','4945','4958')
group by idstructure, a.gsm_dsm

------------------------ Gabungin Semuanya ------------------------
insert into trmasterincentive (idstructure, idlogincentive, idlogsales, idlogtarget, salesgross, target, SalesNet, percentage)
select a.idstructure, @idlogincentive idlogincentive, @idlogsales idlogsales, @idlogtarget idlogtarget,
sum(sales_gross) sales_gross, sum(target) target, sum(sales_gross) + sum(discount) salesnet, (sum(sales_gross)/sum(target))*100 percentage
from trrekapidcovjobdetail a
left join #salescovjob_mr b on (a.idcovjob=b.idcovjob)
left join #targetcovjob_mr c on (a.idcovjob=c.idcovjob)
left join #discountcovjob_mr d on (a.idcovjob=d.idcovjob)
where a.idlogincentive=@idlogstructure and idjob=4 group by a.idstructure

union all

select a.idstructure, @idlogincentive idlogincentive, @idlogsales idlogsales, @idlogtarget idlogtarget,
sum(sales_gross) sales_gross, sum(target) target, sum(sales_gross) + sum(discount) salesnet, (sum(sales_gross)/sum(target))*100 percentage
from trrekapidcovjobdetail a
left join #salescovjob_am b on (a.idcovjob=b.am_sc)
left join #targetcovjob_am c on (a.idcovjob=c.am_sc)
left join #discountcovjob_am d on (a.idcovjob=d.am_sc)
where a.idlogincentive=@idlogstructure and idjob in(3,29) group by a.idstructure--, OfficialName

union all

select idstructure, @idlogincentive idlogincentive, @idlogsales idlogsales, @idlogtarget idlogtarget,
sales_gross, target, sales_gross + discount sales_net, (sales_gross / target)*100 percentage
from (
    select a.idstructure,
        (select sum(sales_gross) from #salescovjob_gsm where idstructure=a.idstructure) sales_gross,
        (select sum(target) from #targetcovjob_gsm where idstructure=a.idstructure) target,
        (select sum(discount) from #discountcovjob_gsm where idstructure=a.idstructure) discount
    from TrRekapIdCovJobdetail a
    where idjob in(1,24) and idlogincentive=@idlogstructure
    group by a.idstructure
)x

SET @end_time = CURRENT_TIMESTAMP
print ''
print '______________________________________________'
print 'Execute time Generate Rekap Actual Call : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' detik, ' + cast(DATEDIFF(mi, @start_time, @end_time) as varchar(1000)) + ' menit'
print '______________________________________________'
print ''
end

-------------------- Incentive Sales ----------------------
begin
Print 'Start Hitung Incentive Sales'
SET @start_time = CURRENT_TIMESTAMP

select a.IdStructure, b.OfficialName, SalesGross, SalesNet, Target, Percentage ach, d.Point bobot, e.Score pengkali, (d.Point/100)*e.Score IncentiveSales into #incentiveSales
from trmasterincentive a
inner join trrekapemployee b on (a.IdStructure=b.idstructure)
inner join TrRekapIdCovJob c on (a.IdStructure=c.IdStructure)
inner join MScore d on (d.Status=201311)--Get Bobot : SALES
inner join MScore e on (e.Status=201321 and c.IdJob=e.IdConfig and a.Percentage between e.FromRange and e.ToRange)-- Get Score
where a.IdLogIncentive=@idlogincentive

insert into #incentiveSales
select a.IdStructure, b.OfficialName, a.SalesGross, a.SalesNet, a.Target, a.Percentage ach, d.Point bobot, 0 pengkali, 0 incentiveSales
from trmasterincentive a
inner join trrekapemployee b on (a.IdStructure=b.idstructure)
inner join MScore d on (d.Status=201311)--Get Bobot : SALES
left join #incentiveSales e on (a.IdStructure=e.idstructure)
where a.IdLogIncentive=@idlogincentive and e.idstructure is null

SET @end_time = CURRENT_TIMESTAMP
print ''
print '______________________________________________'
print 'Execute time Hitung Incentive Sales : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' detik, ' + cast(DATEDIFF(mi, @start_time, @end_time) as varchar(1000)) + ' menit'
print '______________________________________________'
print ''
end
-----------------------------------------------------------

----------------- Incentive Product Focus -----------------
begin
Print 'Start Hitung Incentive Product Focus'
SET @start_time = CURRENT_TIMESTAMP

select a.idstructure, b.OfficialName, sales, target, ach, c.point bobot, (c.Point/100)*d.Score incentiveProductFocus into #incentiveProductFocus
from (
    select idstructure, idjob, isnull(sum(sales),0) sales, isnull(sum(target),0) target, case when isnull(sum(target),0)=0 then 0 else (SUM(sales)/SUM(target))*100 end ach
    from TrRekapIdCovJobdetail a
    left join TrRekapProductFocus b on (a.idcovjob=b.idcovjob)
    where idlogincentive=@idlogstructure
    group by idstructure, idjob
) a
inner join trrekapemployee b on (a.idstructure=b.idstructure)
inner join MScore c on (c.Status=201312)--Get Bobot : PRODUCT FOCUS
inner join MScore d on (d.Status=201321 and a.IdJob=d.IdConfig and a.ach between d.FromRange and d.ToRange)-- Get Score

--select * from mscore where status=201321

insert into #incentiveProductFocus
select a.idstructure, b.OfficialName, a.sales, a.target, a.ach, c.Point bobot, 0 incentiveProductFocus
from (
    select idstructure, idjob, isnull(sum(sales),0) sales, isnull(sum(target),0) target, case when isnull(sum(target),0)=0 then 0 else (SUM(sales)/SUM(target))*100 end ach
    from TrRekapIdCovJobdetail a
    left join TrRekapProductFocus b on (a.idcovjob=b.idcovjob)
    where idlogincentive=@idlogstructure
    group by idstructure, idjob
)a
inner join trrekapemployee b on (a.idstructure=b.idstructure)
inner join MScore c on (c.Status=201312)--Get Bobot : PRODUCT FOCUS
left join #incentiveProductFocus d on (a.idstructure=d.idstructure)
where d.idstructure is null

SET @end_time = CURRENT_TIMESTAMP
print ''
print '________________________________________________________'
print 'Execute time Hitung Incentive Product Focus : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' detik, ' + cast(DATEDIFF(mi, @start_time, @end_time) as varchar(1000)) + ' menit'
print '________________________________________________________'
print ''

end
-----------------------------------------------------------

------------- Incentive Product Semi Trading --------------
begin
Print 'Start Hitung Incentive Product Semi Trading'
SET @start_time = CURRENT_TIMESTAMP

select a.idstructure, b.OfficialName, sales, target, ach, c.point bobot, (c.Point/100)*d.Score incentiveProductSemiTrading into #incentiveProductSemiTrading
from (
    select idstructure, idjob, isnull(sum(sales),0) sales, isnull(sum(target),0) target, case when isnull(sum(target),0)=0 then 0 else (SUM(sales)/SUM(target))*100 end ach
    from TrRekapIdCovJobdetail a
    left join trrekapproductsemitrading b on (a.idcovjob=b.idcovjob)
    where idlogincentive=@idlogstructure
    group by idstructure, idjob
) a
inner join trrekapemployee b on (a.idstructure=b.idstructure)
inner join MScore c on (c.Status=201313)--Get Bobot : SEMI TRADING
inner join MScore d on (d.Status=201321 and a.IdJob=d.IdConfig and a.ach between d.FromRange and d.ToRange)-- Get Score

--select * from mscore where status=201321

insert into #incentiveProductSemiTrading
select a.idstructure, b.OfficialName, a.sales, a.target, a.ach, c.Point bobot, 0 incentiveProductSemiTrading
from (
    select idstructure, idjob, isnull(sum(sales),0) sales, isnull(sum(target),0) target, case when isnull(sum(target),0)=0 then 0 else (SUM(sales)/SUM(target))*100 end ach
    from TrRekapIdCovJobdetail a
    left join trrekapproductsemitrading b on (a.idcovjob=b.idcovjob)
    where idlogincentive=@idlogstructure
    group by idstructure, idjob
)a
inner join trrekapemployee b on (a.idstructure=b.idstructure)
inner join MScore c on (c.Status=201313)--Get Bobot : SEMI TRADING
left join #incentiveProductSemiTrading d on (a.idstructure=d.idstructure)
where d.idstructure is null

SET @end_time = CURRENT_TIMESTAMP
print ''
print '__________________________________________________________'
print 'Execute time Hitung Incentive Product Semi Trading : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' detik, ' + cast(DATEDIFF(mi, @start_time, @end_time) as varchar(1000)) + ' menit'
print '__________________________________________________________'
print ''

end
-----------------------------------------------------------

--------------- Incentive Product Trading -----------------
begin
Print 'Start Hitung Incentive Product Trading'
SET @start_time = CURRENT_TIMESTAMP

select a.idstructure, b.OfficialName, sales, target, ach, c.point bobot, (c.Point/100)*d.Score incentiveProductTrading into #incentiveProductTrading
from (
    select idstructure, idjob, isnull(sum(sales),0) sales, isnull(sum(target),0) target, case when isnull(sum(target),0)=0 then 0 else (SUM(sales)/SUM(target))*100 end ach
    from TrRekapIdCovJobdetail a
    left join trrekapproducttrading b on (a.idcovjob=b.idcovjob)
    where idlogincentive=@idlogstructure
    group by idstructure, idjob
) a
inner join trrekapemployee b on (a.idstructure=b.idstructure)
inner join MScore c on (c.Status=201314)--Get Bobot : TRADING
inner join MScore d on (d.Status=201321 and a.IdJob=d.IdConfig and a.ach between d.FromRange and d.ToRange)-- Get Score

insert into #incentiveProductTrading
select a.idstructure, b.OfficialName, a.sales, a.target, a.ach, c.Point bobot, 0 incentiveProductTrading
from (
    select idstructure, idjob, isnull(sum(sales),0) sales, isnull(sum(target),0) target, case when isnull(sum(target),0)=0 then 0 else (SUM(sales)/SUM(target))*100 end ach
    from TrRekapIdCovJobdetail a
    left join trrekapproducttrading b on (a.idcovjob=b.idcovjob)
    where idlogincentive=@idlogstructure
    group by idstructure, idjob
)a
inner join trrekapemployee b on (a.idstructure=b.idstructure)
inner join MScore c on (c.Status=201314)--Get Bobot : TRADING
left join #incentiveProductTrading d on (a.idstructure=d.idstructure)
where d.idstructure is null

SET @end_time = CURRENT_TIMESTAMP
print ''
print '__________________________________________________________'
print 'Execute time Hitung Incentive Product Trading : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' detik, ' + cast(DATEDIFF(mi, @start_time, @end_time) as varchar(1000)) + ' menit'
print '__________________________________________________________'
print ''

end
-----------------------------------------------------------

---------------- Incentive Active Outlet ------------------
begin
Print 'Start Hitung Incentive Active Outlet'
SET @start_time = CURRENT_TIMESTAMP

select a.idstructure, b.OfficialName, jumlahoutlet, targetAO, achAO, c.point bobot, (c.Point/100)*d.Score incentiveActiveOutlet into #incentiveActiveOutlet
from (
    select idstructure, idjob, isnull(sum(jumlahoutlet),0) jumlahoutlet, isnull(sum(targetAO),0) targetAO, sum(isnull(achAO,0)) achAO
    from TrRekapIdCovJobdetail a
    left join TrRekapActiveOutlet b on (a.idcovjob=b.idcovjob)
    where idlogincentive=@idlogstructure
    group by idstructure, idjob
) a
inner join trrekapemployee b on (a.idstructure=b.idstructure)
inner join MScore c on (c.Status=201315)--Get Bobot : ACTIVE OUTLET
inner join MScore d on (d.Status=201321 and a.IdJob=d.IdConfig and a.achAO between d.FromRange and d.ToRange)-- Get Score

insert into #incentiveActiveOutlet
select a.idstructure, b.OfficialName, a.jumlahoutlet, a.targetAO, a.achAO, c.Point bobot, 0 incentiveActiveOutlet
from (
    select idstructure, idjob, isnull(sum(jumlahoutlet),0) jumlahoutlet, isnull(sum(targetAO),0) targetAO, isnull(sum(achAO),0) achAO
    from TrRekapIdCovJobdetail a
    left join TrRekapActiveOutlet b on (a.idcovjob=b.idcovjob)
    where idlogincentive=@idlogstructure
    group by idstructure, idjob
)a
inner join trrekapemployee b on (a.idstructure=b.idstructure)
inner join MScore c on (c.Status=201312)--Get Bobot Sales
left join #incentiveActiveOutlet d on (a.idstructure=d.idstructure)
where d.idstructure is null

SET @end_time = CURRENT_TIMESTAMP
print ''
print '__________________________________________________________'
print 'Execute time Hitung Incentive Active Outlet : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' detik, ' + cast(DATEDIFF(mi, @start_time, @end_time) as varchar(1000)) + ' menit'
print '__________________________________________________________'
print ''

end
-----------------------------------------------------------

----------------- Incentive Actual Call--------------------
begin
Print 'Start Hitung Incentive Actual Call'
SET @start_time = CURRENT_TIMESTAMP

select a.idstructure, b.OfficialName, actualCall, targetTotalPlan, achCall, c.point bobot, (c.Point/100)*d.Score incentiveCall into #incentiveActualCall
from (
    select idstructure, idjob, isnull(sum(actual),0) actualCall, isnull(sum(totalplan),0) targetTotalPlan,
    case when sum(isnull(totalplan,0))=0 then 0 else (cast(sum(isnull(actual,0)) as float) / cast(sum(isnull(totalplan,0)) as float))*100 end achCall
    from TrRekapIdCovJobdetail a
    left join TrRekapActualCall b on (a.idcovjob=b.idcovjob)
    where idlogincentive=@idlogstructure
    group by idstructure, idjob
) a
inner join trrekapemployee b on (a.idstructure=b.idstructure)
inner join MScore c on (c.Status=201316)--Get Bobot : ACTUAL CALL
inner join MScore d on (d.Status=201321 and a.IdJob=d.IdConfig and a.achCall between d.FromRange and d.ToRange)-- Get Score

insert into #incentiveActualCall
select a.idstructure, b.OfficialName, a.actualCall, a.targetTotalPlan, a.achCall, c.Point bobot, 0 incentiveActiveOutlet
from (
    select idstructure, idjob, isnull(sum(actual),0) actualCall, isnull(sum(totalplan),0) targetTotalPlan,
    case when sum(isnull(totalplan,0))=0 then 0 else (cast(sum(isnull(actual,0)) as float) / cast(sum(isnull(totalplan,0)) as float))*100 end achCall
    from TrRekapIdCovJobdetail a
    left join TrRekapActualCall b on (a.idcovjob=b.idcovjob)
    where idlogincentive=@idlogstructure
    group by idstructure, idjob
)a
inner join trrekapemployee b on (a.idstructure=b.idstructure)
inner join MScore c on (c.Status=201316)--Get Bobot : ACTUAL CALL
left join #incentiveActualCall d on (a.idstructure=d.idstructure)
where d.idstructure is null

SET @end_time = CURRENT_TIMESTAMP
print ''
print '__________________________________________________________'
print 'Execute time Hitung Incentive Actual Call : ' + cast(DATEDIFF(ss, @start_time, @end_time) as varchar(1000))+ ' detik, ' + cast(DATEDIFF(mi, @start_time, @end_time) as varchar(1000)) + ' menit'
print '__________________________________________________________'
print ''

end
-----------------------------------------------------------

------------------ Hitung All Incentive -------------------
begin   
   
    select b.officialname, a.idcovjob, salesgross, salesnet, b.target, b.ach, IncentiveSales,
    c.sales SalesProductFocus, c.target TargetProductFocus, c.ach AchProductFocus, incentiveProductFocus,
    d.sales SalesProductSemiTrading, d.target TargetProductSemiTrading, d.ach AchProductSemiTrading, incentiveProductSemiTrading,
    e.sales SalesProductTrading, e.target TargetProductTrading, e.ach AchProductTrading, incentiveProductTrading,
    f.jumlahoutlet jumlahoutlet, f.targetAO TargetAO, f.achAO AchAO, incentiveActiveOutlet,
    g.actualCall, g.TargetTotalPlan, g.achCall, incentiveCall
    from TrRekapIdCovJob a
    left join #incentiveSales b on (a.IdStructure=b.idstructure)
    left join #incentiveProductFocus c on (a.IdStructure=c.idstructure)
    left join #incentiveProductSemiTrading d on (a.IdStructure=d.idstructure)
    left join #incentiveProductTrading e on (a.IdStructure=e.idstructure)
    left join #incentiveActiveOutlet f on (a.IdStructure=f.idstructure)
    left join #incentiveActualCall g on (a.IdStructure=g.idstructure)
    where IdLogIncentive=@idlogstructure
   
end

------------------- Drop Temporary Tabel ------------------
--begin

drop table #smdsm
drop table #amsc
drop table #mr_notvacant
drop table #mr_vacantcombo
drop table #mr_vacantnotcombo
drop table #covjob
drop table #covjob_amsc
drop table #sales
drop table #sales_productfocus_mr
drop table #target_productfocus_mr
drop table #sales_productfocus_am
drop table #target_productfocus_am
drop table #sales_product_semitrading_mr
drop table #target_product_semitrading_mr
drop table #sales_product_semitrading_am
drop table #target_product_semitrading_am
drop table #sales_product_trading_mr
drop table #target_product_trading_mr
drop table #sales_product_trading_am
drop table #target_product_trading_am
drop table #salescovjob
drop table #targetcovjob
drop table #salescovjob_mr
drop table #targetcovjob_mr
drop table #discountcovjob_mr
drop table #salescovjob_am
drop table #targetcovjob_am
drop table #discountcovjob_am
drop table #salescovjob_gsm
drop table #targetcovjob_gsm
drop table #discountcovjob_gsm
drop table #incentiveSales
drop table #incentiveProductFocus
drop table #incentiveProductSemiTrading
drop table #incentiveProductTrading
drop table #incentiveActiveOutlet
drop table #incentiveActualCall

--end

Berikut contoh simpel penggunaan replace di sql server:

select REPLACE
(
    path,
    (select path from MStructureOrganisation where id=(select parentid from MStructureOrganisation where id=128)),
    (select path from MStructureOrganisation where id=192)
)
from MStructureOrganisation where path like '0;0000375;0001099;0001099;0000542%'

WITH n(IdCovJob, idemp, startdate, enddate) AS 
(
    select idcovjob, idemp, startdate, enddate
    from MCovjobemployee --where IdEmp='1576'
    union all
    SELECT x.idCovJob, n.idemp, x.activeddate, x.enddate
    FROM MCoverJob x WITH (NOLOCK), n
    WHERE n.IdCovJob = x.IdCovJobPar
)
insert into TempEmpCovjob (IdCovJob, idemp, startdate, enddate)
select * from (
    select * from n
)x

SELECT * FROM sysmessages where msglangid=1033

Misalnya kita punya data:
123,345,678
234,567,789

dan kita ingin menampilkan data
123
345
678
234
567
789

Berikut script sederhana untuk men-split data.

select requester, b.Value approver, settingapproval_step from (
    select requestby_posid requester, approvedby_posid approver, settingapproval_step
    FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').anugerah_prod.dbo.vw_request_approval a
    where requestapproval_id=3 and requestby_posid=15351 and requestby_posid not in
    (
        select position_id FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').anugerah_prod.dbo.thrmposition where position_parentpath like '%11361%'
    ) and approvedby_posid not in( cast (14120 as varchar(100)) )--HR Officer   
) a
    cross apply
    (
        select  *  from  dbo.fn_split(a.approver, ',')
    ) b
    inner join (select * from OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').anugerah_prod.dbo.vw_employee_info) c on (b.Value=c.position_id)
order by settingapproval_step

USE [ssreport]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_Split]    Script Date: 08/29/2012 10:54:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[fn_Split](
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)

--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT
if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter

--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1

--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)
insert into @ValueTable ( [Value]) Values (@NextString)
set @String = substring(@String,@pos +1,len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end

return
end

GO

select a.budgetid, budget, b.totalcost, amount,
case when c.budgetid is null then b.totalcost else b.totalcost+amount end x
from vbudgetcapex a
inner join (
    select budgetid, SUM(totalcost) totalcost from trcapexdetail    a
    inner join TrCapexHeader b on (a.capexid=b.capexid)
    where a.capexid=85
    group by budgetid
) b on (a.budgetid=b.budgetid)
left join [vcapex_pemakaianbudget] c on (a.budgetid=c.budgetid)
where budget < (case when c.budgetid is null then b.totalcost else b.totalcost+amount end)

select distinct a.tahun, a.bulan, a.tanggal, userid from (
    select distinct year(logtime) tahun, month(logtime) bulan, day(logtime) tanggal
    from ngac_log
    where year(logtime)=2012 and month(logtime)=5
)a left join (
    select distinct userid, year(logtime) tahun, month(logtime) bulan, day(logtime) tanggal
    from ngac_log
    where year(logtime)=2012 and month(logtime)=5 and userid='002412430000000'
) b on (a.tahun=b.tahun and a.bulan=b.bulan and a.tanggal=b.tanggal)
order by 3

show full fields from database.nama_tabel

show full tables from nama_tabel where table_type = 'BASE TABLE'

select VERSION()

tutorial yang bagus bagi yang ingin mempelajari Partition Table di MySQL
http://www.slideshare.net/datacharmer/mysql-partitions-tutorial

Berikut adalah sampel melakukan query data dengan menggunakan query dengan data di server yang berbeda...


:)

Berikut adalah sedikit tip untuk membuat copy table dengan struktur yang sama .

Mungkin sebagian dari kita pernah mengalami masalah ketika menyimpan struktur tabel (Gambar 1). Cara yang dilakukan ialah dengan mengklik Tools > option > Designer >Uncheck pada prevent saving ...

Perintah berikut digunakan untuk mematikan function auto increment
set IDENTITY_INSERT nama_tabel off;

Berikut sedikit trik untuk merandom data yang akan diupdate. Misalnya saya punya table city seperti pada gambar 1 saya juga mempunyai table customer yang citynya masih blank. Saya ingin mengupdate data city yang ada di customer dengan table city yang saya punya secara random.

Query yang saya gunakan:

update x set cityid=y.cityid
from customers x
inner join (
    select id, CAST( RAND() * id / RAND() as int) cityid
    from customers a
    left join cities b on (a.cityid=b.cityid)
)y on (x.id=y.id)
where y.cityid>5 and y.cityid<482 and x.cityid is null

dengan 5 adalah id terendah dari city dan 482 adalah id tertinggi dari city

Berikut adalah contoh memparsing data.

Berikut adalah gambar tutorial dari mulai membuat database, membuat tabel sampai membuat schema database