Need 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)

Need help with SQL-Transact

Author

Topic

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-26 : 10:49:17

Hi there!
Please help to find the solution for the procedure!
I have a table that has 4 cells where I put the Images that have a highest rating at the defined date
_________________________________________________________________
ID ImageID AddedDate TotalRating
---------------------------------------------------
12 308 12.09.2008 10:10:00 250
12 303 12.09.2008 10:10:00 250
12 302 12.08.2008 10:10:00 250
12 291 12.07.2008 10:10:00 250

And so on

I need to retrieve all the ImageIDs from table according to the Parameter @MonthAdded which is in DateTime format for the defined month&year

Here is my solution that does not work

ALTER PROCEDURE dbo.tbh_Portfolios_Favorites_GetFavoritesByMonth

(
@MonthAdded datetime
)
AS
SET NOCOUNT ON

SELECT tbh_Portfolios_Images_Today_Image.TodayImageID,
tbh_Portfolios_Images_Today_Image.AddedDate,
tbh_Portfolios_Images.AddedBy AS AddedBy,
tbh_Portfolios_Images_Today_Image.ImageID,
tbh_Portfolios_Images_Today_Image.VotesCount,
tbh_Portfolios_Images.MediaID AS MediaID,
tbh_Portfolios_Medias.Title AS MediaTitle,
tbh_Portfolios_Images.ThumbImage AS ThumbImage,
tbh_Portfolios_Images.Title AS Title,
tbh_Portfolios_Images.Comments AS CommentsCount

FROM tbh_Portfolios_Images_Today_Image

INNER JOIN tbh_Portfolios_Images
ON tbh_Portfolios_Images_Today_Image.ImageID = tbh_Portfolios_Images.ImageID
INNER JOIN tbh_Portfolios_Medias
ON tbh_Portfolios_Images.MediaID = tbh_Portfolios_Medias.MediaID

WHERE (CAST(MONTH(tbh_Portfolios_Images_Today_Image.AddedDate) AS VARCHAR(2)) + '/' +
CAST(YEAR(tbh_Portfolios_Images_Today_Image.AddedDate) AS VARCHAR(2))) =
(CAST(MONTH(@MonthAdded) AS VARCHAR(2)) + '/' +
CAST(YEAR(@MonthAdded) AS VARCHAR(4)))

Can anyone tell me where I go wrong? My procedure returns me 0.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 10:51:14

how do you pass month values through @MonthAdded ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 10:53:07

may be this

ALTER PROCEDURE dbo.tbh_Portfolios_Favorites_GetFavoritesByMonth

(
@MonthAdded datetime
)
AS
SET NOCOUNT ON



SELECT tbh_Portfolios_Images_Today_Image.TodayImageID,
tbh_Portfolios_Images_Today_Image.AddedDate,
tbh_Portfolios_Images.AddedBy AS AddedBy,
tbh_Portfolios_Images_Today_Image.ImageID,
tbh_Portfolios_Images_Today_Image.VotesCount,
tbh_Portfolios_Images.MediaID AS MediaID,
tbh_Portfolios_Medias.Title AS MediaTitle,
tbh_Portfolios_Images.ThumbImage AS ThumbImage,
tbh_Portfolios_Images.Title AS Title,
tbh_Portfolios_Images.Comments AS CommentsCount

FROM tbh_Portfolios_Images_Today_Image

INNER JOIN tbh_Portfolios_Images 
ON tbh_Portfolios_Images_Today_Image.ImageID = tbh_Portfolios_Images.ImageID
INNER JOIN tbh_Portfolios_Medias 
ON tbh_Portfolios_Images.MediaID = tbh_Portfolios_Medias.MediaID


WHERE DATEADD(mm,DATEDIFF(mm,0,tbh_Portfolios_Images_Today_Image.AddedDate),0)=
DATEADD(mm,DATEDIFF(mm,0,@MonthAdded),0)

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-26 : 10:53:41

quote:
Originally posted by visakh16

how do you pass month values through @MonthAdded ?

In the DateTime format!
That,s why I try to truncate them as (19.09.2008 10:10:00 to 09/2008 as NvarChar)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 10:55:22

