Identiying Fault cases from Sql Query

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 2012 Forums

Transact-SQL (2012)

Identiying Fault cases from Sql Query

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2015-02-11 : 22:50:50

Hi ,

i have data in below format.using below data to extract the fault cases.
operator machine fromdate todate
1491 QC03 2014-09-02 02:51:00.000 2014-09-02 06:10:00.000
1427 QC03 2014-09-02 06:11:00.000 2014-09-02 07:17:00.000
1491 QC03 2014-09-02 11:21:00.000 2014-09-02 14:50:00.000
1595 QC03 2014-09-02 03:10:00.000 2014-09-02 08:25:00.000

we need to fetch the falut cases from the above mentioned data.
emp is working on different time on specified machines. In some cases multilple employuees working on
specifed Machines on Same time.

In above Example case 1491,1595 operators working time is Overlapping.

we need to check emp working same time on Same machine(Fault cases)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2015-02-12 : 15:28:09

The query below will show the record, followed by 4 colums of the record that it is overlapping with.

The information you submitted though has more records with overlapping time than you indicated, please clarify if I understood incorrectly.


declare @tmp table (operator int,machine char(4),fromdate datetime,todate datetime)
insert into @tmp
values(1491,'QC03','2014-09-02 02:51:00.000','2014-09-02 06:10:00.000'),
(1427,	'QC03',	'2014-09-02 06:11:00.000','2014-09-02 07:17:00.000'),
(1491,	'QC03',	'2014-09-02 11:21:00.000','2014-09-02 14:50:00.000'),
(1595, 'QC03', '2014-09-02 03:10:00.000', '2014-09-02 08:25:00.000')


select * from @tmp a
cross apply
(select * from @tmp aa where not aa.operator = a.operator  and a.machine = aa.machine  and

   (aa.fromdate between a.fromdate  and a.todate
 or aa.todate between a.fromdate  and a.todate
 or a.fromdate between aa.fromdate and aa.todate 
 or a.todate between aa.fromdate  and aa.todate )) b

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

kond.mohan
Posting Yak Master

213 Posts

Posted - 2015-02-16 : 00:57:15

thanks for replying .
but i could not reach the Required output in the above case.
i have added one more column for reach the exact output.

below cases 17,18,22 correct cases. remains Are overlapping cases

QC05 1697 2014-08-01 00:04:00.000 2014-08-01 01:06:00.000 17
QC05 2057 2014-08-01 01:07:00.000 2014-08-01 02:47:00.000 18
QC05 1344 2014-08-01 01:28:00.000 2014-08-01 02:50:00.000 19
QC05 1606 2014-08-01 01:34:00.000 2014-08-01 02:49:00.000 20
QC05 1514 2014-08-01 01:43:00.000 2014-08-01 02:45:00.000 21
QC05 1276 2014-08-01 02:51:00.000 2014-08-01 06:01:00.000 22
QC05 1499 2014-08-01 02:51:00.000 2014-08-01 05:56:00.000 23
QC05 1199 2014-08-01 02:52:00.000 2014-08-01 05:45:00.000 24

pls guide on the same

waterduck
Aged Yak Warrior

982 Posts

Posted - 2015-02-16 : 03:04:05

looks ugly...


declare @table table(machine varchar(4), operator int, fromdate datetime, todate datetime, checkrow int)
insert into @table select
'QC05',	1697	,'2014-08-01 00:04:00.000','2014-08-01 01:06:00.000',17 union all select
'QC05',	2057	,'2014-08-01 01:07:00.000','2014-08-01 02:47:00.000',18 union all select
'QC05',	1276	,'2014-08-01 02:51:00.000','2014-08-01 06:01:00.000',22 union all select
'QC05',	1344	,'2014-08-01 01:28:00.000','2014-08-01 02:50:00.000',19 union all select
'QC05',	1606	,'2014-08-01 01:34:00.000','2014-08-01 02:49:00.000',20 union all select
'QC05',	1514	,'2014-08-01 01:43:00.000','2014-08-01 02:45:00.000',21 union all select
'QC05',	1499	,'2014-08-01 02:51:00.000','2014-08-01 05:56:00.000',23 union all select
'QC05',	1199	,'2014-08-01 02:52:00.000','2014-08-01 05:45:00.000',24                 

;with cte as(
	select *
	, cast(1 as bigint) as rn
	from @table a
	where fromdate = (select min(fromdate)
					from @table b
					where a.machine = b.machine)
	union all
	select a.*
	, ROW_NUMBER() over (order by a.fromdate)
	from @table a
	join cte b
	on a.operator <> b.operator
	and a.machine = b.machine
	and a.fromdate >= b.todate
)
select distinct * 
from cte
where rn = 1
order by fromdate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources