quote:

---

Originally posted by Peso

You can start with two small simple tables demonstrating your need and businees rules.

Do you really need all columns from all tables?

---

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

---

CREATE PROCEDURE dbo.uspGetUserActivityList
(
	@userID INT
)
AS

SET NOCOUNT ON

DECLARE	@Friends TABLE
	(
		friendID INT PRIMARY KEY CLUSTERED,
		nameOnline VARCHAR(15)
	)

INSERT		@Friends
		(
			friendID,
			nameOnline
		)
SELECT		f.friendID,
		u.nameOnline
FROM		Friends AS f
INNER JOIN	Users AS u ON u.userID = f.friendID
WHERE		f.userID = @UserID

-- Present the resultset
SELECT		f.nameOnline + ' added a new photo ' + CONVERT(CHAR(19), ep.maxDate, 120) AS msg,
		ep.MaxDate AS theDate,
		1 AS theType
FROM		@Friends AS f
INNER JOIN	(
			SELECT		userID,
					MAX(photoDate) AS maxDate
			FROM		ExtraPhotos
			GROUP BY	userID
		) AS ep	ON ep.userID = f.friendID

UNION ALL

SELECT		f.nameOnline + ' added a new friend ' + CONVERT(CHAR(19), u.maxDate, 120) AS msg,
		u.MaxDate AS theDate,
		2 AS theType
FROM		@Friends AS f
INNER JOIN	(
			SELECT		userID,
					MAX(dateAdded) AS maxDate
			FROM		Friends
			GROUP BY	userID
		) AS u ON u.userID = f.friendID

ORDER BY	theType,
		theDate DESC

CREATE PROCEDURE dbo.uspGetUserActivityList
(
	@userID INT
)
AS

SET NOCOUNT ON

DECLARE	@lastLogin DATETIME

SELECT	@lastLogin = MAX(loginDate)
FROM	LoginHistory
WHERE	userID = @userID

DECLARE	@Friends TABLE
	(
		friendID INT PRIMARY KEY CLUSTERED,
		nameOnline VARCHAR(15)
	)

INSERT		@Friends
		(
			friendID,
			nameOnline
		)
SELECT		f.friendID,
		u.nameOnline
FROM		Friends AS f
INNER JOIN	Users AS u ON u.userID = f.friendID
WHERE		f.userID = @UserID

-- Present the resultset
SELECT		f.nameOnline + ' added a new photo ' + CONVERT(CHAR(19), ep.maxDate, 120) AS msg,
		ep.MaxDate AS theDate,
		1 AS theType
FROM		@Friends AS f
INNER JOIN	(
			SELECT		userID,
					MAX(photoDate) AS maxDate
			FROM		ExtraPhotos
			WHERE		photoDate >= @lastLogin
			GROUP BY	userID
		) AS ep	ON ep.userID = f.friendID

UNION ALL

SELECT		f.nameOnline + ' added a new friend ' + CONVERT(CHAR(19), u.maxDate, 120) AS msg,
		u.MaxDate AS theDate,
		2 AS theType
FROM		@Friends AS f
INNER JOIN	(
			SELECT		userID,
					MAX(dateAdded) AS maxDate
			FROM		Friends
			WHERE		dateAdded >= @lastLogin
			GROUP BY	userID
		) AS u ON u.userID = f.friendID

ORDER BY	theType,
		theDate DESC

quote:

---

Originally posted by mike123

Hi Peso,

Currently just getting this error.

Msg 8120, Level 16, State 1, Procedure uspGetUserActivityList, Line 27
Column 'Friends.UserID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


any input very help
thanks again,
mike123

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by mike123

Hi Peso,

Currently just getting this error.

Msg 8120, Level 16, State 1, Procedure uspGetUserActivityList, Line 27
Column 'Friends.UserID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


any input very help
thanks again,
mike123

---

Sorry i'm not getting that error in Peso's posted query. ARe you sure you're using same query?

---

quote:

---

Originally posted by Peso

Maybe uspGetActivityListOfFriends is a better name for the stored procedure than uspGetUserActivityList?

---

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

---

quote:

---

Originally posted by Peso

I think you should query the onlinename too, right?

You can do your other idea to, have a log table and a trigger on every of the 20 tables or so inserting into log table with the log text you wish. it will somewhat slow down each and one update/insert, but the presentation will be fast.

---

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

---