Getting same average values excute the stored procedure

Sivakumar

11y1.2k

Hi,

This is My Stored Procedure :

GO
/****** Object: StoredProcedure [dbo].[uspGetVote] Script Date: 9/3/2015 12:40:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspGetVote](@TeamID INT)

AS

BEGIN

select avg(VoteValue) as [AverageVoteValue] from Vote where datediff(day,getdate(),LastModifiedDateTime) <= 7 Group By TeamID order by AverageVoteValue

END

and i execute this procedure but get a same results for all teamid's

please give a correct query for that

18 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pradeep11y ago
give alias name for VoteID after group by
Sivakumar11y ago
This is my query :

GO
ALTER PROCEDURE [dbo].[uspGetVote](@TeamID INT,@CardID INT)

AS

BEGIN

SELECT AVG(VoteValue) AS [AverageVoteValue] , Comment FROM Vote join VoteComment on Vote.VoteID=VoteComment.VoteID
WHERE TeamID=@TeamID and CardID=@CardID or LastModifiedDateTime between DATEADD (day , -7 , GETDATE())and getDate() group by VoteID
end

when i execute this shows an error like :
Msg 209, Level 16, State 1, Procedure uspGetVote, Line 9
Ambiguous column name 'VoteID'.
Pradeep11y ago
then Use group by VoteID.
Sivakumar11y ago
But getting all average values with comment
Pradeep11y ago
Ok, you can apply join to the above query.
Sivakumar11y ago
I want to get average vote value with vote comment in VoteComment table
Pradeep11y ago
Shivakumar,

You need only last 7day's average value of Vote Value.
then why you want to join those two tables?.
Raja T11y ago
not clear your questiong please explain clearly.
Sivakumar11y ago
that's ok, i want to join another table to this procedure.

my tables is like this :

so i want to join VoteComment table also for return vote comment
Pradeep11y ago
then what is the exact value ..!?
can u show me the screen shot..?
Pradeep11y ago
then what is the exact value...! ?
can u show me the screen shot?
Raja T11y ago
hi, try to use round,ceiling and floor sql functions
Please checkout bellow urls

http://www.w3schools.com/sql/sql_func_round.asp

https://www.mssqltips.com/sqlservertip/1589/sql-server-rounding-functions--round-ceiling-and-floor/

http://www.codeproject.com/Questions/622211/Ceiling-and-Floor-doubt
Sivakumar11y ago
this is return like 3.00,4.00 not exact value
Pradeep11y ago
select cast(avg(VoteValue) as decimal(18, 10)) as [AverageVoteValue]
from Vote
where LastModifiedDateTime
in
(select top 7 LastModifiedDateTime from Vote
where TeamID=@teamID
and
CardID=@cardID
order by LastModifiedDateTime
desc)

Raja T11y ago

Hi,

ALTER PROCEDURE taxable_varsalary

 @emp_code bigint,

  @co_id bigint,

  @t_varsalary decimal(8,2) OUTPUT

AS

  select @t_varsalary  = sum(tran_value)     from emp_ded_ben_trans

   where emp_code=@emp_code and co_id=@co_id and period_flg=2 and tax_flg=0

END

more details checkout bellow urls

http://stackoverflow.com/questions/28739797/how-to-return-decimal-value-from-stored-procedure

http://stackoverflow.com/questions/3406073/stored-procedure-return-00-decimal

Sivakumar11y ago
I want to return decimal values
Raja T11y ago
Hi, Missing Parameter try bellow code

GO
/****** Object: StoredProcedure [dbo].[uspGetVote] Script Date: 9/3/2015 12:40:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspGetVote](@TeamID INT)

AS

BEGIN

select avg(VoteValue) as [AverageVoteValue] from Vote where datediff(day,getdate(),LastModifiedDateTime) <= 7 Group By @TeamID order by 1

END
Pradeep11y ago
100% it will work. try it .....

select avg(VoteValue) as [AverageVoteValue] from Vote where LastModifiedDateTime in
(select top 7 LastModifiedDateTime from Vote where TeamID=@teamID and CardID=@cardID order by LastModifiedDateTime desc)

you just Pass @teamID & @cardID parameters that's it.