create table #invoice
(
date datetime,
code varchar(25),
amount numeric(18,2)
)
insert into #invoice values('2009/06/01','C1',44)
insert into #invoice values('1963/09/06','C0',55)
insert into #invoice values('1963/09/06','C1',545)
insert into #invoice values('2014/06/07','C0',66)
insert into #invoice values('2014/08/04','C0',547)
declare @curdate datetime
set @curdate='2014/08/04'
select Code,sum(t.[Above 90])'Above 90',
sum(t.[30-90])'30-90',
sum(t.[below 30]) 'below 30'
from (select Code,
case
when (DATEDIFF("dd",date,@curdate)>90) then SUM(amount)
End 'Above 90' ,
case
when (DATEDIFF("dd",date,@curdate)<=90 and DATEDIFF("dd",date,@curdate)>30) then SUM(amount)
End '30-90',
case
when (DATEDIFF("dd",date,@curdate)<30) then SUM(amount)
End 'below 30'
from #invoice
group by Code,date) T
group by code
select * from #invoice
drop Table #invoice