creating a total with variables with a looping cursor
I'm trying to loop through a table using a cursor and assigning the values from each row to variables to come up with two grand totals. I'm adding itself plus the calculation to itself to get a grand total variable. There are about 5 record that match the criteria of the cursor. I can view the individual rows values but when i try to utilize adding it to itself to have a grand total variable its passing me a null value. Can anyone tell me what i'm doing wrong. It it looks like it would total the values up but its not. Any help would be appreciated.
Mark
OPEN cursorPur
FETCH NEXT FROM cursorPur into @strCUSTID,@strCUSTNAME,
@strCITY,@strSTATE,@dtORDDT,@intQTY,@decDISC,@decU
PRICE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @decTOTPAID = @decTOTPAID + ((@intQTY * @decUPRICE) - @decDISC)
SET @intTOTQTY = @intTOTQTY + @intQTY
FETCH NEXT FROM cursorPur into @strCUSTID, @strCUSTNAME,
@strCITY, @strSTATE, @dtORDDT, @intQTY, @decDISC,@decUPRICE
END
SELECT @strCUSTID AS CUSTID,
@strCUSTNAME AS strCUSTNAME,
@strCITY AS strCITY,
@strSTATE AS strSTATE,
@decTOTPAID AS TOTPAID,
@intTOTQTY AS TOTALQTY
CLOSE cursorPur
DEALLOCATE cursorPur
:confused:
Thank you very much for your help
It still would not work with the calculations in the loop it seems it will only add the two varibles together with no calculations.
I did the calculations for the select in the cursor? I works fine now.
I guess we're not allowed to do calculations with loop variables!!!
WHILE @@FETCH_STATUS = 0
BEGIN
SET @decTOT = @decTOT + @decTOTPAID
SET @intTOT = @intTOT + @intTOTQTY
FETCH NEXT FROM cursorPur into @strCUSTID, @strCUSTNAME,
@strCITY, @strSTATE, @dtORDDT, @intQTY, @decDISC,@decUPRICE, @decTOTPAID, @intTOTQTY
END
SELECT @strCUSTID AS CUSTID,
@strCUSTNAME AS strCUSTNAME,
@strCITY AS strCITY,
@strSTATE AS strSTATE,
@decTOT AS TOTPAID,
@intTOT AS TOTALQTY
CLOSE cursorPur
DEALLOCATE cursorPur
:D :D :D