SELECT SUM(CASE WHEN TransType_ID = 0 THEN AMOUNT ELSE null END) SumAmount_TransType_ID_0
	, SUM(CASE WHEN TransType_ID = 2 THEN AMOUNT ELSE null END) SumAmount_TransType_ID_2
	, SUM(CASE WHEN TransType_ID = 3 THEN AMOUNT ELSE null END) SumAmount_TransType_ID_3
	, Transaction_AccNo
FROM Tbl_Transaction
GROUP BY Transaction_AccNo

SELECT 
	a.AccNo,
	SUM( case when t.TransType_ID = 0 then a.Amount else 0 end ) as Total_Adjustment,
	SUM( case when t.TransType_ID = 1 then a.Amount else 0 end ) as Total_Topup,
	SUM( case when t.TransType_ID = 2 then a.Amount else 0 end ) as Total_Comm,
	SUM( case when t.TransType_ID = 3 then a.Amount else 0 end ) as Total_Transfer,
	SUM( case when t.TransType_ID = 4 then a.Amount else 0 end ) as Total_RecvTransfer,
	SUM( case when t.TransType_ID = 2 AND p.Topup_Company='M' then a.Amount else 0 end ) as Total_Maxis,
	SUM( case when t.TransType_ID = 2 AND p.Topup_Company='D' then a.Amount else 0 end ) as Total_Digi,
	SUM( case when t.TransType_ID = 2 AND p.Topup_Company='C' then a.Amount else 0 end ) as Total_Celcom--,
	--(SELECT Top 1 Amount as Open_Balance)<<---how to do this at here?i want to get the first Amount as Opening 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 
Group By a.AccNo
ORDER BY a.AccNo

with CTE as
(
    SELECT
        Transaction_AccNo,
        Amount,
        TransType_ID,
        Topup_ID,
        ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo
                          ORDER BY convert(Date,Transaction_Date, 105)) as RowNum
    from Tbl_Transaction
    where convert(Date,Transaction_Date, 105) BETWEEN '2012-09-1' AND '2012-09-30'
)

select
    Tbl_Account.AccNo,
    SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment,
    SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup,
    SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm,
    SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer,
    SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer,
    SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis,
    SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi,
    SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom,
    SUM( case when RowNum = 1 then Amount else 0 end) as Open_Balance
FROM Tbl_Account
LEFT JOIN CTE ON Tbl_Account.AccNo = CTE.Transaction_AccNo
LEFT JOIN Tbl_Topup ON CTE.Topup_ID = Tbl_Topup.Topup_ID
Group By Tbl_Account.AccNo
ORDER BY Tbl_Account.AccNo