Howtoget Same Second record & Same Location Record

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

Howtoget Same Second record & Same Location Record

Author

Topic

Najju565
Starting Member

6 Posts

Posted - 2012-08-02 : 02:41:57

Hi.. I need to get only records having Same datetime but can have second difference of 10 Seconds and has same location in SQL Server .. Pls help me ...
DATETIME ADMITID LOCATION
--------------------------------------------------------
7/29/2012 00:01:40 1 1F-E FRONT IN
7/29/2012 00:01:54 2 1F-E FRONT OUT
-----------------------------------------------------------------
7/29/2012 00:01:55 1 CHEN DLF 1 IN
7/29/2012 00:01:58 1 CHEN DLF 1 IN

--------SAME LOCATION AND SAME DATETIME and SECOND DIFFERENCE records only should get Displayed same as shown above and below records
7/29/2012 00:02:18 1 CHEN-DLF IN
7/29/2012 00:02:20 1 CHEN-DLF IN
---------------------------------------------------------------------
7/29/2012 00:03:06 1 BLR-WF-ITSVC-3F-NORTH(ENTRY)
7/29/2012 00:03:10 2 BLR-WF-ITSVC-2F-SOUTH(EXIT)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 09:49:32

[code]
SELECT t.*
FROM Table t
CROSS APPLY (
SELECT COUNT(1) AS Cnt
FROM Table
WHERE ADMITID = t.ADMITID
AND [DATETIME] > = DATEADD(ss,-10,t.[DATETIME])
AND [DATETIME] <= DATEADD(ss,10,t.[DATETIME]
)t1
WHERE Cnt >0
[/code]

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

Najju565
Starting Member

6 Posts

Posted - 2012-08-03 : 07:33:15

Thanks a lot.. It worked..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 09:40:47

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources