Help with queries - 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 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

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 )

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

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?

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

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. ^_^

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources