Create PROCEDURE dbo.[PaymentInformation_T_CalculateBonus]
(
@FromDate DATETIME, 
@ToDate DATETIME
)

AS

DECLARE @VENTAS INT
DECLARE @AGENT VARCHAR(50)
DECLARE @Bonos INT
DECLARE @Pay DECIMAL(10,2)
DECLARE @ProductID INT
DECLARE @ProductoNomre VARCHAR(200)
DECLARE @QTY INT
DECLARE @LE INT 
DECLARE @LG INT

--Declare the cursor
DECLARE @Cursor CURSOR
SET @Cursor = Cursor
FOR
SELECT T.[proID], P.[Product], COUNT(1) AS QTY, p.[SalesAgent]
FROM [transactions_V] T
INNER JOIN [PaymentInformation_V] p ON t.[payID] = p.[payID]
INNER JOIN [ReferenciasValores_V] r ON t.[proID] = r.[ValorID]
WHERE T.[Result] LIKE 'Completed%'
AND p.[SalesAgentID] IS NOT NULL 
AND p.[SalesAgent] IS NOT NULL 
AND t.[Date] BETWEEN @FromDate AND @ToDate
AND t.transactiontype in ('Enrollment')
GROUP BY p.[SalesAgent], T.[proID], P.[Product]
ORDER BY p.SalesAgent

/*
Temp Table to Hold The Calculated Rows
*/

CREATE TABLE #BonosInformation
(
AgentID INT,
Agente VARCHAR(50),
ProductoID INT,
ProductoNombre VARCHAR(200),
Ventas INT,
CantReqPorBono INT,
Precio INT,
TotalBonos INT,
FromDate DATETIME,
ToDate DATETIME
)

OPEN @Cursor
FETCH @Cursor INTO @ProductID, @ProductoNomre, @VENTAS, @AGENT

WHILE @@FETCH_STATUS = 0
BEGIN
SET @LE = 100000
SELECT 
    @LG = MAX(PackMinimum), 
    @Pay = MAX(PackPrice) 
FROM [BonusPackages_V] 
WHERE packminimum < @LE
AND ProID = @ProductID
GROUP BY proid

WHILE @LE > 1
Begin
    SET @Bonos = @Pay * CONVERT(INT,@VENTAS/@LG)
    
     INSERT INTO [#BonosInformation] (
        [Agente],
        ProductoID,
        [ProductoNombre],
        Ventas,
        [CantReqPorBono],
        [Precio],
        [TotalBonos],
        [FromDate],
        [ToDate]
    ) VALUES ( 
    @AGENT,
    @ProductID,
    @ProductoNomre,
    @Ventas,
    @LG,
    @Pay,
    @Bonos,
    @FromDate,
    @ToDate
             ) 
             
    SET @LE = @LG
    SET @Ventas = @Ventas-(CONVERT(INT,@VENTAS/@LG)*@LG);
    SELECT 
        @LG = MAX(PackMinimum), 
        @Pay = MAX(PackPrice) 
    FROM [BonusPackages_V] 
    WHERE packminimum < @LE
    AND ProID = @ProductID
    GROUP BY proid
END
FETCH @Cursor INTO @ProductID, @ProductoNomre, @VENTAS, @AGENT
END


SELECT * FROM #BonosInformation WHERE [TotalBonos] > 0
DROP TABLE #BonosInformation
CLOSE @Cursor