100000001 process starts 2020/5/9 raised
Process a completed 2020/5/9 raised 100000001
100000001 process starts 2020/5/9 raised
100000002 process starts 2020/5/9 raised
100000003 process starts 2020/5/9 raised
100000004 process starts 2020/5/9 raised
Process a completed 2020/5/9 raised 100000004
100000004 process starts 2020/5/9 raised
100000005 process starts 2020/5/9 raised
100000005 process starts 2020/5/9 raised
100000006 process starts 2020/5/9 raised
Process a completed 2020/5/9 raised 100000006
Need through SQL query have started construction in the process, procedure of work order, the sample is as follows:
Order work procedure was completed a starting time time
2020/5/9 raised 100000002/
2020/5/9 raised 100000003/
100000006 2020/5/9 2020/5/9 prominence raised
CodePudding user response:
The create table # TAB (id nvarchar (50), the name nvarchar (50), the status nvarchar (50))
Insert into # TAB (id, name, status)
Select '1', 'process a', 'start' union all
Select '1', 'process a', 'completion' union all
Select '1', '2', 'start' union all
Select '2', 'process a', 'start' union all
Select '3', 'process a', 'start' union all
A select '4', 'process' and 'start' union all
A select '4', 'process' and 'completion' union all
Select '4', '2', 'start' union all
Select the '5', 'process a', 'start' union all
Select the '5', '2', 'start' union all
A select '6', 'process' and 'start' union all
A select '6', 'process' and 'completion'
Select * from # TAB a
Where a.n ame='one step and a.s tatus=' start '
And not the exists (
Select id # from the TAB b
Where b.n ame='process 2' and b.s tatus='start' and Anderson, d=b.i d
)
CodePudding user response:
Don't see there is a check completed time requirements, under the new added:
If object_id (' tempdb for.. # # TAB ') is not null drop table TAB
The create table # TAB (id nvarchar (50), the name nvarchar (50), the status nvarchar (50), [time] datetime)
Insert into # TAB (id, name, status, [time])
Select '1', 'process a', 'start', getdate () union all
Select '1', 'process a', 'completed', getdate () + 1 union all
Select '1', '2', 'start', getdate () + 2 union all
Select '2', 'process a', 'start', getdate () + 3 union all
A select '3', 'process' and 'start', getdate () + 4 union all
A select '4', 'process' and 'start', getdate () + 5 union all
A select '4', 'process' and 'completed', getdate () + 6 union all
Select '4', '2', 'start', getdate () + 7 union all
Select the '5', 'process a', 'start', getdate () + 8 union all
Select the '5', '2', 'start', getdate () + 9 union all
A select '6', 'process' and 'start', getdate () + 10 union all
A select '6', 'process' and 'completed', getdate () + 11
Select t. *, TAB. The time from (
Select * from # TAB a
Where a.n ame='one step and a.s tatus=' start '
And not the exists (
Select id # from the TAB b
Where b.n ame='process 2' and b.s tatus='start' and Anderson, d=b.i d
)
) t
Left the join # TAB TAB on t.i d=TAB. Id and t.n ame=TAB. The name and TAB. Status='completion'
CodePudding user response:
DECLARE @ t TABLE (order VARCHAR (10) NOT NULL,
Process NVARCHAR (20) NOT NULL,
State NVARCHAR (10) NOT NULL,
Time a DATETIME NOT NULL);
INSERT the @ t (orders, process, status, time)
VALUES (' 100000001 ', 'process a', 'start', '2020/5/9 raised),
(' 100000001 ', 'process a', 'completed', '2020/5/9 raised),
(' 100000001 ', '2', 'start', '2020/5/9 raised),
(' 100000002 ', 'process a', 'start', '2020/5/9 raised),
(' 100000003 ', 'process a', 'start', '2020/5/9 raised),
(' 100000004 ', 'process a', 'start', '2020/5/9 raised),
(' 100000004 ', 'process a', 'completed', '2020/5/9 raised),
(' 100000004 ', '2', 'start', '2020/5/9 raised),
(' 100000005 ', 'process a', 'start', '2020/5/9 raised),
(' 100000005 ', '2', 'start', '2020/5/9 raised),
(' 100000006 ', 'process a', 'start', '2020/5/9 raised),
(' 100000006 ', 'process a', 'completed', '2020/5/9 raised');
Process, order, SELECT a. a. a. state, a., f. completed time
The FROM @ t a
The OUTER APPLY (SELECT c. time completion time
The FROM @ t c
WHERE a. order=c. order AND a. process=c. process AND c. status='completion') f
WHERE a. status='start' AND a. process=a 'process'
AND NOT the EXISTS (SELECT 1 FROM @ t b WHERE b. order=a. orders AND b. process='process 2' AND b. status='start');