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 queries

Author  Topic 

xxhajime08xx
Starting Member

4 Posts

Posted - 2013-11-06 : 20:36:53
Hi I have a table (with fields ReferenceNo,LastName,FirstName,MiddleInit,DatePosted) what i would like to do is to get the latest ReferenceNo and DatePosted where the LastName,FirstName, and MiddleInit is already existing.

Can anyone help me formulate a query for this?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-06 : 23:34:30
SELECT ReferenceNo,LastName,FirstName,MiddleInit,DatePosted
FROM (SELECT ReferenceNo,LastName,FirstName,MiddleInit,DatePosted, ROW_NUMBER() OVER(PARTITION BY LastName,FirstName,MiddleInit ORDER BY DatePosted DESC) RN FROM TabbleName) temp
WHERE temp.RN=1


--
Chandu
Go to Top of Page

xxhajime08xx
Starting Member

4 Posts

Posted - 2013-11-07 : 01:47:03
Thanks Chandu and sorry if I did'nt explain further however i would only like to get those records with more than one record. below is a more detailed explanation

From a 30 minute duration (say 11-06-2013 08:00:00.000 to 11-06-2013 08:30:00.999) I would like to get those ReferenceNo and DatePosted where the LastName,FirstName, and MiddleInit is already existing (records below 11-06-2013 07:59:59.999 )

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 07:36:41
[code]
DECLARE @StartDate datetime
SET @StartDate='2013-11-06 08:00' -- pass any value you want here

SELECT ReferenceNo,LastName,FirstName,MiddleInit,DatePosted
FROM TabbleName
WHERE DatePosted> = @StartDate
AND DatePosted < DATEADD(minute,31,@StartDate)
AND EXISTS (SELECT 1
FROM TableName
WHERE LastName = t.LastName
AND FirstName = t.FirstName
and MiddleInit = t.MiddleInit
AND DatePosted < @StartDate
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

xxhajime08xx
Starting Member

4 Posts

Posted - 2013-12-09 : 03:01:57
Hi is it also possible to show both ReferenceNo including LastName,FirstName,MiddleInit,DatePosted where LastName,FirstName,MiddleInit has duplicate?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 06:54:25
do you mean this?


DECLARE @StartDate datetime
SET @StartDate='2013-11-06 08:00' -- pass any value you want here

SELECT t1.ReferenceNo,t1.LastName,t1.FirstName,t1.MiddleInit,t1.DatePosted,
t2.ReferenceNo AS prevReferenceNo, t2.DatePosted AS prevDatePosted
FROM TabbleName t1
INNER JOIN TabbleName t2
ON t1.LastName = t2.LastName
AND t1.FirstName = t2.FirstName
and t1.MiddleInit = t2.MiddleInit
AND t2.DatePosted < @StartDate
WHERE t1.DatePosted > = @StartDate
AND t1.DatePosted < DATEADD(minute,31,@StartDate)


Beware that in this case if there are multiple prev reference records you'll get multiple records in output for the same LastName,FirstName, and MiddleInit combination.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

xxhajime08xx
Starting Member

4 Posts

Posted - 2013-12-09 : 21:13:16
Thanks a lot visakh16 for the help just the query i need to get started. ^_^
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 22:33:01
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -