indofraiser indofraiser - 10 days ago 5
SQL Question

Returns zero not a value

Issue: PassedPercent returns 0 but there should be a number (I can confirm in all cases @Started and @Passed have numerical values). The same formula worked correctly on the .aspx page but I'm converted it to an SQL query.

Code:

USE DATABASE

DECLARE @i INT = 0
DECLARE @ModuleID bigint
DECLARE @Started bigint
DECLARE @Completed bigint
DECLARE @Passed bigint

DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT [ModuleID] FROM dbo.[TblModules]
--ORDER BY [ModuleID]
OPEN merge_cursor

FETCH NEXT FROM merge_cursor INTO @ModuleID

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Started = (Select Count(ID) FROM [TblResults] WHERE [ModuleID] = @ModuleID)
SET @Completed = (Select Count(ID) FROM [TblResults] WHERE [ModuleID] = @ModuleID and ModuleDatecomplete <> '')
SET @Passed = (Select Count(ID) FROM [TblResults] WHERE [ModuleID] = @ModuleID and Pass = 'Yes')

UPDATE [TblModules] SET [Started] = @Started,[Completed] = @Completed,[Passed] = @Passed,[PassedPercent] = ((@Passed / @Started) * 100)
WHERE [ModuleID] = @ModuleID

FETCH NEXT FROM merge_cursor INTO @ModuleID
END

CLOSE merge_cursor
DEALLOCATE merge_cursor

Answer

An int / int will return an int (in this case zero)

Try

 ,[PassedPercent] = (@Passed *100.0) / @Started