September 5, 2007 at 12:04 am
Hello everyone, me using SQL Server 2000,i want to make a SP for rating system for the Auction site. 2 tables involved in this:
1) Members ( this table is used whenever any one trust to others, one entry insert in this table)
MemberID
FullName
TrustedScore
2) MemberTrustLog (this table is used to show the total score of one person)
MemberID
TrustedBy
TrustedScore
TrustDate
IsTrusted
Working of Rating System:
For example,If I trust Jack, then you naturally would trust Jack too, so Jack becomes invited to join your trusted dealers, so you would have Jack also on your open communication and dealing.. and so on this will build a major community of dealers who trust each other worldwide.
let us begin by fawad, tony, jack and john, fawad posted for sale item,tony bid on it, fawad and tony worked out a deal or simply there was some sort of a bid or offer submitted,until then, neither tony or fawad can mark the other person so before any bid or offer, no one marks another now fawad marked tony as trusted.fawad (for the sake of this argument is rated as follows:
Fawad 4 (trustd by 3: Not trusted by 1)just for this example let us use that tony has not been rated yet,fawad marks tony as trusted,tony has now a rating of Tony rating:3 (trusted by 1)
PLEASE NOTE THERE IS NO NEED TO SAY ANY field that has no value such as 0 i.e. Not Trusted by 0
no let us say that John who has a rating of 6 (Trusted by 4; Not trusted by 1) bids on tonys item,then John marks tony as trusted,what do you thing tony's rating would be 7.
Now let us assume that Jack has a rating of 10 (trusted by 2: Not trusted by 1),now let us say that Jack is pissed off at tony, so he marked him not trusted so now what is tony's rating 7-2 = 5.
So we are dealing with the trusted integer, if you are trusted you get all the people who trust the person trusting you added and if you are not trusted, then all the dealer that trust the person rating you, also dont trust you.
Me tried to make it but its not working accordingly:so kindly check my SP according to the explanations above and make corrections in it.
CREATE PROCEDURE [AddMemberTrustScore]
(@MemberID [int],
@TrustedBy [int],
@TrustDate [datetime],
@IsTrusted[tinyint])
AS
DECLARE @TrustierScore int
DECLARE @LastScoreByTrustier int
DECLARE @CurrentScore int
DECLARE @TrustedScore int
DECLARE @FinalScore int
-- get score of the person who is placing the trust
SELECT @TrustierScore = TrustedScore FROM Members WHERE MemberID = @TrustedBy
-- get score of the trusted member
SELECT @CurrentScore = TrustedScore FROM Members WHERE MemberID = @MemberID
-- check if the trustier has already placed any trust for the member or not
IF EXISTS(SELECT @MemberID FROM MemberTrustLog WHERE MemberID = @MemberID AND TrustedBy = @TrustedBy)
BEGIN
SELECT @LastScoreByTrustier=TrustedScore FROM MemberTrustLog WHERE MemberID = @MemberID AND TrustedBy = @TrustedBy
ORDER BY TrustDate DESC
IF (@TrustierScore > 1)
BEGIN
SET @TrustedScore = @TrustierScore - @LastScoreByTrustier
SET @FinalScore = @CurrentScore + (@TrustierScore - @LastScoreByTrustier)
END
ELSE
BEGIN
SET @TrustedScore = @CurrentScore
END
END
ELSE
BEGIN
-- in case if this is the first trust placed by the person
IF (@TrustierScore > 0)
BEGIN
SET @TrustedScore = @TrustierScore
SET @FinalScore = @CurrentScore + @TrustierScore
END
ELSE
BEGIN
SET @TrustedScore = 1
SET @FinalScore = @CurrentScore + 1
END
END
IF(@TrustedScore > 0)
BEGIN
INSERT INTO [MemberTrustLog]
( [MemberID],
[TrustedBy],
[TrustedScore],
[TrustDate])
VALUES
( @MemberID,
@TrustedBy,
@TrustedScore,
@TrustDate)
UPDATE Members
SET [TrustedScore] = @FinalScore
WHERE MemberID = @MemberID
END
GO
plz reply me asap.
Thanx in Advance.
September 5, 2007 at 5:14 am
Hmm.. this system seems pretty illogical to me so I may have problems understanding what should happen when, Anyway, here is one of your problems:
SELECT @LastScoreByTrustier=TrustedScore FROM MemberTrustLog WHERE MemberID = @MemberID AND TrustedBy = @TrustedBy
ORDER BY TrustDate DESC
You order by TrustDate DESC, but you don't limit the output to 1 value. Code of this type will - depending on how it is written - either throw an error ("subquery returned more than 1 value" or similar), or set the value of parameter to the last value encountered, which is - when ordering by date DESC - probably the OLDEST score, not the last one. I think that as you wrote it, it should work, but set parameter to incorrect valeu.
This should be closer to what you need:
SET @LastScoreByTrustier=(SELECT TOP 1 TrustedScore FROM MemberTrustLog WHERE MemberID = @MemberID AND TrustedBy = @TrustedBy
ORDER BY TrustDate DESC)
Sorry, didn't have enough time to check the rest of the code.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply