• The Water Bill was billed for dates 8/15/10 to 9/15/10 (31 days). From 8/15/10 to 8/28/10 (13 days), only Clark Kent lived on the property. This split portion would be (((13 days/31 days) * 25.00 amt_due)/ 1 tenant) = $10.48. This split portion only applies to Clark Kent.


• From 8/28/10 to 9/5/10 (8 days), Clark Kent and Bruce Wayne lived on the property. This split portion would be (((8 days/31 days) * 25.00 amt_due)/ 2 tenants) = $3.23. This split portion applies to Clark Kent and Bruce Wayne. Clark now is responsible for $13.71 (10.48 + 3.23) and Bruce is now responsible for $3.23.


• From 9/5/10 to 9/10/10 (5 days), only Bruce Wayne lived on the property. The split portion would be (((5 days/31 days) * 25.00 amt_due)/ 1 tenant) = $4.03. This split portion only applies to Bruce Wayne. Clark is still responsible for $13.71 and Bruce is now responsible for $7.26 (3.23 + 4.03).


• From 9/10/10 to 9/15/10 (5 days), Bruce Wayne and Peter Parker lived on the property. The split portion would be (((5 days/31 days) * 25.00 amt_due)/ 2 tenants) = $2.02. This split portion applies to Bruce Wayne and Peter Parker. That completes the bill. Clark is responsible for $13.71; Bruce Wayne is responsible for $9.28 (7.26 + 2.02); Peter Parker is responsible for $2.02. These add up to 1 penny over, but that is ok for what I am trying to do.

declare @tenant table(tenant_id int not null primary key, name varchar(30) not null, 
move_in smalldatetime not null, move_out smalldatetime null)

declare @bill table(bill_id int not null primary key, bill_type varchar(20) not null, 
service_from smalldatetime not null, service_to smalldatetime not null, amt_due money not null)

declare @result table(tenant_id int not null, bill_type varchar(20) not null, amt_due money not null,
split_prorate_amt money not null)

insert @tenant select 1, 'Clark Kent', '10/20/2009', '09/05/2010' union all select 
2, 'Bruce Wayne', '08/28/2010', NULL union all select 
3, 'Peter Parker', '09/10/2010', NULL

insert @bill select 1, 'Power Bill', '08/01/2010', '09/01/2010', 100.00 union all select 
2, 'Water Bill', '08/15/2010', '09/15/2010', 25.00 union all select 
3, 'Gas Bill', '07/28/2010', '08/27/2010', 75.00 union all select 
4, 'Cable Bill', '09/11/2010', '10/10/2010', 45.00

insert @result select 1, 'Gas Bill', 75.00, 75.00 union all select 
2, 'Cable Bill', 45.00, 22.50 union all select 
3, 'Cable Bill', 45.00, 22.50 union all select 
1, 'Water Bill', 25.00, 13.71 union all select 
2, 'Water Bill', 25.00, 9.28 union all select 
3, 'Water Bill', 25.00, 2.02 union all select 
1, 'Power Bill', 100.00, 93.55 union all select 
2, 'Power Bill', 100.00, 6.45

-- now the magic

-- the following generates a sequence of numbers
;with n(n) as (select 0 union all select n+1 from n where n<100),

-- this next one generates all the billable days for each bill, plus the per diem amount
	b(bill_id, type, billable_date, per_diem, amt_due) as 
		(select b.bill_id, b.bill_type, dateadd(day, n, b.service_from), 
		amt_due/datediff(day,b.service_from,b.service_to), b.amt_due
		from @bill b cross join n 
		where n<=datediff(day,b.service_from,b.service_to)-1),

-- this one joins the tenants to the available billable days they occupied the property
	t(tenant_id, name, bill_id, type, billable_date, per_diem, amt_due) as 
		(select t.tenant_id, t.name, b.bill_id, b.type, b.billable_date, b.per_diem, b.amt_due
		from b
		inner join @tenant t on b.billable_date between t.move_in and IsNull(t.move_out-1, b.billable_date)),

-- this one summarizes each bill and counts the number of split occupants
	s(bill_id, billable_date, splits) as
		(select bill_id, billable_date, count(distinct tenant_id)
		from t group by bill_id, billable_date)

-- the following sums up the bill amounts generated from the previous CTE's
select t.tenant_id, t.name, t.type, sum(t.per_diem/s.splits) split_prorate_amt, 
round(sum(t.per_diem/s.splits),2) split_prorate_amt_rounded, 
count(*) days, max(t.amt_due) amt_due, max(t.per_diem) per_diem
from t
inner join s on t.bill_id=s.bill_id and t.billable_date=s.billable_date
group by t.tenant_id,t.name, t.type
order by 3,1 

-- this lists the results you specified
select * from @result order by 2, 1