| Author |
 Topic  |
|
|
IK1972
Starting Member
41 Posts |
 Posted - 02/08/2013 : 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 is
RID
1
2
3
4
5
8
9
10
11
12
13
18
19
20
Thanks
|
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 02/08/2013 : 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.LCN
ORDER BY RID; |
 |
|
|
IK1972
Starting Member
41 Posts |
Posted - 02/08/2013 : 18:29:02
|
Yeah its not giving expected result. Row 18,19,20 is expected in result set.
Thanks |
|
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 02/08/2013 : 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
Starting Member
41 Posts |
Posted - 02/09/2013 : 00:03:37
|
Yes Correct. Its work now.
Thanks for all your help.
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 02/09/2013 : 06:03:43
|
| Sure, you are very welcome. |
|
|
| |
Topic |
|
|
|
SQL Query Help
