SELECT
a.AccNo,
SUM( case when t.TransType_ID = 0 then t.Amount else 0 end ) as Total_Adjustment,
SUM( case when t.TransType_ID = 1 then t.Amount else 0 end ) as Total_Topup,
SUM( case when t.TransType_ID = 2 then t.Amount else 0 end ) as Total_Comm,
SUM( case when t.TransType_ID = 3 then t.Amount else 0 end ) as Total_Transfer,
SUM( case when t.TransType_ID = 4 then t.Amount else 0 end ) as Total_RecvTransfer,
SUM( case when t.TransType_ID = 2 AND p.Topup_Company='M' then t.Amount else 0 end ) as Total_Maxis,
SUM( case when t.TransType_ID = 2 AND p.Topup_Company='D' then t.Amount else 0 end ) as Total_Digi,
SUM( case when t.TransType_ID = 2 AND p.Topup_Company='C' then t.Amount else 0 end ) as Total_Celcom,
Option1.Open_Balance,
Option2.Open_Balance,
Option3.Open_Balance
FROM Tbl_Account a
LEFT JOIN Tbl_Transaction t
ON a.AccNo = t.Transaction_AccNo
-- this date arithmetic should provide the same filter as the original
-- but is SARGable (use Google)
AND t.Transaction_Date > convert(Date,'16-9-2012', 105)
AND t.Transaction_Date < convert(Date,'01-10-2012', 105)
LEFT JOIN Tbl_Topup p ON t.Topup_ID = p.Topup_ID
OUTER APPLY (-- first transaction of the date range, if there is one
SELECT TOP 1 Open_Balance = ti.Amount
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date > convert(Date,'16-9-2012', 105)
AND ti.Transaction_Date < convert(Date,'01-10-2012', 105)
ORDER BY ti.Transaction_Date
) Option1
OUTER APPLY (-- largest value of a transaction, if there is one
SELECT TOP 1 Open_Balance = ti.Amount
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date > convert(Date,'16-9-2012', 105)
AND ti.Transaction_Date < convert(Date,'01-10-2012', 105)
ORDER BY ti.Amount DESC
) Option2
CROSS APPLY ( -- SUM of all the amounts prior to date range
SELECT Open_Balance = SUM(ti.Amount)
FROM Tbl_Transaction ti
WHERE ti.Transaction_AccNo = a.AccNo -- outer ref
AND ti.Transaction_Date <= convert(Date,'16-9-2012', 105)
) Option3
Group By a.AccNo
ORDER BY a.AccNo
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden