Thanks,Chris. understood all.....thanks a lot(but i haven't tried the code yet)

SELECT

a.AccNo,

t.Total_Adjustment,

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 ClosingBalance

FROM Tbl_Account a

LEFT JOIN (

SELECT

d.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...

ChrisM@Work (9/20/2012)

Use ISNULL or COALESCE in the output...

ChrisM@Work (9/19/2012)

chinye2020 (9/19/2012)

ChrisM@Work (9/19/2012)

chinye2020 (9/19/2012)

Easy - but first, we need to...

ChrisM@Work (9/19/2012)

chinye2020 (9/19/2012)

Easy - but first, we need to sort out...

ChrisM@Work (9/19/2012)

That's not opening and closing balance - it's simply the amounts of the first and last transactions in the date range.

SELECT

a.AccNo,

t.Total_Adjustment,

t.Total_Topup,

t.Total_Comm,

t.Total_Transfer,

t.Total_RecvTransfer,

t.Total_Maxis,

t.Total_Digi,

t.Total_Celcom,

Option3.Open_Balance + t.TransactionsForPeriod AS ClosingBalance

FROM Tbl_Account a

LEFT...

with CTE as

(

SELECT

Transaction_AccNo,

Amount,Before_Amount,After_Amount,

...

with CTE as

(

SELECT

Transaction_AccNo,

Amount,

...

all Sum will according the TrasnType_ID and Acc No, so Result will show...

i mean select the top 1 Amount in the sql query

i totally no idea with this,pls help~!

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...

problem solved,thanks

DECLARE @Tbl_AccountTABLE(

AccNoint,

Add_By_AccNoint,

Commdecimal(18,8),

lvl nvarchar(50))

INSERT @Tbl_Account VALUES(2,0,6,'MANAGER'),(3,2,4,'SUPERVISOR'),(5,3,3,'SALE PERSON'),(6,2,4,'SUPERVISOR')

DECLARE @Tbl_Transaction TABLE(

Transaction_AccNoint,

Commdecimal(18,8),

Amountdecimal(18,8))

DECLARE @PurchasePrice decimal(18,3)

set @PurchasePrice = 10.00;

WITH Dependencies AS(

SELECT * FROM @Tbl_Account WHERE AccNo = 5 UNION ALL

SELECT t.* FROM @Tbl_Account t...

dude,unserstand?