quote:
Originally posted by krainov

quote:
Originally posted by visakh16

how do you pass month values through @MonthAdded ?

In the DateTime format!
That,s why I try to truncate them as (19.09.2008 10:10:00 to 09/2008 as NvarChar)

ok then try my posted solution

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-26 : 10:57:25

[/quote]
ok then try my posted solution
[/quote]

No rows affected
@Return_Value = 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 10:58:31

Why overcomplicate the WHERE clause?

ALTER PROCEDURE dbo.tbh_Portfolios_Favorites_GetFavoritesByMonth
(
	@MonthAdded datetime
)
AS

SET NOCOUNT ON

SELECT		piti.TodayImageID,
		piti.AddedDate,
		tbh_Portfolios_Images.AddedBy AS AddedBy,
		tbh_Portfolios_Images_Today_Image.ImageID,
		piti.VotesCount,
		tbh_Portfolios_Images.MediaID AS MediaID,
		tbh_Portfolios_Medias.Title AS MediaTitle,
		tbh_Portfolios_Images.ThumbImage AS ThumbImage,
		tbh_Portfolios_Images.Title AS Title,
		tbh_Portfolios_Images.Comments AS CommentsCount
FROM		tbh_Portfolios_Images_Today_Image AS piti
INNER JOIN	tbh_Portfolios_Images AS [pi] ON [pi].ImageID = piti.ImageID
INNER JOIN	tbh_Portfolios_Medias AS pm ON pm.MediaID = [pi].MediaID
WHERE		DATEDIFF(MONTH, piti.AddedDate, @MonthAdded) = 0

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

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-26 : 11:01:00

quote:
Originally posted by Peso

Why overcomplicate the WHERE clause?
WHERE DATEDIFF(MONTH, tbh_Portfolios_Images_Today_Image.AddedDate, @MonthAdded) = 0

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

Same result!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:03:18

And if you want to make use of an existing index over AddedDate, use this

ALTER PROCEDURE dbo.tbh_Portfolios_Favorites_GetFavoritesByMonth
(
	@MonthAdded datetime
)
AS

SET NOCOUNT ON

SET	@MonthAdded = DATEADD(MONTH, DATEDIFF(MONTH, '20000101', @MonthAdded), '20000101')

SELECT		piti.TodayImageID,
		piti.AddedDate,
		tbh_Portfolios_Images.AddedBy AS AddedBy,
		tbh_Portfolios_Images_Today_Image.ImageID,
		piti.VotesCount,
		tbh_Portfolios_Images.MediaID AS MediaID,
		tbh_Portfolios_Medias.Title AS MediaTitle,
		tbh_Portfolios_Images.ThumbImage AS ThumbImage,
		tbh_Portfolios_Images.Title AS Title,
		tbh_Portfolios_Images.Comments AS CommentsCount
FROM		tbh_Portfolios_Images_Today_Image AS piti
INNER JOIN	tbh_Portfolios_Images AS [pi] ON [pi].ImageID = piti.ImageID
INNER JOIN	tbh_Portfolios_Medias AS pm ON pm.MediaID = [pi].MediaID
WHERE		piti.AddedDate >= @MonthAdded
		AND piti.AddedDate < DATEADD(MONTH, 1, @MonthAdded)

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:04:26

Which value for @MonthAdded are you using?
For that value, what is the expected resultset for the orignal sample data?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:07:54

[code]DECLARE @Sample TABLE
(
ID TINYINT,
ImageID SMALLINT,
AddedDate SMALLDATETIME,
TotalRating SMALLINT
)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 12, 308, '12.09.2008 10:10:00', 250 UNION ALL
SELECT 12, 303, '12.09.2008 10:10:00', 250 UNION ALL
SELECT 12, 302, '12.08.2008 10:10:00', 250 UNION ALL
SELECT 12, 291, '12.07.2008 10:10:00', 250

DECLARE @MonthAdded DATETIME

SET @MonthAdded = '18.9.2008'

