declare @custnmbr varchar(15)
set @custnmbr = 'myCustomer'
declare @dtDate datetime
set @dtDate = '1/1/2007'
declare @month table (month varchar(7))
while @dtDate < GETDATE() begin
insert into @month (month) values( convert(varchar(7),@dtDate,111))
set @dtDate = DATEADD(m,1,@dtDate)
end
with sales (monthYear, sales,payments, docamnt) as
(
select
convert(varchar(7),docdate,111) as monthYear,
sum(case when rmdtypal < 4 then ortrxamt else 0 end) as sales,
sum(case when rmdtypal < 4 then 0 else ortrxamt end) as payments,
sum(case when rmdtypal < 4 then ortrxamt else -ortrxamt end) as docamnt
from (
select rmdtypal, ortrxamt, docdate
from RM20101
where CUSTNMBR = @custnmbr and VOIDSTTS = 0
union all
select rmdtypal, ortrxamt, docdate
from RM30101
where CUSTNMBR = @custnmbr and VOIDSTTS = 0
) t
group by convert(varchar(7),DOCDATE,111)
)
select
m.month,
s.sales,
s.payments,
s.docamnt as net,
(select sum(docamnt) from sales where monthYear <= m.month) as totalDue
from @month m
left join sales s on s.monthYear = m.month
order by m.month