Help with SQL-transact! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with SQL-transact!

Author

Topic

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-27 : 12:40:56

Hi there!
Need help on this complicated way to say Hooray :-)

As a part of an web site I have a Photo Gallery where I decided to have "Today Best Photo" - kind of addition to make the site visitors feel the spirit of competition posting the images to the web site.

So there are actually 5 tables created for this PhotoGallery which are: Categories, Medias(as Albums), Images, ImageComments, CommentsCount, ImagesViewCounts, ImagesVotes, TodayFavoriteImages.

When the new image is inserted into table "Images" today, and right after someone is Voting on photo or inserting the new comment on it I run this MESS MY POSSIBILITIES (See the transact body below:-) So dont blame me! This is my first startUp project on asp.net! This procedure is inserting the ImageID, Date, and TotalTodayRating into "TodayFavoriteImages" table. The TotalTodayRating consist of number of today ViewCounts, todayVotes (values from 1 to 5) and today CommentsCount.
I know that this way is too complicated and that there are other easier ways! Help to simplify this procedure!

ALTER PROCEDURE dbo.tbh_Test_Portfolios_Images_ADD_to_TodayImage
(
@TodayImageID int OUTPUT
)

AS
SET NOCOUNT ON

/*Declare TODAY date*/
DECLARE @TodayIs datetime
SELECT @TodayIs = GetDate()

/*All today images into tempTable*/
SELECT ImageID
INTO #userImages
From tbh_Portfolios_Images
WHERE DATEDIFF(DAY,'', tbh_Portfolios_Images.AddedDate) = DATEDIFF(DAY, '', @TodayIs)

/*1 tempTable more for collecting the viewCouns, Sum Votes and commentsCount
???-?? ?????????? ?? ???????, ????? ?????? ? ?????????? ????????????*/
SELECT

--ID
ImageID,

(
--TotalRating
(SELECT SUM(Vote) FROM tbh_Portfolios_Images_Votes where ImageID = #userImages.ImageID) +

--TodayComments
(SELECT COUNT(*) FROM tbh_Portfolios_Images_Comments where ImageID = #userImages.ImageID) +

--TodayViewCount
(SELECT COUNT(*) FROM tbh_Portfolios_Images_ViewCounts where ImageID = #userImages.ImageID)

) AS TotalImageRating

INTO #todayImages
FROM #userImages

/*Declare highest today rating imageID */
DECLARE @TodayMaxRatingID int

/*Find highest today rating imageID in #todayImages*/
SELECT @TodayMaxRatingID = (SELECT TOP 1 ImageID
FROM #todayImages
ORDER BY TotalImageRating DESC)

/*Lets count the @TodayMaxRatingIdVotes*/
DECLARE @TodayMaxRatingIdVotes int
SELECT @TodayMaxRatingIdVotes = (SELECT TotalImageRating
FROM #todayImages
WHERE ImageID = @TodayMaxRatingID)

-- check is there @TodayMaxRatingID in table
DECLARE @CurrID int
SELECT @CurrID = ImageID
FROM tbh_Portfolios_Images_Today_Image
WHERE ImageID = @TodayMaxRatingID
AND DATEDIFF(DAY,'', AddedDate) = DATEDIFF(DAY, '', @TodayIs)

--If yes
IF @CurrID IS NOT NULL
BEGIN
/*Refresh VotesCount in table /
UPDATE tbh_Portfolios_Images_Today_Image
SET VotesCount = @TodayMaxRatingIdVotes
WHERE ImageID = @TodayMaxRatingID
RETURN
END

--If not
IF @CurrID IS NULL
BEGIN
--Delete the image that was before this one
DELETE tbh_Portfolios_Images_Today_Image
WHERE DATEDIFF(DAY,'', tbh_Portfolios_Images_Today_Image.AddedDate) = DATEDIFF(DAY, '', @TodayIs)

/*Insert new record"*/
INSERT INTO tbh_Portfolios_Images_Today_Image
(ImageID, AddedDate, VotesCount)
VALUES (@TodayMaxRatingID, @TodayIs, @TodayMaxRatingIdVotes)
SET @TodayImageID = scope_identity()

END

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-27 : 12:46:41

duplicate
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111530

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources