SQL Query to expire messages at a given date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Thorn - 26 May 2006 13:34 GMT

Hello all,

I have a SQL Code to retreive messages from a server that have been posted
in the last 7 days. See below.

SELECT TOP 100 PERCENT Name, Subject, [Date], Message, messageID
FROM dbo.Messages
WHERE ([Date] > GETDATE() - 7)
ORDER BY messageID DESC

I wish to add an additional field for message expiration. Ie. If an expiry
date is set, the message will not fall off the end of the list until that
date has past or if this is not entered, it simply reverts back to the 7 day
age rule.

Can someone please help me out with this one [or give me a complete solution
;-)] ?

Many thanks,
John

Reply to this Message

Lucas Kartawidjaja - 26 May 2006 20:14 GMT

Hi John

There are several ways to do this. Here's one of them. Once you add the
ExpiryDate column, you can use the following sql query:

SELECT Name, Subject, [Date], Message, messageID, ExpiryDate, 1 AS
OrderFlag
FROM dbo.Messages
WHERE ([ExpiryDate] >= GETDATE())
UNION
SELECT Name, Subject, [Date], Message, messageID, ExpiryDate, 2 AS
OrderFlag
FROM dbo.Messages
WHERE ([Date] > GETDATE() - 7)
ORDER BY OrderFlag ASC

Hopefully this is what you are looking for.

Lucas

> Hello all,
>
[quoted text clipped - 16 lines]
> Many thanks,
> John

Reply to this Message

John Thorn - 30 May 2006 14:00 GMT

Hi Lucas
This code looks to me like it will do the job, but I still need further
help: When I whack it in to SQL Query Analyser and parse, it returns:
Server: Msg 8163, Level 16, State 1, Procedure LastFewEntries, Line 4
The text, ntext, or image data type cannot be selected as DISTINCT.

I had to alter the SELECT lines to SELECT TOP 100 PERCENT Name,..... because
it was complaining about the ORDER BY.

Many thanks in advance,
John

> Hi John
>
[quoted text clipped - 40 lines]
>> Many thanks,
>> John

Reply to this Message