SELECT *
FROM @Sample
WHERE DATEDIFF(MONTH, AddedDate, @MonthAdded) = 0[/code]

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

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-26 : 11:10:01

quote:
Originally posted by Peso

Which value for @MonthAdded are you using?
For that value, what is the expected resultset for the orignal sample data?

I want to get the ImageIDs for the month&year of the @MonthAdded.
The procedure must return all the images that had maximum Rating for the Month & Year. For example: @MonthAdded = 23.09.2008 10:10:00, I need all ImageIds with 09.2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:22:18

Yes, and that is EXACTLY what the examples does that we have given you.
Did you even try my example below?

DECLARE	@Sample TABLE
	(
		ID TINYINT,
		ImageID SMALLINT,
		AddedDate SMALLDATETIME,
		TotalRating SMALLINT
	)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	12, 308, '12.09.2008 10:10:00', 250 UNION ALL
SELECT	12, 303, '12.09.2008 10:10:00', 250 UNION ALL
SELECT	12, 302, '12.08.2008 10:10:00', 250 UNION ALL
SELECT	12, 291, '12.07.2008 10:10:00', 250

DECLARE	@MonthAdded DATETIME

SET	@MonthAdded = '18.9.2008'

SELECT	*
FROM	@Sample
WHERE	DATEDIFF(MONTH, AddedDate, @MonthAdded) = 0

And what do you mean by "maximum rating"?

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

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-26 : 11:24:03

quote:

Originally posted by Peso

Yes, and that is EXACTLY what the examples does that we have given you.
Did you even try my example below?

DECLARE	@Sample TABLE
	(
		ID TINYINT,
		ImageID SMALLINT,
		AddedDate SMALLDATETIME,
		TotalRating SMALLINT
	)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	12, 308, '12.09.2008 10:10:00', 250 UNION ALL
SELECT	12, 303, '12.09.2008 10:10:00', 250 UNION ALL
SELECT	12, 302, '12.08.2008 10:10:00', 250 UNION ALL
SELECT	12, 291, '12.07.2008 10:10:00', 250

DECLARE	@MonthAdded DATETIME

SET	@MonthAdded = '18.9.2008'

SELECT	*
FROM	@Sample
WHERE	DATEDIFF(MONTH, AddedDate, @MonthAdded) = 0

And what do you mean by "maximum rating"?

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

Yes I tried! Same ZERO Result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 11:26:42

quote:
Originally posted by krainov

quote:
Originally posted by Peso

Yes, and that is EXACTLY what the examples does that we have given you.
Did you even try my example below?
DECLARE @Sample TABLE
(
ID TINYINT,
ImageID SMALLINT,
AddedDate SMALLDATETIME,
TotalRating SMALLINT
)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 12, 308, '12.09.2008 10:10:00', 250 UNION ALL
SELECT 12, 303, '12.09.2008 10:10:00', 250 UNION ALL
SELECT 12, 302, '12.08.2008 10:10:00', 250 UNION ALL
SELECT 12, 291, '12.07.2008 10:10:00', 250

DECLARE @MonthAdded DATETIME

SET @MonthAdded = '18.9.2008'

SELECT *
FROM @Sample
WHERE DATEDIFF(MONTH, AddedDate, @MonthAdded) = 0
And what do you mean by "maximum rating"?

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

Yes I tried! Same ZERO Result

is Addeddate a datetime field?

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-26 : 11:26:44

Here is how it looks now:

ALTER PROCEDURE dbo.tbh_Portfolios_Favorites_GetFavoritesByMonth
(
@MonthAdded datetime
)
AS
SET NOCOUNT ON

SET @MonthAdded = DATEADD(MONTH, DATEDIFF(MONTH, '20000101', @MonthAdded), '20000101')

SELECT piti.TodayImageID,
piti.AddedDate,
[pi].AddedBy AS AddedBy,
piti.ImageID,
piti.VotesCount,
pm.MediaID AS MediaID,
pm.Title AS MediaTitle,
[pi].ThumbImage AS ThumbImage,
[pi].Title AS Title,
[pi].Comments AS CommentsCount
FROM tbh_Portfolios_Images_Today_Image AS piti
INNER JOIN tbh_Portfolios_Images AS [pi] ON [pi].ImageID = piti.ImageID
INNER JOIN tbh_Portfolios_Medias AS pm ON pm.MediaID = [pi].MediaID
WHERE piti.AddedDate >= @MonthAdded
AND piti.AddedDate < DATEADD(MONTH, 1, @MonthAdded)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:29:09

