quote:

---

--INPUT 3 -> EXPECTED "Result 3" in the above link
DECLARE @dateStart DATETIME='2014-01-08 00:00:00',@dateEnd DATETIME='2014-01-13 00:00:00'
DECLARE @TBL TABLE(ID INT IDENTITY(1,1), interval INT, amount MONEY)
INSERT INTO @TBL VALUES (4,300,0,0),(2,55,0,0),(1,45,0,0)

---

declare	@nights	int

select	@nights = datediff(day, @dateStart, @dateEnd)

; with 
numbers as   -- RCTE numbers / tally table
(
	select	n = 1
	union all
	select	n = n + 1 
	from 	numbers
	where	n < 10
),
rcte as  -- to find the combination with lowest amount
(
	select	top 1 
		seq = 1,
		ID, interval, amount, nights = interval, cumm_nights = interval
	from	@TBL
	where	interval <= @nights
	order by amount / interval

	union all

	select	seq, ID, interval, amount, nights, cumm_nights
	from
	(
		select	seq 		= r.seq + 1,
			t.ID, t.interval, t.amount, 	
			nights		= case 	when 	t.interval <= @nights - r.cumm_nights
						then	t.interval
						else	@nights - r.cumm_nights
						end,
			cumm_nights 	= r.cumm_nights 
					+ case 	when 	t.interval <= @nights - r.cumm_nights
						then	t.interval
						else	@nights - r.cumm_nights
						end,
			rn	= row_number() over (order by case when (@nights - r.cumm_nights) >= t.interval then t.amount / t.interval else t.amount end)
		from	@TBL t
			cross join rcte r
		where	r.cumm_nights	< @nights
	) r
	where	rn		= 1
 	and	cumm_nights	<= @nights 
),
amt as   -- to apportion the amount
(
	select	*,
		breakAmount	= amount / interval nights,
		finalAmount	= sum(amount) over()
	from	rcte
)
-- to break-apart by dates
select	*, [date] = dateadd(day, row_number() over(order by seq) - 1, @dateStart)
from	amt a
	cross join numbers n 
where	n.n <= a.nights
order by seq

rcte as
(
	select	top 1 
		seq = 1,
		ID, interval, amount, 
		nights 		= case when interval < @nights then interval else @nights end, 
		cumm_nights 	= case when interval < @nights then interval else @nights end
	from	@TBL
	order by amount / interval

	union all

rcte as
(
	select	top 1 
		seq = 1,
		ID, interval, amount, 
		nights 		= case when interval < @nights then interval else @nights end, 
		cumm_nights 	= case when interval < @nights then interval else @nights end
	from	@TBL
	order by amount / interval (case when interval < @nights then interval else @nights end)

	union all

declare	@nights	int

select	@nights = datediff(day, @dateStart, @dateEnd)

; with 
numbers as	-- recursive CTE number / tally tables, if you have one, you don't need this
(
	select	n = 1
	union all
	select	n = n + 1 
	from 	numbers
	where	n < 10		-- max n is 10. Change accordingly to requirement
),
rcte as		-- using recursive CTE to determine the lowest amount combinition
(
	-- Anchor Member (get the record with lowest amount)
	select	top 1 
		seq 		= 1,		-- Anchor Member, seq = 1. Used for ordering final result
		ID, interval, amount, 
		nights 		= case when interval < @nights then interval else @nights end, 	-- actual no of nighs
		cumm_nights 	= case when interval < @nights then interval else @nights end	-- cummulative nights
	from	@TBL
	order by amount / (case when interval < @nights then interval else @nights end)		-- amount / actual no of nights  = lowest amount

	union all

	-- Recursive Member (continue getting lowest amount based on balance nights)
	select	seq, ID, interval, amount, nights, cumm_nights
	from
	(
		select	seq 		= r.seq + 1,	-- Increment the seq number
			t.ID, t.interval, t.amount, 
			nights		= case 	when 	t.interval <= @nights - r.cumm_nights 	-- if interval is less than the balance nights
						then	t.interval				-- actual no of nights = interval
						else	@nights - r.cumm_nights			-- if interval is more than the balance nights, actual = balance
						end,
			cumm_nights 	= r.cumm_nights 
					+ case 	when 	t.interval <= @nights - r.cumm_nights
						then	t.interval
						else	@nights - r.cumm_nights
						end,
			rn	= row_number() over (order by 	case  	when 	(@nights - r.cumm_nights) >= t.interval 
									then 	t.amount / t.interval 
									else 	t.amount 
									end)
		from	@TBL t
			cross join rcte r
		where	r.cumm_nights	< @nights
	) r
	where	rn		= 1			-- get the lowest amount
 	and	cumm_nights	<= @nights 
),
amt as	-- calculate the break amount 
(
	select	*,
		breakAmount	= amount / nights,
		finalAmount	= sum(amount) over()
	from	rcte
)
--	final result that break up the dates
select	*, [date] = dateadd(day, row_number() over(order by seq) - 1, @dateStart)
from	amt a
	cross join numbers n 
where	n.n <= a.nights
order by seq