Decimal places lost in division

[anorthcote]

Hi,

I hope someone can help with a query on a stored procedure.

Records being displayed in our application are split into pages of x amount of records. x being a value (default of 30) that is user specified.

I have a stored procedure that takes the @NumPerPage from the user and divided the count of records by that number to provide a count of pages.

CREATE PROCEDURE dbo.CallPages
(
	@LocationID		int,
	@StatusID		int,
	@Area			int,
	@Contractor		int,
	@NumPerPage		int
)
AS
BEGIN

DECLARE @Calls		int

	select @Calls = COUNT(dbo.Calls.LogID)
FROM         dbo.Location LEFT OUTER JOIN
                      dbo.Area ON dbo.Location.AreaID = dbo.Area.ID RIGHT OUTER JOIN
                      dbo.Calls ON dbo.Location.ID = dbo.Calls.LocationID LEFT OUTER JOIN
                      dbo.ContractorOrder INNER JOIN
                      dbo.Contractor ON dbo.ContractorOrder.ContractorID = dbo.Contractor.ID ON dbo.Calls.OrderID = dbo.ContractorOrder.OrderID

where (@LocationID = 0  OR LocationID  = @LocationID) AND
      (@StatusID   = 0  OR StatusID    = @StatusID)   AND
      (@area       = 0  OR Area.ID  = @area) AND
      (@Contractor = 0  OR dbo.Contractor.ID = @Contractor)

RETURN CEILING(@Calls / @NumPerPage)
	
END
GO

My problem is that the return value is always an integer and, even with using CEILING the number is rounding down and when dividing records into pages of 30 this number should be rounded up. I have tried changing @Calls and @NumPerPage to REAL but that doesn't work either, it still returns an integer.
The only fix I have found is very clunky and, although it works, I don't like it!

CREATE PROCEDURE dbo.CallPages
(
	@LocationID		int,
	@StatusID		int,
	@Area			int,
	@Contractor		int,
	@NumPerPage		int
)
AS
BEGIN

DECLARE @Calls		int
DECLARE @Pages		int
DECLARE @CallTest	int
DECLARE @Diff		int

	select @Calls = COUNT(dbo.Calls.LogID)
FROM         dbo.Location LEFT OUTER JOIN
                      dbo.Area ON dbo.Location.AreaID = dbo.Area.ID RIGHT OUTER JOIN
                      dbo.Calls ON dbo.Location.ID = dbo.Calls.LocationID LEFT OUTER JOIN
                      dbo.ContractorOrder INNER JOIN
                      dbo.Contractor ON dbo.ContractorOrder.ContractorID = dbo.Contractor.ID ON dbo.Calls.OrderID = dbo.ContractorOrder.OrderID

where (@LocationID = 0  OR LocationID  = @LocationID) AND
      (@StatusID   = 0  OR StatusID    = @StatusID)   AND
      (@area       = 0  OR Area.ID  = @area) AND
      (@Contractor = 0  OR dbo.Contractor.ID = @Contractor)

set @Pages = round (@Calls / @NumPerPage,2)

set @CallTest = (@Pages * @NumPerPage)

set @Diff = (@Calls - @CallTest)

if @Diff > 0 
	BEGIN
		set @Pages = (@Pages + 1)
	END

RETURN @Pages
	
END
GO

Can anyone suggest a better way of writing the procedure?

 

[SQLSister]

you are a victim of integer math.

set @Pages = round (@Calls /(@NumPerPage* 1.0),2)

This should fix it

"NOTHING is more important in a database than integrity." ESquared

 

[anorthcote]

Ok, I've tried

CREATE PROCEDURE dbo.CallPages
(
	@LocationID		int,
	@StatusID		int,
	@Area			int,
	@Contractor		int,
	@NumPerPage		int
)
AS
BEGIN

DECLARE @Calls		int

	select @Calls = COUNT(dbo.Calls.LogID)
FROM         dbo.Location LEFT OUTER JOIN
                      dbo.Area ON dbo.Location.AreaID = dbo.Area.ID RIGHT OUTER JOIN
                      dbo.Calls ON dbo.Location.ID = dbo.Calls.LocationID LEFT OUTER JOIN
                      dbo.ContractorOrder INNER JOIN
                      dbo.Contractor ON dbo.ContractorOrder.ContractorID = dbo.Contractor.ID ON dbo.Calls.OrderID = dbo.ContractorOrder.OrderID

where (@LocationID = 0  OR LocationID  = @LocationID) AND
      (@StatusID   = 0  OR StatusID    = @StatusID)   AND
      (@area       = 0  OR Area.ID  = @area) AND
      (@Contractor = 0  OR dbo.Contractor.ID = @Contractor)

RETURN round (@Calls /(@NumPerPage* 1.0),2)
	
END
GO

and ran it with the following simple query

declare @pages real
declare @calls int

select @Calls = COUNT(dbo.Calls.LogID)
FROM         dbo.Location LEFT OUTER JOIN
                      dbo.Area ON dbo.Location.AreaID = dbo.Area.ID RIGHT OUTER JOIN
                      dbo.Calls ON dbo.Location.ID = dbo.Calls.LocationID LEFT OUTER JOIN
                      dbo.ContractorOrder INNER JOIN
                      dbo.Contractor ON dbo.ContractorOrder.ContractorID = dbo.Contractor.ID ON dbo.Calls.OrderID = dbo.ContractorOrder.OrderID



exec @pages=callpages 0,0,0,0,30

print @calls
print @pages

my query returns

18776
625

where I would expect it to return 625.86 or 626 if rounding up. 626 is my desired result in this example.

Why is the code still not working?

 

[SQLSister]

return it without the round and see what you get.

"NOTHING is more important in a database than integrity." ESquared

 

[anorthcote]

no difference, I still get 625

 

[SQLSister]

try changing the datatype of @pages. it is an int. I think this is what is causing the problem.
look at this

declare @pages decimal (10,4), @calls int, @numperpage int
set @calls = 18776
set @numperpage = 30
set @Pages = @Calls /(@NumPerPage* 1.0)
select round(@pages, 0)

"NOTHING is more important in a database than integrity." ESquared

 

[anorthcote]

That works... Thank you very much

 

[anorthcote]

I thought this one was done and dusted but it still isn't working quite correctly.

Using the example given above I have set @calls to 32 and @numperpage to 30. This being the case if I have 32 calls with 30 per page I should have 2 pages. I don't, I get 1!

declare @pages decimal (10,4), @calls int, @numperpage int
set @calls = 32
set @numperpage = 30
set @Pages = @Calls /(@NumPerPage* 1.0)
select round(@pages, 0)

It works in excel using roundup(32/30,0) so why can't SQL handle it?

 

[gmmastros]

It works in excel using roundup(32/30,0) so why can't SQL handle it?

Well... in Excel, you are using RoundUp, but in SQL, you're using Round. If you want to RoundUp in SQL, you need to use the Ceiling function. Like this...

declare @pages decimal (10,4), @calls int, @numperpage int
set @calls = 32
set @numperpage = 30
set @Pages = @Calls /(@NumPerPage* 1.0)
select [!]Ceiling[/!](@pages)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anorthcote]

Thanks, that works now...

I, obviously, was getting a mixture of integer maths and rounding issues together.

Cheers