OMG!!! I can't believe this:pinch:

The sample data you have provided is good but not error free

I got an error for the data type number( you have number in Oracle, not SQL Server )

It would have been great if you had included the expected results too

Nevertheless, its a good start and I am glad you finally understood what we want to help you 🙂

I have given the solution as per my understanding of your issue

If the results are not what you desire, please post the desired results

declare @report table

(

account varchar(30),

trans char(30),

trans_date date,

amount numeric(18,2)

)

insert into @report(account,trans,trans_date,amount) values('E2674','Cr','02apr02',300)

insert into @report(account,trans,trans_date,amount) values('R7890','Dr','06jun11',5000)

insert into @report(account,trans,trans_date,amount) values('E9700','Cr','7feb09',5000)

insert into @report(account,trans,trans_date,amount) values('R7890','Cr','06jun11',5900)

insert into @report(account,trans,trans_date,amount) values('E9700','Dr','7feb09',8000)

SELECTaccount, DATENAME(YEAR, trans_date) Year, DATENAME(MONTH, trans_date) Month,

SUM(CASE WHEN trans = 'Cr' THEN amount ELSE amount * -1 END) amount

FROM@report

GROUP BY account, DATENAME(YEAR, trans_date), DATENAME(MONTH, trans_date)