help with maintenance delete query

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)

help with maintenance delete query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-07 : 05:24:38

Hi,

I have a table that grows pretty fast and I am trying to keep it under control by deleting older records. Table structure is below.

I want to delete all records older than 30 days
WHERE
the user that has been viewed (profileUserID) has more than 50 unique records

To explain this literally... This table logs profiles looking at other profiles. I want users to be able to view "who's viewed me", but I want to delete the information after 30 days. If the person doesn't have 50 people viewing them in 30 days, Id like to preserve the records older than our cut off date, until we reach the 50 records.

(Optional: To make things more complicated people can view profiles multiple times and its logged. if user 100 looks at user 500, 50 times in thirty days, id prefer not to count these records. So it would be better to count unique views. The duplicate views from user 100 looking at user 500 could all be deleted EXCEPT for the most recent one)

Hopefully thats not too confusing, I know its making my head spin just looking at it, but I know theres a few people nothing short of genius on here :)

Any help provided is very much appreciated, as always

DELETE tblProfileViews_Users WHERE ...



CREATE TABLE [dbo].[tblProfileViews_Users](
	[viewID] [int] IDENTITY(1,1) NOT NULL,
	[viewerUserID] [int] NOT NULL,
	[profileUserID] [int] NOT NULL,
	[viewDate] [smalldatetime] NOT NULL
) ON [PRIMARY]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 06:32:23

Something similar to this

DELETE		u
FROM		tblProfileViews_Users AS u
INNER JOIN	(
			SELECT		ProfileUserID,
					COUNT(DISTINCT ViewerUserID) AS ViewCount,
					MAX(ViewID) AS LastID
			FROM		tblProfileViews_Users
			WHERE		ViewDate >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP)
			GROUP BY	ProfileUserID
		) AS b ON b.ProfileUserID = u.ProfileUserID
WHERE		b.ViewCount >= 50
		AND b.LastID > u.ViewID

I always like helping people working for FaceBook

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

kutumbarao
Starting Member

13 Posts

Posted - 2007-12-07 : 06:39:31

Hi Mike,
Please try with this

-- TO DELETE DUPLICATE VIEWS
DELETE tblProfileViews_Users
FROM tblProfileViews_Users P
LEFT JOIN (SELECT viewerUserID, profileUserID, MAX(viewDate) viewDate
FROM tblProfileViews_Users GROUP BY viewerUserID, profileUserID) M
ON M.viewerUserID = P.viewerUserID AND M.profileUserID = P.profileUserID AND M.viewDate = P.viewDate
WHERE M.viewerUserID IS NULL

-- DELETE ALL RECORDS OLDER THAN 30 DAYS, IF THAT PROFILE USER HAVE MORE THAN 50 VIEWERS
DELETE tblProfileViews_Users
FROM tblProfileViews_Users P
LEFT JOIN (SELECT profileUserID FROM tblProfileViews_Users GROUP BY profileUserID HAVING COUNT(viewerUserID) > 50) M
ON M.profileUserID = P.profileUserID
WHERE viewDate > DATEADD(DAY,30,GETDATE()) AND M.profileUserID IS NOT NULL

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-12-07 : 08:12:38

quote:

Originally posted by Peso

Something similar to this

DELETE		u
FROM		tblProfileViews_Users AS u
INNER JOIN	(
			SELECT		ProfileUserID,
					COUNT(DISTINCT ViewerUserID) AS ViewCount,
					MAX(ViewID) AS LastID
			FROM		tblProfileViews_Users
			WHERE		ViewDate >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP)
			GROUP BY	ProfileUserID
		) AS b ON b.ProfileUserID = u.ProfileUserID
WHERE		b.ViewCount >= 50
		AND b.LastID > u.ViewID

I always like helping people working for FaceBook

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

LOL, thanks peso!!. I will try this out and report back .. not facebook they might be in trouble if I was there DBA :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources