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 2005 Forums

Transact-SQL (2005)

Help with SQL-transact!

Author

Topic

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-27 : 12:37:51

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:25

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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

Duplicate post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111531

E 12°55'05.63"
N 56°04'39.26"

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-27 : 12:53:47

Should I delete dublicated post? I thought that the forum groups are different! Sorry guys!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-27 : 12:55:47

Either you are using SQL Server 2005 or SQL Server 2000, right?
Or are you using both platforms?

afrika, "circular reference"...

E 12°55'05.63"
N 56°04'39.26"

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-27 : 12:57:57

quote:
Originally posted by Peso

Either you are using SQL Server 2005 or SQL Server 2000, right?
Or are you using both platforms?

afrika, "circular reference"...

E 12°55'05.63"
N 56°04'39.26"

Hi Peso! Your yesterday brilliant solution made me post dublicated post in SQL2000. But I'm using 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 14:06:07

may be this:-

ALTER PROCEDURE dbo.tbh_Test_Portfolios_Images_ADD_to_TodayImage
(
@TodayImageID int OUTPUT
)

AS
SET NOCOUNT ON
DECLARE @TodayMaxRatingID int,@TodayMaxRatingIdVotes int


SELECT TOP 1 @TodayMaxRatingID =ImageID,@TodayMaxRatingIdVotes =TotalImageRating
FROM
(
SELECT ImageID, sumvotes+commentcount+view count AS TotalImageRating
INTO #userImages 
From tbh_Portfolios_Images t
cross apply (SELECT SUM(Vote) AS sumvotes FROM tbh_Portfolios_Images_Votes where ImageID = t.ImageID) b
cross apply (SELECT COUNT(*)  AS commentcount FROM tbh_Portfolios_Images_Comments where ImageID = t.ImageID) c
cross apply (SELECT COUNT(*) AS  view count FROM tbh_Portfolios_Images_ViewCounts where ImageID = t.ImageID)d
WHERE DATEADD(dd,DATEDIFF(dd,0, tbh_Portfolios_Images.AddedDate),0) = DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)
)t
ORDER BY t.TotalImageRating DESC


IF EXISTS(SELECT ImageID 
FROM tbh_Portfolios_Images_Today_Image
WHERE ImageID = @TodayMaxRatingID
AND DATEADD(dd,DATEDIFF(dd,0, AddedDate),0)= DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)
)
BEGIN
UPDATE tbh_Portfolios_Images_Today_Image
SET VotesCount = @TodayMaxRatingIdVotes
WHERE ImageID = @TodayMaxRatingID
AND DATEADD(dd,DATEDIFF(dd,0, AddedDate),0)= DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)
END
ELSE
BEGIN
DELETE tbh_Portfolios_Images_Today_Image
WHERE DATEADD(dd,DATEDIFF(dd,0, AddedDate),0)= DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)

INSERT INTO tbh_Portfolios_Images_Today_Image 
(ImageID, AddedDate, VotesCount)
VALUES (@TodayMaxRatingID, GETDATE(), @TodayMaxRatingIdVotes)
SET @TodayImageID = scope_identity()
END
GO

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-27 : 14:51:59

Visakh16! Look at this please!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 15:07:58

quote:
Originally posted by krainov

Visakh16! Look at this please!

remove the space between view & count. its viewcount one word.

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-27 : 15:12:45

But it still does not want to go
I got:

Incorrect syntax near keyword 'INTO'.
Incorrect syntax near 'b'.
Incorrect syntax near 'c'.
Incorrect syntax near 'd'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 15:19:30

what about this

ALTER PROCEDURE dbo.tbh_Test_Portfolios_Images_ADD_to_TodayImage
(
@TodayImageID int OUTPUT
)

AS
SET NOCOUNT ON
DECLARE @TodayMaxRatingID int,@TodayMaxRatingIdVotes int


SELECT TOP 1 @TodayMaxRatingID =r.ImageID,@TodayMaxRatingIdVotes =r.TotalImageRating
FROM
(
SELECT t.ImageID, b.sumvotes+c.commentcount+d.viewcount AS TotalImageRating
From tbh_Portfolios_Images t
cross apply (SELECT SUM(Vote) AS sumvotes FROM tbh_Portfolios_Images_Votes where ImageID = t.ImageID) b
cross apply (SELECT COUNT(*)  AS commentcount FROM tbh_Portfolios_Images_Comments where ImageID = t.ImageID) c
cross apply (SELECT COUNT(*) AS  viewcount FROM tbh_Portfolios_Images_ViewCounts where ImageID = t.ImageID)d
WHERE DATEADD(dd,DATEDIFF(dd,0, tbh_Portfolios_Images.AddedDate),0) = DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)
)r
ORDER BY r.TotalImageRating DESC


IF EXISTS(SELECT ImageID 
FROM tbh_Portfolios_Images_Today_Image
WHERE ImageID = @TodayMaxRatingID
AND DATEADD(dd,DATEDIFF(dd,0, AddedDate),0)= DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)
)
BEGIN
UPDATE tbh_Portfolios_Images_Today_Image
SET VotesCount = @TodayMaxRatingIdVotes
WHERE ImageID = @TodayMaxRatingID
AND DATEADD(dd,DATEDIFF(dd,0, AddedDate),0)= DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)
END
ELSE
BEGIN
DELETE FROM tbh_Portfolios_Images_Today_Image
WHERE DATEADD(dd,DATEDIFF(dd,0, AddedDate),0)= DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)

INSERT INTO tbh_Portfolios_Images_Today_Image 
(ImageID, AddedDate, VotesCount)
VALUES (@TodayMaxRatingID, GETDATE(), @TodayMaxRatingIdVotes)
SET @TodayImageID = scope_identity()
END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-27 : 15:19:57

Why are you using

WHERE DATEADD(dd,DATEDIFF(dd,0, AddedDate),0)= DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)

?? You can't use index anyway so this might be easier to read

WHERE DATEDIFF(DAY, AddedDate, GETDATE()) = 0

E 12°55'05.63"
N 56°04'39.26"

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-27 : 15:29:18

quote:
Originally posted by Peso

Why are you using

WHERE DATEADD(dd,DATEDIFF(dd,0, AddedDate),0)= DATEADD(dd,DATEDIFF(dd,0, GETDATE()),0)

?? You can't use index anyway so this might be easier to read

WHERE DATEDIFF(DAY, AddedDate, GETDATE()) = 0

E 12°55'05.63"
N 56°04'39.26"

Right Peso! Now it works! But there is another problem! String SELECT t.ImageID, b.sumvotes+c.commentcount+d.viewcount AS TotalImageRating returns NULL in case when the image has no comments & viewcounts yet. It refuses to insert NULL into Table

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-27 : 15:30:13

Visakh! Thanks for your effort & contributing into my project!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-28 : 01:39:25

SELECT t.ImageID, coalesce(b.sumvotes, 0) + coalesce(c.commentcount, 0) + coalesce(d.viewcount, 0) AS TotalImageRating

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-28 : 02:56:15

quote:
Originally posted by krainov

Visakh! Thanks for your effort & contributing into my project!

welcome

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-28 : 13:45:25

quote:
Originally posted by Peso

SELECT t.ImageID, coalesce(b.sumvotes, 0) + coalesce(c.commentcount, 0) + coalesce(d.viewcount, 0) AS TotalImageRating

E 12°55'05.63"
N 56°04'39.26"

Thanx PESO! I never thought that people can be so friendly on sharing experience to other!!! I spent 3 hrs to get rid of this NULL-bug in this procedure, and now your help is saving my time again! Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources