Next, populate that table with the SKU (not the item number), the Start Date and the End Date that you wish the item to be featured. READ

BELOW FOR CAVEATS.

Now, create this SP by running the script below:

USE [DotNetNuke]

CREATE PROC dbo.AES_UpdateFeaturedItems AS

BEGIN

SELECT SKU, StartDate, EndDate

INTO #tmp_Featured

FROM dbo.AES_FeaturedItems t WHERE

(DATEPART(month,t.StartDate) <= DATEPART(month,GETDATE())

AND DATEPART(day,t.StartDate) <= DATEPART(day,GETDATE()))

AND

(DATEPART(month,t.EndDate) >= DATEPART(Month,GETDATE())

AND DATEPART(day,t.EndDate) >= DATEPART(day,GETDATE()));

/* Clear the Featured Items flag in the Smith_Products table */

UPDATE dbo.Smith_Products SET Featured=0;

/* Set the Featured Item flag in the database accordingly */

UPDATE dbo.Smith_Products

SET Featured=1

FROM dbo.Smith_Products sp

INNER JOIN #tmp_Featured tf

ON sp.ModelNumber = tf.SKU;

END

..... now, here's the caveats.

It does NOT pay attention to the YEAR. I wrote this routine because we have items that are featured the same time every year; for example, christmas stuff during christmas. The routine IGNORES the year, allowing you to "set it and forget it".

Don't cross new years with the dates.... I haven't re-written the stored procedure yet for something to begin in November and end in January.... (because 11 would be greater than 1, it doesn't work well). If something is going to be featured from November through the end of January, put the same item in for November through Dec. 31, then Jan 1 through the end. That will keep things straight.

The update script needs to run. This is kind of obvious.... but to make it do so, you can either make a task in SQL Server (not available in Express), OR, you can put the following code in a batch file, and set it to run as a Scheduled Task under windows: osql -UUser -PPassword -SDB2010 -h-1 -Q"DotNetNuke.dbo.AES_UpdateFeaturedItems" -w65535

... that's all there is to it! I run my batch file nightly, so that it updates my featured items on my web page, depending on the dates in the database.