DECLARE
	@BookingID varchar(32) = 'IR0000002496',

	@Date      datetime = '20170110',

	@LastDate  datetime = '20170109',

	@Value     float

begin

	declare @Ticket                varchar(10),

			@DueDate			   datetime,

			@Cashflow			   int,

			@ValueDate				datetime,
			@PrincipalStart			float

	select @Ticket = Ticket from GL_Transaction where BookingID = @BookingId



	select @Cashflow = Cashflow from IR_Deal where Ticket = @Ticket


	select @DueDate = max(InterestDate) from v_Cashflow where id = @Cashflow and DueDate < @Date and InterestEvent = 1
	select @ValueDate = ValueDate from IR_Deal where BookingID = @BookingID
	select top 1 @PrincipalStart = Principal from v_Cashflow where Id = @Cashflow and PrincipalEvent = 1 order by DueDate asc


	if @DueDate is null
		select @DueDate = @ValueDate
	else
		set @PrincipalStart = 0



	select @Value = SUM(isnull(i.AmortisedCost - i.prevAmortisedCost, 0)) + isnull(sum(abs(cf.Interest)), 0) + isnull(sum(ABS(cf.Principal)), 0) - abs(@PrincipalStart)

	from v_IR_Info i

		inner join IR_Deal d on d.Ticket = i.Ticket

		left outer join v_Cashflow cf on cf.Id = @Cashflow and (cf.SettlementInterestDate = i.Date or cf.SettlementPrincipalDate = i.Date) and cf.DueDate <> @ValueDate

	where i.Ticket = @Ticket

		and i.Date <= @Date

		and i.Date > @DueDate

end
select @value