Query : Contoh Query Fcuntion SQL Server

create function CustBalanceAsOfTransDatex ( @AsOfDate datetime, @DataAreaID varchar(10) )
returns table
as return (
select a.ACCOUNTNUM, sum(AMOUNTMST - isnull(Terbayar, 0)) as Sisa, cust.CUSTGROUP,dirp.NAME as names
from (

    select   
        ct.*,
        (select sum(SETTLEAMOUNTMST - EXCHADJUSTMENT)
            from CUSTSETTLEMENT cset
            where cset.ACCOUNTNUM = ct.ACCOUNTNUM and cset.TRANSRECID = ct.RECID
                and partition = 5637144576 and DATAAREAID = @DataAreaID
                and cset.TRANSDATE <= @AsOfDate)
                as Terbayar
    from
        (
            select VOUCHER, INVOICE, CLOSED, POSTINGPROFILE, AMOUNTMST, AMOUNTCUR, RECID, ACCOUNTNUM, TRANSDATE
            from CUSTTRANS
            where --AccountNum = 'CUST-C006' and
            partition = 5637144576 and DATAAREAID = @DataAreaID
            and TRANSDATE <= @AsOfDate
            and TransType in (
            0, --None
            2, -- Sales
            15, -- payment
            8, -- cust
            36 -- General Journal
            ) and
            POSTINGPROFILE = 'GEN' and (Closed = '1900-01-01' or Closed > @AsOfDate)
        ) ct
) a inner join CUSTTABLE cust on a.ACCOUNTNUM = cust.ACCOUNTNUM
inner join DIRPARTYTABLE dirp on cust.PARTY = dirp.RECID
where a.AMOUNTMST - isnull(a.Terbayar, 0) <> 0
group by a.ACCOUNTNUM, cust.CUSTGROUP,dirp.NAME
)
go


create function CustBalanceAsOfTransDateRinciok ( @AsOfDate date, @DataAreaID varchar(10) )
returns table
as return (
select a.ACCOUNTNUM, AMOUNTMST - isnull(Terbayar, 0) as Sisa, cust.CUSTGROUP, VOUCHER, INVOICE, TRANSDATE,dirp.NAME as names
from (

    select   
        ct.*,
        (select sum(SETTLEAMOUNTMST - EXCHADJUSTMENT)
            from CUSTSETTLEMENT cset
            where cset.ACCOUNTNUM = ct.ACCOUNTNUM and cset.TRANSRECID = ct.RECID
                and partition = 5637144576 and DATAAREAID = @DataAreaID
                and cset.TRANSDATE <= @AsOfDate)
                as Terbayar
    from
        (
            select VOUCHER, INVOICE, CLOSED, POSTINGPROFILE, AMOUNTMST, AMOUNTCUR, RECID, ACCOUNTNUM, TRANSDATE
            from CUSTTRANS
            where --AccountNum = 'CUST-C006' and
            partition = 5637144576 and DATAAREAID = @DataAreaID
            and TRANSDATE <= @AsOfDate
            and TransType in (
            0, --None
            2, -- Sales
            15, -- payment
            8, -- cust
            36 -- General Journal
            ) and
            POSTINGPROFILE = 'GEN' and (Closed = '1900-01-01' or Closed > @AsOfDate)
        ) ct
) a inner join CUSTTABLE cust on a.ACCOUNTNUM = cust.ACCOUNTNUM
inner join DIRPARTYTABLE dirp on cust.PARTY = dirp.RECID
where a.AMOUNTMST - isnull(a.Terbayar, 0) <> 0
)
go

Cara memanggil Function nya

select ACCOUNTNUM, Sisa, CUSTGROUP
from CustBalanceAsOfTransDate ('20161030','san')

select ACCOUNTNUM,names
from CustBalanceAsOfTransDateRinciok ('20161130', 'san')