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

DONE !, this is select Top 1 Amount,But how to select the LAST Amount as Closing_Balance?