# 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 doubt anyone could help you on "how to get the required data much faster", if you don't show them the query you're currently using. Also consider providing DDL for that table (possibly including indexes), sample data and maybe a description of the meaning of the columns. May 1, 2018 at 12:38
• @doaa - take a look at how to create an MCVE - a minimally complete verifiable example. Add the appropriate `CREATE TABLE` statements, sample data, and desired result to your question with the edit link below your question. May 1, 2018 at 13:07
• Thanks for adding those details to your question! Can you script the tables involved and add the `CREATE TABLE` statements to your question? May 1, 2018 at 14:35
• why question is upvoted so many times when nobody is satisfied and question is incomplete.Main problem appear to be using 3 UDF.write UDF logic in cross apply or sub query.Beside few condition are not SARgable. May 2, 2018 at 8:19

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 & Refund
FROM  dbo.Student_Card_Transaction CT
WHERE  ct.IsDeleted=0
and CONVERT(date, ct.TransactionDate) <  @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) <  @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) <  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) <  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 & 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)
``````