create procedure create_ledger1
	@account_no int
as
set nocount on
declare @charges table(account_no int, fixcharges_key int, date_charged smalldatetime, amount_charged smallmoney, advance bit, past_due bit)
declare @payments table (account_no int, fixcharges_key int, date_paid smalldatetime, amount_paid smallmoney)
declare @monthlycharges table(account_no int, fixcharges_key int, amount smallmoney)
declare @subscribers table(account_no int, date_installed smalldatetime)

insert into @subscribers 
select 1,'10/10/2005'
/*
select account_no, date_installed from tblsubscribers where account_no = @account_no
*/

insert into @monthlycharges 
select 1,19,150 union all select 1, 23,200

/*
select account_no, mc.fixcharges_key,
 case when  mc.rate = 0 then fc.amount else mc.rate end * coalesce(nullif(mc.quantity,0),1) as amount_charged
from tblmonthlycharges mc inner join tblfixcharges fc on mc.fixcharges_key = fc.fixcharges_key 
where account_no =@account_no
*/

insert into @charges (account_no ,fixcharges_key,  date_charged , amount_charged, past_due )
select 1, 19, '10/20/2005', 1000,0

/*
select account_no,fixcharges_key, [date], rate * coalesce(nullif(quantity,0),1) , past_due from tblcharges where account_no = @account_no
  */

insert into @payments
select 1, 19, '11/15/2005', 1050
union all select 1,19, '12/23/2005',350	
union all select 1,19, '1/23/2006',350	
union all select 1,19, '2/23/2006',350	

/*
select account_no, fixcharges_key, date_paid, amount_paid from tblpayments p
	inner join tblpaymentdetails pd on pd.or_no = p.or_no
where account_no = @account_no
*/

 
select account_no, fixcharges_key, date_charged, amount_charged, date_paid 
	, case when C > 0	and B < D	and B <= C	and A <= D then  a -c
		when C <= 0	and B >= D	and B > C	and A > D then d
		when C <= 0	and B >= D	and B > C	and A <= D then a
		when C > 0	and B < D	and B > C	and A <= D then a -c
		when C > 0	and B < D	and B <= C	and A > D then b
	else b
	end as amount_paid

	 from
	(select x1.account_no, x1.fixcharges_key
	, case when advance =1 and x1.fixcharges_key != 19 then null else  x1.date_charged  end as Date_Charged
	, case when advance =1 and x1.fixcharges_key != 19 then null else  x1.amount_charged   end as Amount_Charged
	,x2.date_paid
 	,  max_charge - min_charge  as a
	,  max_payment-min_payment   as b
	, max_charge - max_payment  as c
	,  (max_payment-min_payment)+ (max_charge - max_payment)  as d
	 
	 from
	(select account_no
		, case when past_due =1 then 54 else fixcharges_key end as fixcharges_key
		, date_charged, amount_charged
		,(select sum(amount_charged) from @charges where date_charged <= p.date_charged and 
		case when past_due =1 then 54 else fixcharges_key end = case when p.past_due =1 then 54 else p.fixcharges_key end  ) as Max_Charge
		, (select coalesce(sum(amount_charged),0) from @charges where date_charged < p.date_charged and 
		case when past_due =1 then 54 else fixcharges_key end = case when p.past_due =1 then 54 else p.fixcharges_key end ) as Min_Charge 
		, null as advance, past_due from @charges p
	union all 
	select s.account_no,s.fixcharges_key,  dateadd(m,d.number,s.last_charged) as date_charged,mc.monthlycharge 
		,(select sum(amount_charged)  from  (select account_no, amount_charged, case when past_due =1 then 54 else fixcharges_key end as fixcharges_key from @charges) x2 where account_no = s.account_no and 
			fixcharges_key =s.fixcharges_key  ) +  mc.monthlycharge * d.number 
		,(select sum(amount_charged)  from (select account_no, amount_charged, case when past_due =1 then 54 else fixcharges_key end as fixcharges_key from @charges) x2  where  account_no = s.account_no and 
			fixcharges_key =s.fixcharges_key  ) + mc.monthlycharge  * (d.number-1), 1 as advance
	 	,null as Past_Due
		from ( 
			select sum_c.account_no,  sum_c.fixcharges_key, ceiling((coalesce(total_paid,0)- coalesce(total_charged,0))/monthlycharge) as months_advance
			,(select max(date_charged) as last_charged from @charges where account_no = s.account_no) as Last_Charged
			,(select sum(amount_charged)  from @charges where account_no = s.account_no and fixcharges_key =19) as Total_Charged
			 
			from @subscribers s
			 	left join
				(select account_no,fixcharges_key 
					,coalesce(sum(amount_charged),0) as total_charged from @charges
				group by account_no, fixcharges_key ) sum_c on sum_c.account_no = s.account_no
				left join 
					(select account_no,fixcharges_key,coalesce(sum(amount_paid),0) as total_paid from @payments 
					group by account_no, fixcharges_key) sum_p
					on s.account_no = sum_p.account_no and sum_c.fixcharges_key = sum_p.fixcharges_key
				left join (select account_no, sum(amount) as monthlycharge from @monthlycharges group by account_no) mc
					on s.account_no = mc.account_no
	 		where coalesce(total_paid,0)- coalesce(total_charged,0)> 0
					) s
		inner join (select account_no, sum(amount) as monthlycharge from @monthlycharges group by account_no) mc
			on mc.account_no = s.account_no
		inner join(
				select	number
				from	master..spt_values
				where	number between 1 and 20
					and type = 'p'
			) AS d ON d.number <= s.months_advance) x1
left join
	(select * , (select sum(amount_paid) from @payments where date_paid <= p.date_paid and 
			fixcharges_key = p.fixcharges_key) as Max_Payment
		, (select coalesce(sum(amount_paid),0) from @payments where date_paid < p.date_paid and 
			fixcharges_key = p.fixcharges_key) as Min_Payment from @payments p) x2
		on x1.account_no = x2.account_no and 
			 ( (x1.max_charge+1 >= x2.max_payment and x1.min_charge+1 <= x2.max_payment)
				or 
			    (x2.max_payment+1 >= x1.max_charge and x2.min_payment+1 <= x1.max_charge ) )
			and x1.fixcharges_key = x2.fixcharges_key ) tt
order by account_no,   fixcharges_key, coalesce(date_charged,'01/01/2070') 
set nocount off
go

exec dbo.create_ledger1 1