Author |
Topic |
IK1972
56 Posts |
Posted - 2013-02-08 : 11:42:45
|
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 isRID 123458910111213181920Thanks |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-08 : 13:05:20
|
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.LCNORDER BY RID; |
|
|
IK1972
56 Posts |
Posted - 2013-02-08 : 18:29:02
|
Yeah its not giving expected result. Row 18,19,20 is expected in result set.Thanks |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-08 : 18:53:19
|
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. |
|
|
IK1972
56 Posts |
Posted - 2013-02-09 : 00:03:37
|
Yes Correct. Its work now.Thanks for all your help. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-09 : 06:03:43
|
Sure, you are very welcome. |
|
|
|
|
|