; with tblASmax as
(
	select	serialnumber, runno,process,side, result
	from	(
			select	serialnumber,runno,process,result,side, row_no = row_number() over (partition by process,side,serialnumber order by runno desc)
			from	tblAS

			union all

			select	serialnumber,runlocal,process,result,NULL, row_no = row_number() over (partition by process,serialnumber order by runlocal desc)
			from	tblFA
		) m  	where	m.row_no	= 1 and process = '12'
)
select k.productiono,k.qty,serialnumber,result 
from(
select po.productiono,qty,partcode,serialnumber,result

from	tblPO po
	left join tblASmax rs	on	po.firstsr	<= rs.serialnumber	and	po.lastsr	>= rs.serialnumber 
group by po.productiono,qty,partcode,serialnumber,result)k     where k.productiono='5009073'

; with cte as
(
	select	productiono, serial = firstsr, lastsr
	from	tblpo where productiono = '5009073'

	union all

	select	productiono, serial = serial + 1, lastsr
	from	cte
	where	serial + 1 <= lastsr
)
select	productiono, serial
from	cte 
order by productiono, serial
option (maxrecursion 1000);

; with tblASmax as
(
	select	serialnumber, runno,process,side, result
	from	(
			select	serialnumber,runno,process,result,side, row_no = row_number() over (partition by process,side,serialnumber order by runno desc)
			from	tblAS

			union all

			select	serialnumber,runlocal,process,result,NULL, row_no = row_number() over (partition by process,serialnumber order by runlocal desc)
			from	tblFA
		) m  	where	m.row_no	= 1 and process = '12'
),
k AS 
(
	select po.productiono,qty,partcode,serialnumber,result

from	tblPO po
	left join tblASmax rs	on	po.firstsr	<= rs.serialnumber	and	po.lastsr	>= rs.serialnumber 
group by po.productiono,qty,partcode,serialnumber,result
),
cte AS
(
	select	productiono, serial = firstsr, lastsr
	from	tblpo where productiono = '5009073'

	union all

	select	productiono, serial = serial + 1, lastsr
	from	cte
	where	serial + 1 <= lastsr
)
select k.productiono,k.qty,serialnumber,result ,
cte.productiono, cte.serial
FROM k INNER /* or left,right or full*/ JOIN cte ON cte.productionno = k.productionno;

quote:

---

Originally posted by sunitabeck

Can be done something like this. What I posted parses, but obviously I couldn't test because I don't have the tables referenced.

; with tblASmax as
(
	select	serialnumber, runno,process,side, result
	from	(
			select	serialnumber,runno,process,result,side, row_no = row_number() over (partition by process,side,serialnumber order by runno desc)
			from	tblAS

			union all

			select	serialnumber,runlocal,process,result,NULL, row_no = row_number() over (partition by process,serialnumber order by runlocal desc)
			from	tblFA
		) m  	where	m.row_no	= 1 and process = '12'
),
k AS 
(
	select po.productiono,qty,partcode,serialnumber,result

from	tblPO po
	left join tblASmax rs	on	po.firstsr	<= rs.serialnumber	and	po.lastsr	>= rs.serialnumber 
group by po.productiono,qty,partcode,serialnumber,result
),
cte AS
(
	select	productiono, serial = firstsr, lastsr
	from	tblpo where productiono = '5009073'

	union all

	select	productiono, serial = serial + 1, lastsr
	from	cte
	where	serial + 1 <= lastsr
)
select k.productiono,k.qty,serialnumber,result ,
cte.productiono, cte.serial
FROM k INNER /* or left,right or full*/ JOIN cte ON cte.productionno = k.productionno;

---