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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Query Help

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 is

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

Thanks


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.LCN
ORDER BY RID;
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

IK1972

56 Posts

Posted - 2013-02-09 : 00:03:37

Yes Correct. Its work now.

Thanks for all your help.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-09 : 06:03:43
Sure, you are very welcome.
Go to Top of Page
   

- Advertisement -