Get starting and ending balance, with total credit and debit transactions I have a table that contain transactions like this: 



I get the required Data by using scalar function, but it is executing very long time to get the query results. Can any one help me how to get the required data faster?

I read it can be done using a table function, but I didn't know how to or how to use a join with the same table.

For context, TransactionType 1 = debit and TransactionType 2 = credit.

Here is the function I created:

ALTER FUNCTION [dbo].[CardBalanceByDate_FN] 
(
    @studentID int ,
    @CurrancyID int,
    @BalanceDate Date
)
RETURNS decimal(18, 2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result decimal(18, 2)

    -- Add the T-SQL statements to compute the return value here

    select @Result =  ISNULL((((dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,1,@BalanceDate) + (dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,3,@BalanceDate))) )-(dbo.ProfileCardAmountByDate_FN(@studentID,@CurrancyID,2,@BalanceDate))) ,0)


    --******************************************************************************************


    -- Return the result of the function
    RETURN ISNULL(@Result,0)

END


and

ALTER FUNCTION [dbo].[PeriodTotalTransactions] 
(
    -- Add the parameters for the function here
    @studentID int,
    @CurrancyID int,
    @TransactionTypeID int,
    @StarteDate Date,
    @EndDate Date
)
RETURNS decimal(18, 2)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result decimal(18, 2)

    -- Add the T-SQL statements to compute the return value here

    select @Result =  ISNULL(SUM(TransactionAmount) ,0)
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=@studentID
    and ct.IsDeleted=0   
    and CONVERT(date, ct.TransactionDate) between @StarteDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=@CurrancyID
    and ct.TransactionTypeID=@TransactionTypeID

--******************************************************************************************


    -- Return the result of the function
    RETURN ISNULL(@Result,0)

END


and the select query is

SELECT sp.studentcode, 
       sp.studentnameen, 
       sp.flag, 
       dbo.Cardbalancebydate_fn(sp.serial, 1, '2017-12-15') 
       StartBalance, 
       dbo.Periodtotaltransactions(sp.serial, 1, 2, '2017-12-15', '2018-01-15') 
       DebitTransaction, 
( 
dbo.Periodtotaltransactions(sp.serial, 1, 1, '2017-12-15', '2018-01-15') 
+ dbo.Periodtotaltransactions(sp.serial, 1, 3, '2017-12-15', '2018-01-15') ) CreditTransaction, 
dbo.Cardbalancebydate_fn(sp.serial, 1, '2018-01-15') 
EndBalance 
FROM   student_profile sp, 
student_cards SC 
WHERE  sp.serial = sc.studentid 
AND sp.isdeleted = 0 
AND sc.isdeleted = 0 


the transaction Table is 

CREATE TABLE [dbo].[Student_Card_Transaction](
    [Serial] [int] IDENTITY(1,1) NOT NULL,
    [StudentID] [int] NOT NULL,
    [CardID] [int] NOT NULL,
    [TransactionTypeID] [int] NOT NULL,
    [CurrencyID] [int] NOT NULL,
    [TransactionAmount] [decimal](18, 2) NOT NULL,
    [TransactionDate] [datetime] NULL,
    [UserID] [int] NULL,
    [Notes] [nvarchar](2000) NULL,
    [IsDeleted] [bit] NOT NULL CONSTRAINT 
)    


and the table involved 

CREATE TABLE [dbo].[Student_Profile](
    [Serial] [int] IDENTITY(1,1) NOT NULL,
    [StudentCode] [nvarchar](20) NOT NULL,
    [StudentNameAR] [nvarchar](500) NOT NULL,
    [StudentNameEN] [nvarchar](500) NOT NULL,
    [StudentImagePath] [nvarchar](2000) NULL,
    [StudentEmail] [nvarchar](1000) NULL,
    [Flag] [int] NOT NULL,
    [IsDeleted] 
)
I solved the problem using CROSS APPLY. The performance is so much better 
the code is below:

select sp.StudentCode,sp.StudentNameEN,sp.Flag,DST.DTAmount-CST.CTAmount StartBalance,TDTrans.DTran  DreditTransactions,TCTrans.CTran Credittransactions,DET.DTAmount-CET.CTAmount EndBalance,@StartDate StartDate,@EndDate EndDate
from Student_Profile sp  cross apply 
(
    select DTAmount = ISNULL(SUM(TransactionAmount) ,0)    --Charge &amp; Refund
    FROM  dbo.Student_Card_Transaction CT 
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) &lt;  @StartDate
    and ct.CurrencyID=1
    and ct.TransactionTypeID!=2 
    and ct.StudentID=sp.Serial
)as DST cross apply 
(
    select CTAmount = ISNULL(SUM(TransactionAmount) ,0)     --orders
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) &lt;  @StartDate
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2 
    and ct.StudentID=sp.Serial
)as CST cross apply 
(
    select DTAmount = ISNULL(SUM(TransactionAmount) ,0)     
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) &lt;  DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID !=2 
    and ct.StudentID=sp.Serial
)as DET cross apply 
(
    select CTAmount = ISNULL(SUM(TransactionAmount) ,0)
    FROM  dbo.Student_Card_Transaction CT
    WHERE  ct.IsDeleted=0
    and CONVERT(date, ct.TransactionDate) &lt;  DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2 
    and ct.StudentID=sp.Serial
)as CET  cross apply 
(
select DTran =  ISNULL(SUM(TransactionAmount) ,0)    --charge &amp; Refund
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=sp.Serial
    and ct.IsDeleted=0   
    and CONVERT(date, ct.TransactionDate) between @StartDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID!=2
)as TDTrans cross apply 
(
select CTran =  ISNULL(SUM(TransactionAmount) ,0)     --order
    FROM     dbo.Student_Card_Transaction CT
    WHERE   CT.StudentID=sp.Serial
    and ct.IsDeleted=0   
    and CONVERT(date, ct.TransactionDate) between @StartDate  and DATEADD("day", 1, @EndDate)
    and ct.CurrencyID=1
    and ct.TransactionTypeID=2
)as TCTrans 
inner join  Student_Cards SC on sp.Serial=sc.StudentID
where sp.IsDeleted=0
and sc.IsDeleted=0
and sc.IsActive=1
and (sp.Flag=1)