Ilmu Sql

alter function fincentivetrade2013_sales()
returns @sales
table
(
    iddistsales int, idproduct int, year int, month int, idcovjob varchar(20), product varchar(100), outlet  varchar(300),
    salesgross float, panel float, pbf float, oth float, diskon float, salesnet float
)
as
begin
   DECLARE @trdistributorsales TABLE
        (
            year int,
            month int,
            iddistsales bigint,
            price float,
            channelcode varchar(20),
            idoutlet int,
            idchannel int,
            idproduct int,
            prins float, PrinsHNA float,
            PrinXtra float, PrinPnel float, PrinE float
        )
       
        insert into @trdistributorsales(year, month, iddistsales, idproduct, idoutlet, prins, PrinsHNA, PrinXtra, PrinPnel, PrinE)
        select year(trxdate) year, month(trxdate) month, iddistsales, idproduct, idoutlet, prins, PrinsHNA, PrinXtra, PrinPnel, PrinE from trdistributorsales
        where year(trxdate)=2013 and month(trxdate)=1
       
        insert into @sales
        select iddistsales, idproduct, year, month, idcovjob, product, outlet, sum(salesgross) salesgross, sum(panel) panel, sum(pbf) pbf, sum(subtotal) oth, sum(diskon) diskon,
        sum(subtotal) + sum(panel) + sum(pbf) + sum(diskon) salesnet
        from (
            select year, month, idcovjob, iddistsales, a.idproduct, b.name product, a.idoutlet, c.name outlet,
            subtotal salesgross,
            case when tender=1 or panel=1 or pbf=1 then 0 else subtotal end subtotal,
            case when tender=1 then 0 when panel=1 then subtotal*0.5 else 0 end panel,
            case when tender=1 then 0 when pbf=1 then subtotal*0.25 else 0 end pbf,
            case when tender=1 then 0 when panel=1 then diskon/2 when pbf=1 then diskon/4 else diskon end diskon,
            tender   
            from (
                select year, month, c.idcovjob, a.iddistsales, a.idproduct, a.idoutlet,
                subtotal,
                case when channelcode in('08','09','8A', '8B') then 1 else 0 end tender,
                case when channelcode in('04','07','10') or a.prinpnel<>0 then 1 else 0 end panel,   
                case when d.outletid is not null then 1 else 0 end pbf,
                ( (isnull(a.Prins,0)*isnull(price,0)) + isnull(a.PrinsHNA,0) + isnull(a.PrinXtra,0) + isnull(a.PrinPnel,0) + isnull(a.PrinE,0) ) diskon
                from @trdistributorsales a
                inner join trclaimsales c on (a.iddistsales=c.iddistsales)
                left join moutletincentive d on (a.idoutlet=d.outletid)
                inner join mcoverjob e on (c.idcovjob=e.idcovjob)       
                where e.idsubcat=31 --and year(a.trxdate)=2013
                and claimtype='A' and isrelease='F' --and c.idcovjob=5435 --and a.iddistsales=10587390
            ) a
            inner join mproduct b on (a.idproduct=b.idprod)
            inner join moutlet c on (a.idoutlet=c.idoutlet)
            --where tender=0
        ) a
        where year=2013 --and month=1
        group by iddistsales, idproduct, year, month, idcovjob, product, outlet       

    return
end