need help on a query - 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

General SQL Server Forums

New to SQL Server Programming

need help on a query

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-01-25 : 21:44:51

SELECT TOP 100

[UserNameID] as ViewerUserNameID, ----- table tbl_ProfileViews

[ViewUserNameID] as ViewedUserNameID, ----- table tbl_ProfileViews

[TimeStamp] as ViewedDate, ----- table tbl_ProfileViews

[PhotoID], ----- table tbl_Photos

[ProfileID], ----- table tbl_Photos

[p_Primary] ----- table tbl_Photos

ViewerUserNameID ViewedUserNameID ViewedDate PhotoID ProfileID p_Primary

31 44 2011-01-24 17:22:50.023 40 31 0
31 44 2011-01-24 17:22:50.023 41 31 0
31 44 2011-01-24 17:22:50.023 42 31 0
31 44 2011-01-24 17:22:50.023 43 31 0
31 44 2011-01-24 17:22:50.023 44 31 0
31 44 2011-01-24 17:22:50.023 47 31 1
45 44 2011-01-20 11:36:12.940 50 45 1
47 44 2011-01-20 11:34:24.320 52 47 1

I need to get the latest date and the query should return
31 44 2011-01-24 17:22:50.023 47 31 1
45 44 2011-01-20 11:36:12.940 50 45 1
47 44 2011-01-20 11:34:24.320 52 47 1

I need the latest time and one instance of ViewerUserNameID
============================================

EXEC @return_value = [dbo].[prc_ProfileViews_SelectByUserName]

@ViewedUsernameID = 44,

@FromDate = N'12/15/201

===============================================
ALTER PROCEDURE [dbo].[prc_ProfileViews_SelectByUserName]
(
@ViewedUsernameID int,
@FromDate DATETIME = NULL
)
AS
SELECT TOP 100
[UserNameID] as ViewerUserNameID, ----- table tbl_ProfileViews
[ViewUserNameID] as ViewedUserNameID, ----- table tbl_ProfileViews
[TimeStamp] as ViewedDate, ----- table tbl_ProfileViews
[PhotoID], ----- table tbl_Photos
[ProfileID], ----- table tbl_Photos
[p_Primary] ----- table tbl_Photos
FROM tbl_ProfileViews INNER JOIN tbl_Photos ON
tbl_Photos.ProfileID = tbl_profileViews.UserNameID
WHERE tbl_ProfileViews.ViewUserNameID = @ViewedUsernameID AND
(@FromDate IS NULL OR (([TimeStamp] >= @FromDate)AND [TimeStamp] = (SELECT MAX([TimeStamp])
FROM tbl_ProfileViews A where A.UserNameID = tbl_profileViews.UserNameID)))
ORDER BY [TimeStamp] DESC

Any help would be awesome.

X002548
Not Just a Number

15586 Posts

Posted - 2011-01-26 : 00:32:00

well what RE YOU GETTING OR WHAT ERROR ARE YOU GETTING

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 11:38:37

how many posts for same problem?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155731
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155733

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources