Group by Max Date

[Dalel23]

I have this Stored Procedure that I converted from an Access Query that is not returning the correct result.

The SumOfWt_Add is retuning incorrect result.

For example if Bales_Add for a particular Row is 0 and the Avg_Bales_Wt is 1200 then result I get is 0. It should be 0.6.

CREATE PROCEDURE dbo.procStartup_Query
AS
SET NOCOUNT ON
SELECT Row, Furnish, MAX(dtDate) AS dtDate, SUM(Bales_Add) AS SumOfBales_Add, SUM(Bales_Remove) AS SumOfBales_Remove,
SUM(Bales_Add * Avg_Bale_Wt) / 2000 AS SumofWt_Add
FROM dbo.Main_Table
GROUP BY Row, Furnish
ORDER BY MAX(dtDate) DESC
SET NOCOUNT OFF
GO


Any help will be greatly appreciated.

Thank you

 

[monksnake]

CREATE PROCEDURE dbo.procStartup_Query
AS
SET NOCOUNT ON
 SELECT     Row, Furnish, MAX(dtDate) AS dtDate, SUM(Bales_Add) AS SumOfBales_Add, SUM(Bales_Remove) AS SumOfBales_Remove, 
                      SUM(Bales_Add * Avg_Bale_Wt) / 2000[!].0[/!] AS SumofWt_Add
FROM         dbo.Main_Table
GROUP BY Row, Furnish
ORDER BY MAX(dtDate) DESC
SET NOCOUNT OFF
GO

<.

 

[Dalel23]

Thank you monksnake

It did the trick.

Could you please explain to me what the difference is?
I mean you had to .0 for it to work?

Thank you

 

[monksnake]

Could you please explain to me what the difference is?

If you divide by an integer by an integer, you get an integer answer, so adding the .0 forces a decimal answer. As to the inner workings of it, I really don't know.

I mean you had to .0 for it to work?

You can convert one of the numbers you divide by into a decimal then divide, but it's much easier just to add the .0

<.