Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Query Help
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IK1972
Yak Posting Veteran

56 Posts

Posted - 02/08/2013 :  11:42:45  Show Profile  Reply with Quote

I'm trying to prepare one query and need help.

Let me explain.

create table #temp(RID bigint, LCN varchar(50), LCD datetime, BID bigint, BName varchar(100), BCreatedDate datetime)


insert into #temp values
(1,259220593,'10 Jun 2012 23:00:29:640',2514050,'Test A','23 Jan 2013 21:33:53:713'),
(2,259220593,'10 Jun 2012 23:00:29:640',1237038,'Test A','10 Jun 2012 23:00:35:607'),
(3,259221119,'10 Jun 2012 23:00:29:640',1612788,'Test B','09 Aug 2012 21:30:10:163'),
(4,259221119,'10 Jun 2012 23:00:29:640',1349020,'Test B','11 Jul 2012 00:38:45:517'),
(5,259221119,'10 Jun 2012 23:00:29:640',1237066,'Test B','10 Jun 2012 23:00:36:703'),
(6,259221171,'10 Jun 2012 23:00:29:640',1237068,'Test C','10 Jun 2012 23:00:36:703'),
(7,259220783,'10 Jun 2012 23:00:29:657',1237033,'Test D','10 Jun 2012 23:00:34:983'),
(8,259221302,'10 Jun 2012 23:00:29:700',1347641,'Test E','11 Jul 2012 00:37:30:837'),
(9,259221302,'10 Jun 2012 23:00:29:700',1237083,'Test E','10 Jun 2012 23:00:37:420'),
(10,259220827,'10 Jun 2012 23:00:29:717',1311402,'Test XYZ','09 Jul 2012 21:24:01:123'),
(11,259220827,'10 Jun 2012 23:00:29:717',1237067,'Test XYZ','10 Jun 2012 23:00:36:703'),
(12,259221212,'10 Jun 2012 23:00:29:797',1456613,'Test WER','16 Jul 2012 21:12:54:263'),
(13,259221212,'10 Jun 2012 23:00:29:797',1237064,'Test WER','10 Jun 2012 23:00:36:687'),
(14,259221133,'10 Jun 2012 23:00:29:843',1237076,'Test FGH','10 Jun 2012 23:00:37:123'),
(15,259221206,'10 Jun 2012 23:00:29:843',1237042,'Test KLJ','10 Jun 2012 23:00:36:047'),
(16,259221111,'10 Jun 2012 23:00:29:843',1237078,'Test ert','10 Jun 2012 23:00:39:123'),
(17,259221111,'10 Jun 2012 23:00:29:843',1237049,'Test ert','10 Jun 2012 23:00:36:047'),
(18,259221112,'10 Jun 2012 23:00:29:843',1237278,'Test dfg','10 Jun 2012 23:00:39:123'),
(19,259221112,'10 Jun 2012 23:00:29:843',1237349,'Test hjy','10 Jun 2012 22:00:38:047'),
(20,259221112,'10 Jun 2012 23:00:29:843',1237449,'Test hjy','10 Jun 2012 22:00:36:047')

Now in my result set I want to exclude these

1 -- If there is only one LCN Number then I don't need in result.
2 -- If we have 2 or multiple LCN and its BCreatedDate difference in few minutes suppose 5 minutes then also not need it in result.


So expected Result set is

RID
1
2
3
4
5
8
9
10
11
12
13
18
19
20

Thanks


James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/08/2013 :  13:05:20  Show Profile  Reply with Quote
Can you try this? This does not give the rows 18,19, and 20 that you have in your sample result. So I may have misunderstood the requirement:
;WITH cte AS
(
	SELECT
		LCN
	FROM
		#temp t1
		OUTER APPLY
		(
			SELECT ABS(DATEDIFF(mi,t1.BCreatedDate, t2.BCreatedDate)) AS TimeDiff
			FROM #temp t2
			WHERE t2.LCN = t1.LCN
			AND t1.BCreatedDate <> t2.BCreatedDate
		) t2
	GROUP BY 
		LCN
	HAVING
		COUNT(*) > 1
		AND MIN(TimeDiff) > 5
)
SELECT RID FROM #temp t1 
INNER JOIN cte c1 ON c1.LCN = t1.LCN
ORDER BY RID;
Go to Top of Page

IK1972
Yak Posting Veteran

56 Posts

Posted - 02/08/2013 :  18:29:02  Show Profile  Reply with Quote

Yeah its not giving expected result. Row 18,19,20 is expected in result set.

Thanks
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/08/2013 :  18:53:19  Show Profile  Reply with Quote
Try changing the Min(TimeDiff) > 5 to MAX(TimeDiff) > 5. I assumed that if there was at least one pair of LCN's that was less than 5 minutes apart you want to exclude all those LCNs. I think you want to exclude only if all the LCN's are less than 5 minutes apart. Hence the change from MIN to MAX.
Go to Top of Page

IK1972
Yak Posting Veteran

56 Posts

Posted - 02/09/2013 :  00:03:37  Show Profile  Reply with Quote

Yes Correct. Its work now.

Thanks for all your help.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/09/2013 :  06:03:43  Show Profile  Reply with Quote
Sure, you are very welcome.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000