hi,
people, is there any difference when my stored procedure is selecting from a cursor? when I create the dataset, it doesn't show fields for the procedure.
so, the same happens on the report wizard.
I really don't know if this problem is caused for circumstances on Crystal Reports or it's the Stored Procedure.
heres the procedure:
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
is a little long... but i wanted to make sure I was showing everything...
thanks in advance...
-------------
Enter a signature that you would like shown at the bottom of your Forum Posts (max 200 characters)
|