Krainov, do you really mean you get no records back at all with this example?

DECLARE	@Sample TABLE
	(
		ID TINYINT,
		ImageID SMALLINT,
		AddedDate SMALLDATETIME,
		TotalRating SMALLINT
	)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	12, 308, '12.09.2008 10:10:00', 250 UNION ALL
SELECT	12, 303, '12.09.2008 10:10:00', 250 UNION ALL
SELECT	12, 302, '12.08.2008 10:10:00', 250 UNION ALL
SELECT	12, 291, '12.07.2008 10:10:00', 250

DECLARE	@MonthAdded DATETIME

SET	@MonthAdded = '18.9.2008'

SELECT	*
FROM	@Sample
WHERE	DATEDIFF(MONTH, AddedDate, @MonthAdded) = 0

I get imageID 303 and imageID 308 as result.
And I can trust you with that everyone else will too.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:32:07

quote:
Originally posted by visakh16

is Addeddate a datetime field?

Even if AddedDate is a VARCHAR, the example code below will work

DECLARE	@Sample TABLE
	(
		ID TINYINT,
		ImageID SMALLINT,
		AddedDate VARCHAR(200),
		TotalRating SMALLINT
	)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	12, 308, '12.09.2008 10:10:00', 250 UNION ALL
SELECT	12, 303, '12.09.2008 10:10:00', 250 UNION ALL
SELECT	12, 302, '12.08.2008 10:10:00', 250 UNION ALL
SELECT	12, 291, '12.07.2008 10:10:00', 250

DECLARE	@MonthAdded DATETIME

SET	@MonthAdded = '18.9.2008'

SELECT	*
FROM	@Sample
WHERE	DATEDIFF(MONTH, AddedDate, @MonthAdded) = 0

I still get records 303 and 308 back.

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

krainov
Yak Posting Veteran

57 Posts

Posted - 2008-09-26 : 11:39:10

Look!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:44:11

Wow! I can see clearly now.

Krainov. Let's start with the basics.
Do you, or do you not, get any records back using this sample case?

DECLARE	@Sample TABLE
	(
		ID TINYINT,
		ImageID SMALLINT,
		AddedDate SMALLDATETIME,
		TotalRating SMALLINT
	)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	12, 308, '12.09.2008 10:10:00', 250 UNION ALL
SELECT	12, 303, '12.09.2008 10:10:00', 250 UNION ALL
SELECT	12, 302, '12.08.2008 10:10:00', 250 UNION ALL
SELECT	12, 291, '12.07.2008 10:10:00', 250

DECLARE	@MonthAdded DATETIME

SET	@MonthAdded = '18.9.2008'

SELECT	*
FROM	@Sample
WHERE	DATEDIFF(MONTH, AddedDate, @MonthAdded) = 0

Yes or No answer only please.

If Yes, which records?
If No, you are lying.

When you answer Yes (and 303+308), please proceed to next step posted below.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 11:46:46

When you have answered that question, let's proceed with the basics.
If you run this query against your database, which result do you get. Can you post the result back here, please?

SELECT		DATEADD(MONTH, DATEDIFF(MONTH, '19000101', piti.AddedDate), '19000101') AS theMonth,
		COUNT(*) AS theCount
FROM		tbh_Portfolios_Images_Today_Image AS piti
INNER JOIN	tbh_Portfolios_Images AS [pi] ON [pi].ImageID = piti.ImageID
INNER JOIN	tbh_Portfolios_Medias AS pm ON pm.MediaID = [pi].MediaID
GROUP BY	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', piti.AddedDate), '19000101')
ORDER BY	DATEADD(MONTH, DATEDIFF(MONTH, '19000101', piti.AddedDate), '19000101')

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources