Please if someone can help me to get my desired result using the sample below. I have below my initial script. reject and good location is the last transaction perform meaning this is already ended but if the location is not reject or good meaning this is still on process (i don't know what particular location.) how can i capture or display the records. for example the location is Pc_working (sample).
another one is who to capture the Pl_Entry and Pl_Reject. do i need to create a separate cte for this location.
create table #register (itemcode varchar(20),ponum varchar(10), id2 varchar(5), item varchar(3), wrkctr varchar(10), serialid varchar(9),em_serial varchar(10),date datetime)
insert into #register values
('A5912947270138','P008279816','S7843','A13','FEntry','12345678F','aaaaaaaaaa','2016-10-07 06:20:54.000'),
('A5912947270138','P008508629','S8479','A13','FEntry','12345678F','bbbbbbbbbb','2016-11-14 06:32:58.000'),
('A5936181820133','P008281945','S7809','A24','FEntry','56789045D','ffffffffff','2016-10-07 14:48:13.000'),
('A5936181820133','P008510773','S8482','A24','FEntry','56789045D','gggggggggg','2016-11-14 09:36:37.000'),
('A5936181820133','P008510776','S8483','A24','FEntry','56789045D','kkkkkkkkkk','2016-12-29 09:36:37.000'),
('A5936181820133','P008510775','S8484','A24','FEntry','56789045D','uuuggggggg','2017-1-6 09:36:37.000')
create table #details (line varchar(3),pool varchar(2), ponum varchar(20),itemid varchar(10),location varchar(10),date datetime,serialid varchar(10))
insert into #details values
('L01','PC','P06965620','pc13-t','Pc_Entry','2016-10-08 08:34:53.000','12345678F'),
('L01','PC','P06965620','pc13-t','Pc_Reject','2016-10-08 08:44:45.000','12345678F'),
('L02','PL','P06965620','pc13-t','Pl_Entry','2016-10-15 10:44:03.000','12345678F'),
('L02','PL','P06965620','pc13-t','Pl_Reject','2016-10-18 09:10:15.000','12345678F'),
('L01','PC','P07628121','pc24-t','Pc_Entry','2016-11-19 17:03:27.000','56789045D'),
('L01','PC','P07628121','pc24-T','Pc_Reject','2016-11-19 17:37:18.000','56789045D'),
('L01','PC','P07891150','pc24-t','Pc_Entry','2016-12-06 17:07:40.000','56789045D'),
('L01','PC','P07891150','pc24-t','Pc_Good','2016-12-20 17:17:18.000','56789045D'),
('L01','PC','P07891155','pc24-t','Pc_Entry','2017-1-1 17:07:40.000','56789045D'),
('L01','PC','P07891155','pc24-t','Pc_Working','2017-1-2 17:07:40.000','56789045D'),
('L01','PC','P07891156','pc24-t','Pc_Entry','2017-1-10 17:07:40.000','56789045D')
select * from #register
select * from #details
;with preRegister as
(
select wrkctr, serialid, em_serial, date,
Dense_RANK() over (partition by r.serialid order by r.date) rn
from #register r
)
,preDetails as
(
select *,
Dense_RANK() over (partition by d.serialid order by d.ponum) rn
from #details d
)
,cteL as
(
select d.ponum,d.location,d.date, d.itemid, d.serialid, d.rn
from preDetails d
where d.location = 'Pc_Entry'
)
,cte as
(
select c.date as date2, c.location as location1, c.serialid, c.rn, d.date as date3, d.location as location2, d.itemid
from cteL c
left join preDetails d
on c.ponum = d.ponum and d.location in ('Pc_Reject','Pc_Good') --- last location meaning the process was already finished
)
select
wrkctr, r.serialid, em_serial, date, date2, location1, date3, location2, itemid
from preRegister r
left join cte c
on r.serialid = c.serialid and r.rn = c.rn
drop table #details,#register
wrkctr|serialid |em_serial |date |date2 |location1|date3 |location2|itemid|date4 |location3|date5 |location4
------|---------|----------|-----------------------|-----------------------|---------|-----------------------|---------|------|-----------------------|---------------------------------|----------
FEntry|12345678F|aaaaaaaaaa|2016-10-07 06:20:54.000|2016-10-08 08:34:53.000|Pc_Entry |2016-10-08 08:44:45.000|Pc_Reject|pc13-t|2016-10-15 10:44:03.000|Pl_entry |2016-10-18 09:10:15.000|Pl_Reject
FEntry|12345678F|bbbbbbbbbb|2016-11-14 06:32:58.000|NULL |NULL |NULL |NULL |NULL |NULL |
FEntry|56789045D|ffffffffff|2016-10-07 14:48:13.000|2016-11-19 17:03:27.000|Pc_Entry |2016-11-19 17:37:18.000|Pc_Reject|pc24-T|NULL |
FEntry|56789045D|gggggggggg|2016-11-14 09:36:37.000|2016-12-06 17:07:40.000|Pc_Entry |2016-12-20 17:17:18.000|Pc_Good |pc24-t|NULL |
FEntry|56789045D|kkkkkkkkkk|2016-12-29 09:36:37.000|2017-01-01 17:07:40.000|Pc_Entry |NULL |NULL |NULL |NULL |
FEntry|56789045D|uuuggggggg|2017-01-06 09:36:37.000|2017-01-10 17:07:40.000|Pc_Entry |NULL |NULL |NULL |NULL |