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 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_IDOUTER 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 ) Option1OUTER 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 ) Option2CROSS 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) ) Option3Group By a.AccNoORDER BY a.AccNo

...

with CTE as( SELECT Transaction_AccNo, Amount,Before_Amount,After_Amount, TransType_ID, Topup_ID, ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo ORDER BY Transaction_Date) as RowNum, ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo ORDER BY Transaction_Date DESC) as LastRowNum from Tbl_Transaction where convert(Date,Transaction_Date, 105) BETWEEN '2012-09-16' 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 Before_Amount else 0 end) as Open_Balance, SUM( case when LastRowNum = 1 then After_Amount else 0 end) as Close_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

...DONE!

SELECTa.AccNo,isnull(t.Total_Adjustment,0.00),t.Total_Topup,t.Total_Comm,t.Total_Transfer,t.Total_RecvTransfer,t.Total_Maxis,t.Total_Digi,t.Total_Celcom,Option3.Open_Balance,Option3.Open_Balance + t.TransactionsForPeriod AS ClosingBalanceFROM Tbl_Account a WHERE A.AccNo <> 1 <---- add at here??? ERROR ...LEFT JOIN (SELECTd.Transaction_AccNo,SUM(d.Amount) AS TransactionsForPeriod,SUM( case when d.TransType_ID = 0 then d.Amount else 0 end ) as Total_Adjustment,SUM( case when d.TransType_ID = 1 then d.Amount else 0 end ) as Total_Topup,SUM( case when d.TransType_ID = 2 then d.Amount else 0 end ) as Total_Comm,SUM( case when d.TransType_ID = 3 then d.Amount else 0 end ) as Total_Transfer,SUM( case when d.TransType_ID = 4 then d.Amount else 0 end ) as Total_RecvTransfer,SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'M' then d.Amount else 0 end ) as Total_Maxis,SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'D' then d.Amount else 0 end ) as Total_Digi,SUM( case when d.TransType_ID = 2 AND d.Topup_Company = 'C' then d.Amount else 0 end ) as Total_CelcomFROM (SELECTti.Transaction_AccNo,ti.TransType_ID,p.Topup_Company,Amount = SUM(ti.Amount)FROM Tbl_Transaction tiLEFT JOIN Tbl_Topup p ON p.Topup_ID = ti.Topup_IDwhere convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)GROUP BY ti.Transaction_AccNo, ti.TransType_ID, p.Topup_Company) d GROUP BY d.Transaction_AccNo) t ON t.Transaction_AccNo = a.AccNoOUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECTTop 1 Before_Amount as Open_BalanceFROM Tbl_Transaction tiWHERE ti.Transaction_AccNo = a.AccNo -- outer refAND convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)ORDER BY Transaction_Date) Option3ORDER BY a.AccNo

where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)

OUTER APPLY ( -- SUM of all the amounts prior to date rangeSELECTTop 1 Before_Amount as Open_BalanceFROM Tbl_Transaction tiWHERE ti.Transaction_AccNo = a.AccNo -- outer refAND convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)ORDER BY Transaction_Date) Option3

OUTER APPLY ( -- SUM of all the amounts prior to date range SELECT Top 1 Before_Amount as Open_Balance FROM Tbl_Transaction ti WHERE ti.Transaction_AccNo = a.AccNo -- outer ref AND ti.Transaction_Date < convert(Date,'16-09-2012', 105) ORDER BY Transaction_Date) Option3