SQLTeam.com | Weblogs | Forums

Help - Logic to capture records


#1

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					  |