quote:

---

desired output

NEC Number Customer Current Due 30 Due 60 Due >60
1 ABC 0-00 90-00 45-00 0-00
2 XYZ 100-00 0-00 30-00 0-00

---

create table #tblCustomer
(
	[NEC Number]		int,
	[Employee]		varchar(10),
	[Count Class]		int
)

insert into #tblCustomer
select	1, 'ABC', 1	union all
select	2, 'XYZ', 1	union all
select	3, 'MNO', 2


create table #tblEstimate
(
	[NEC Number] 		int,
	[Month]			int,
	[Year]			int,
	[Due Date]		datetime,
	[Estimated Amount]	numeric(10,2)
)

insert into #tblEstimate
select 1,  8, 2005, '15/09/2005', 300.00	union all
select 2,  8, 2005, '15/09/2005', 380.00	union all
select 1,  9, 2005, '15/10/2005', 390.00	union all
select 2,  9, 2005, '15/10/2005', 500.00	union all
select 1, 10, 2005, '15/11/2005', 410.00	union all
select 2, 10, 2005, '15/11/2005', 350.00

create table #tblPayment
(
	[Date]		datetime,
	[NEC Number]	int,
	[Month]		int,
	[Year]		int,
	[Amount]	numeric(10,2)
)

insert into #tblPayment
select '27/08/2005', 1,  8, 2005, 255.00	union all
select '22/08/2005', 2,  8, 2005, 350.00	union all
select '17/09/2005', 1,  9, 2005, 300.00	union all
select '19/09/2005', 2,  9, 2005, 300.00	union all
select '02/10/2005', 2,  9, 2005, 200.00	union all
select '20/10/2005', 1, 10, 2005, 410.00	union all
select '24/10/2005', 2, 10, 2005, 250.00


select	a.[NEC Number], c.[Employee],
	sum(case when Due <= 30              then Amount else 0 end) as [Current],
	sum(case when Due > 30 and Due <= 60 then Amount else 0 end) as [Due 30],
	sum(case when Due > 60 and Due <= 90 then Amount else 0 end) as [Due 60],
	sum(case when Due > 90               then Amount else 0 end) as [Due > 60]
from
(
	select 	p.[NEC Number], Amount, datediff(day, [Date], [Due Date]) as Due
	from	#tblPayment p inner join #tblEstimate e
		on	p.[NEC Number]	= e.[NEC Number]
		and	p.[Month]	= e.[Month]
		and	p.[Year]	= e.[Year]
) as a inner join #tblCustomer c
	on	a.[NEC Number]	= c.[NEC Number]
group by a.[NEC Number], c.[Employee]

drop table #tblCustomer
drop table #tblEstimate
drop table #tblPayment