Display record of the day - 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)

Display record of the day

Author

Topic

new_bees
Starting Member

27 Posts

Posted - 2008-05-22 : 16:33:46

Hi Guys,

How do I display an item in a day? This is a featured product of the day. This will change everyday. I can do this in classic ASP but not in stored procedure.

Table:
This is just to give you an idea of the table
ID | ProductName | DatePosted
----------------------------------------
1 | item1 | 5/1/2008
2 | item2 | 1/2/2008
3 | item3 | 6/2/2007

and so on...

Can someone please help me?

Thanks in advanced.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-22 : 16:36:15

What do you want the query to output?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

new_bees
Starting Member

27 Posts

Posted - 2008-05-23 : 01:08:42

tkizer,

I want it to output different product everyday. I have this working but it is in ASP classic. Maybe you can help me translate this ASP SQL code to stored procedure.

set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open "SELECT ID, CAT_ID, Category, Ingredients, Instructions, Name, Author, (RATING/ NO_RATES) AS RATES, NO_RATES, Date, HITS, TOTAL_COMMENTS FROM Recipes order by ID ", my_Conn, 3, 1'
Dim JulianDate
JulianDate = DateDiff("d","1/1/" & Year(date),date + 1)
objRS.Move JulianDate

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-23 : 01:13:58

If you just want a random product, then you can use this:

SELECT TOP 1 ProductName
FROM Product
ORDER BY NEWID()

But if you just want to use your current code, then just grab your query and wrap it into a stored procedure. If you need help on writing stored procedures, you should consult SQL Server Books Online for examples and details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 02:05:11

quote:
Originally posted by new_bees

Hi Guys,

How do I display an item in a day? This is a featured product of the day. This will change everyday. I can do this in classic ASP but not in stored procedure.

Table:
This is just to give you an idea of the table
ID | ProductName | DatePosted
----------------------------------------
1 | item1 | 5/1/2008
2 | item2 | 1/2/2008
3 | item3 | 6/2/2007

and so on...

Can someone please help me?

Thanks in advanced.

Is this what you're looking at?

SELECT * FROM Table WHERE ProductName=@ProductName AND DatePosted=@Date
where @ProductName is item you're looking for @Date

new_bees
Starting Member

27 Posts

Posted - 2008-05-23 : 02:05:49

tkizer,

Thanks for the advice. The order by newid() will return random record everytime the page is access. This is not what I want. I want
to show random record for 1 day and change the following day. I have this working in classic ASP that show random record in a day. But I'm having difficulty implementing it to stored procedure.

Thanks

new_bees
Starting Member

27 Posts

Posted - 2008-05-23 : 02:09:02

Thanks visakh,

No, that is not what I want.
I want something like this site http://allrecipes.com/
were they have a feature called "Recipe of the Day".

I posted the working code in ASP 3.0 ADO. Now I'm trying to convert that to stored procedure.

set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open "SELECT ID, CAT_ID, Category, Ingredients, Instructions, Name, Author, (RATING/ NO_RATES) AS RATES, NO_RATES, Date, HITS, TOTAL_COMMENTS FROM Recipes order by ID ", my_Conn, 3, 1'
Dim JulianDate
JulianDate = DateDiff("d","1/1/" & Year(date),date + 1)
objRS.Move JulianDate

Can you help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 02:17:07

quote:
Originally posted by new_bees

Thanks visakh,

No, that is not what I want.
I want something like this site http://allrecipes.com/
were they have a feature called "Recipe of the Day".

I posted the working code in ASP 3.0 ADO. Now I'm trying to convert that to stored procedure.

set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open "SELECT ID, CAT_ID, Category, Ingredients, Instructions, Name, Author, (RATING/ NO_RATES) AS RATES, NO_RATES, Date, HITS, TOTAL_COMMENTS FROM Recipes order by ID ", my_Conn, 3, 1'
Dim JulianDate
JulianDate = DateDiff("d","1/1/" & Year(date),date + 1)
objRS.Move JulianDate

Can you help?

I think you can put the above SELECT statement inside a SP and call it from ASP like this:-

http://support.microsoft.com/kb/164485

new_bees
Starting Member

27 Posts

Posted - 2008-05-23 : 02:28:54

visakh,

I tried it, and it failed. I read similar situation on someone's blog, but no solution.
http://www.netnewsgroups.net/group/microsoft.public.dotnet.framework.aspnet/topic438.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 03:18:05

Try this

SELECT	ID,
	CAT_ID,
	Category,
	Ingredients,
	Instructions,
	Name,
	Author,
	RATES,
	NO_RATES,
	Date,
	HITS,
	TOTAL_COMMENTS
FROM	(
		SELECT	ID,
			CAT_ID,
			Category,
			Ingredients,
			Instructions,
			Name,
			Author,
			1.0 * RATING / NO_RATES AS RATES,
			NO_RATES,
			Date,
			HITS,
			TOTAL_COMMENTS,
			ROW_NUMBER() OVER (ORDER BY ID) AS RecID
		FROM	Recipes
	) AS d
WHERE	RecID = DATEPART(DAYOFYEAR, GETDATE())

E 12°55'05.25"
N 56°04'39.16"

new_bees
Starting Member

27 Posts

Posted - 2008-05-24 : 10:31:50

Peso,

The code works perfectly.

Another question.

How do I go through all 2,000 records and randomly pick 1 record for each day, but not based on DAYOFYEAR?

What to change in the code?

Right now the code look for the record with an ID number that match the DAYOFYEAR.

Thank you!

New B

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-25 : 02:35:06

[code]SELECT ID,
CAT_ID,
Category,
Ingredients,
Instructions,
Name,
Author,
RATES,
NO_RATES,
Date,
HITS,
TOTAL_COMMENTS
FROM (
SELECT ID,
CAT_ID,
Category,
Ingredients,
Instructions,
Name,
Author,
1.0 * RATING / NO_RATES AS RATES,
NO_RATES,
Date,
HITS,
TOTAL_COMMENTS,
ROW_NUMBER() OVER (ORDER BY ID) - 1 AS RecID
FROM Recipes
) AS d
WHERE RecID = ABS(CHECKSUM(CONVERT(VARCHAR(10), GETDATE(), 120))) % (SELECT COUNT(*) FROM Recipes)[/code]

E 12°55'05.25"
N 56°04'39.16"

new_bees
Starting Member

27 Posts

Posted - 2008-05-28 : 15:02:46

Peso,

You are my savior. Thank you very much. That's what I'm looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources