SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

IK1972
Starting Member

49 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

3587 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
Starting Member

49 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

3587 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
Starting Member

49 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

3587 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000