Always return 3 items even if enddate has passed

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)

Always return 3 items even if enddate has passed

Author

Topic

amjad171
Starting Member

15 Posts

Posted - 2011-04-12 : 05:47:23

Hello,

I am writing a stored procedure to return items from a database, this is a news system that I am creating, the news has a startdate and an enddate.

What I want is to make sure that there are always 3 news items displayed on the webpage, even if the enddate has passed, this will not happen often as news is going to be added frequently but for the odd month I want to make sure there is always a news item displayed...below is the code for the stored procedure I've created to return the news items, can anyone help with what I need to add to make sure there are always 3 items returned even if enddate has passed??

SELECT featureID, title, featureimage, featuredescription, link, startdate, enddate, imagealt 
		FROM tbHomepage_Features
		Where featureID = @featureID
		Order by startdate DESC

thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-12 : 06:09:58

[code]SELECT TOP(3) featureID,
title,
featureimage,
featuredescription,
link,
startdate,
enddate,
imagealt
FROM dbo.tbHomepage_Features
WHERE featureID = @featureID
ORDER BY startdate DESC,
ISNULL(enddate, '99991231') DESC[/code]

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

amjad171
Starting Member

15 Posts

Posted - 2011-04-12 : 06:32:29

Many thanks Peso...

Can I just ask what the line below means?:

ISNULL(enddate, '99991231') DESC

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-12 : 07:17:40

If there is no enddate (enddate is null) it means in most cases that the article is open forever.
And with forever, I interpret as december 31, 9999.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-12 : 07:18:00

If there is no enddate (enddate is null) it means in most cases that the article is open forever.
And with forever, I interpret as december 31, 9999.

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

amjad171
Starting Member

15 Posts

Posted - 2011-04-12 : 08:00:56

Does this require the enddate to be set as "null"?

initially when the news will be added there will be an enddate added aswell to determine when the news is finished, does the SELECT statement you have sent automatically make the enddate of the TOP 3 items null?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-12 : 08:22:49

No. Only when there is no enddate present, the date value of 99991231 will be used.

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

amjad171
Starting Member

15 Posts

Posted - 2011-04-12 : 09:39:55

HI,

There will always be an enddate no matter what, but what I want is for it to show the top 3 items even when the enddate has expired, this is because I want to make sure that at all times there are atleast 3 news items on the website even if that means one of them has gone passed its enddate...

Is this possible?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-12 : 14:46:25

Post some sample data and expected output.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources