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_AccNoFROM Tbl_TransactionGROUP 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 BalanceFROM Tbl_Account aLEFT 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.AccNoORDER 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_BalanceFROM Tbl_AccountLEFT JOIN CTE ON Tbl_Account.AccNo = CTE.Transaction_AccNoLEFT JOIN Tbl_Topup ON CTE.Topup_ID = Tbl_Topup.Topup_IDGroup By Tbl_Account.AccNoORDER BY Tbl_Account.AccNo