Redeem Calculation Only

create table PurRedTranTable

(
id int identity(1,1),

folionumber varchar(20),

schemecode varchar(20),

plancode varchar(20),

tdate date,

units float,

transtype varchar(20),

purred varchar(20),

nav int
)


INSERT INTO PurRedTranTable
VALUES('100', 'FF', 'GP', '06/01/18', '300', 'SIP','P', 20 );

INSERT INTO PurRedTranTable
VALUES('100', 'FF', 'GP', '06/10/18', '50', 'SIP','P', 20 );

INSERT INTO PurRedTranTable
VALUES('100', 'FF', 'GP', '06/20/18', '50', 'SIP','P', 20 );

INSERT INTO PurRedTranTable
VALUES('100', 'FF', 'GP', '06/30/18', '200', 'RED','R', 20 );

INSERT INTO PurRedTranTable
VALUES('100', 'FF', 'GP', '07/10/18', '100', 'SIP','P', 20 );

INSERT INTO PurRedTranTable
VALUES('100', 'FF', 'GP', '07/20/18', '50', 'SIP','P', 20 );

INSERT INTO PurRedTranTable
VALUES('100', 'FF', 'GP', '07/30/18', '150', 'SIP','P', 20 );

INSERT INTO PurRedTranTable
VALUES('100', 'FF', 'GP', '08/05/18', '500', 'RED','R', 20 );
-----------------------------------------------------------------------------

 CREATE FUNCTION [dbo].[GetDetailsOfRedeemCalculationTable]
(
--Can use profile or ClientID as parameter to execute function on specific thing like..
@folioNum int,
@redeemDate date,
@schemaCode varchar(10),
@planCode varchar(10),
@nav int 
)
RETURNS
-- This will be your output table --OT--
@tExitLoadResults TABLE
( 
ID varchar(20),
TDate date,
units int,
daysDiff int,
RedeemAmount float,
ExitLoadAmount float
)
AS
BEGIN

DECLARE @ID varchar(20)
DECLARE @TDate date
DECLARE @units int

DECLARE myCursor CURSOR FOR
--Get required data from source table. Use parameter variable if you declared above in where clause. Like where tranType='P' and policyNum=@policyNum
--Order by date ascending. Because old dates should come 1st in cursor
SELECT ID, TDate, units FROM PurRedTranTable
where folionumber=@folioNum and purred='P' and tdate!=@redeemDate and schemecode=@schemaCode and plancode=@plancode
ORDER BY TDate

declare @totalRedmAmt int
-- Get total redemtion amount in 1 variable
SET @totalRedmAmt=(select sum(units) from PurRedTranTable
where folionumber=@folioNum and purred='R' and tdate!=@redeemDate and schemecode=@schemaCode and plancode=@plancode)
OPEN myCursor

--It will take 1 by 1 record from select query result
FETCH NEXT FROM myCursor INTO  @ID, @TDate,@units

WHILE (@@FETCH_STATUS = 0)
BEGIN
-- If @totalRedmAmt is greater than 0 and Purchase amount of selected record then set amount to 0 and minus "amount of selected record" from @totalRedmAmt
Declare @remainingQty int
DECLARE @totalDays int
set @totalDays = DATEDIFF(DAY, @TDate, @redeemDate)

IF(@totalRedmAmt > 0)
BEGIN
IF(@totalRedmAmt >= @units)
set @totalRedmAmt  = @totalRedmAmt - @units
ELSE
set @remainingQty = @units - @totalRedmAmt
set @totalRedmAmt  = 0

INSERT INTO @tExitLoadResults (ID, TDate, units, daysDiff, RedeemAmount, ExitLoadAmount )
SELECT @ID, @TDate, @remainingQty, @totalDays,(select redeemAmount from [getExitLoadAndRedeemAmount](@totalDays, @nav, @remainingQty)),
(select exitLoadAmount from [getExitLoadAndRedeemAmount](@totalDays, @nav, @remainingQty))
END
ELSE
BEGIN
INSERT INTO @tExitLoadResults (ID, TDate, units, daysDiff, RedeemAmount, ExitLoadAmount )
SELECT @ID, @TDate, @units,@totalDays, (select redeemAmount from [getExitLoadAndRedeemAmount](@totalDays, @nav, @units)),
(select exitLoadAmount from [getExitLoadAndRedeemAmount](@totalDays, @nav, @units))
END
--Go to next record
FETCH NEXT FROM myCursor INTO @ID, @TDate, @units

END /*WHILE*/

CLOSE myCursor
DEALLOCATE myCursor

RETURN
END
------------------------------------------------------------------------------

--select * from getExitLoadAndRedeemAmount(65, 20, 100)
--print (CAST(20 as float )/ cast(100 as float)*3) * 100
CREATE function getExitLoadAndRedeemAmount
(
 @totalDays int,
 @nav int,
 @units int
)
RETURNS
@getRedeemAmount table
(
redeemAmount float,
exitLoadAmount float
)
as

begin
--make sure you put GREATER than or LESS tahn with EQUAL '=' sign . Also maintain high to low value order
DECLARE @RedeemAmount float


IF(@totalDays >= 30)
BEGIN
SET @RedeemAmount = (CAST(@nav as float )/ cast(100 as float)*3) * @units
insert into @getRedeemAmount(redeemAmount, exitLoadAmount)
select @RedeemAmount,(@units * @nav)- @RedeemAmount
end

else IF(@totalDays >= 20)
BEGIN
SET @RedeemAmount = (CAST(@nav as float )/ cast(100 as float)*2) * @units
insert into @getRedeemAmount(redeemAmount, exitLoadAmount)
select @RedeemAmount,(@units * @nav)- @RedeemAmount
end
else if (@totalDays >= 10)
BEGIN
SET @RedeemAmount = (CAST(@nav as float )/ cast(100 as float)*1) * @units
insert into @getRedeemAmount(redeemAmount, exitLoadAmount)
select @RedeemAmount,(@units * @nav)- @RedeemAmount
end
else IF(@totalDays >= 0)
BEGIN
SET @RedeemAmount = (CAST(@nav as float )/ cast(100 as float)*0.5) * @units
insert into @getRedeemAmount(redeemAmount, exitLoadAmount)
select @RedeemAmount,(@units * @nav)- @RedeemAmount
end

RETURN
END

--------------------------------------------------------------------------------
--To get in detail report
select * from [GetDetailsOfRedeemCalculationTable](100, '2018-08-05', 'FF', 'GP',20)
-- To get redeem and exitload amount
select sum(redeemAmount),sum (exitloadamount) from [GetDetailsOfRedeemCalculationTable](100, '2018-08-05', 'FF', 'GP',20)