help with SP - 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)

help with SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-05-29 : 01:21:55

I have a table called TBLIM that stores mail for registered users. Each time a registered user is loggedin, it checks for new mail each page rendering. The problem is this table is about 50,000 records and its starting to be a heavy task. Every hour I delete mail that is older than 24 hours to save speed. I keep of mail, so users can view what they have said to other users and such.

What I do now is this. I run this every hour:

DELETE tblIM WHERE DateDiff(hh, date, GetDate()) > 24 AND checked ='1'

This deletes all checked messages that are over 24 hours old. I want to further this by deleting messages that fit this criteria.

It doesnt matter how old they are.
They have to be checked.
If they are checked, I want to delete all messages except for the newest one to each recipietn user.

Thats a bit hard to explain, I'll try to be clear with another example

TBLIM

userFrom UserTo Message Date Checked

100 200 'hi' 9:00pm 1
100 200 'hello' 10pm 1
100 200 'goodnight' 11pm 1
123 200 'messagehere' 9pm 1

For example, the bolded rows would be the only rows kept in the above situation.

This is why I am doing it like this, any critism is welcome.

The rows add up quickly, and when users use this simulated instant message to other users they sometimes forget what they said. So I want them to be able to view the last thing they said to each user.

A bit complicated yes. If anybody can help me out it would be greatly appreciated. Hopefully my explanation is clear enough.

Thanks again .

Mike123

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-29 : 01:59:22

mike123,

Where is the key?

This should get you started though...


DELETE T
FROM tblim t
WHERE Checked = 1 
AND NOT EXISTS
(
SELECT 1 FROM 
tblim WHERE Checked = 1  AND UserFrom = t.UserFrom
GROUP BY UserFrom 
HAVING MAX(Date) = t.DATE
)

DavidM

"SQL-3 is an abomination.."

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-06-02 : 07:34:54

byrmol, thanks for the help

Sorry I forgot the key. The key is an identity column of datatype INT, column name instantmessageID.

I get the following error when trying to run your code. Any ideas?

Server: Msg 8121, Level 16, State 1, Line 1
Column 'tblIM.Date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

thanks alot

mike123

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-02 : 10:12:25

First setup a query to return the DateTime of the last checked IM for each user.

SELECT UserID, MAX(IMDateTime) AS LastIMDate
FROM tblIM GROUP BY UserID

Works? Now plug this into a second query which will retun the InstantMessageID for each of these messages.

SELECT InstantMessageID FROM tblIM T
INNER JOIN
(SELECT UserID, MAX(IMDateTime) AS LastIMDate
FROM tblIM GROUP BY UserID) A
ON T.UserID = A.UserID AND T.IMDateTime = A.LastIMDate

Now you have the list of InstantMessageIDs that must not be deleted. Its pretty straight forward from here on. You want to delete all other message except these ones:

DELETE tblIM WHERE InstantMessageID NOT IN
(SELECT InstantMessageID FROM tblIM T
INNER JOIN
(SELECT UserID, MAX(IMDateTime) AS LastIMDate
FROM tblIM GROUP BY UserID) A
ON T.UserID = A.UserID AND T.IMDateTime = A.LastIMDate)

[b]EDIT: I forgot to add the "AND Checked = 1" filter. Add wherever required.

Hope this helps with the problem.

Owais

Edited by - mohdowais on 06/02/2003 10:15:36

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources