SQLTeam.com | Weblogs | Forums

Help with different scenario

I have this result from my query that I would like to get or perform these different scenario using data. could not figured out break it down in script. Any help is very much appreciated. thank you.

--different scenario
same prdnum and same 2d serialnumber with LCOp or FTA --get latest
diff prdnum and same 2d serialnumber with LCOp and FTA -- get FTA
one prdnum , one 2d serialnumber with LCOp --get LCOp
one prdnum , one 2d serialnumber with FTA --get FTA

please see below ddl and sample data

[code]drop table #s1
create table #S1
(prdnum nvarchar(35), wrkctr nvarchar(35),item nvarchar(35), serialnumber nvarchar(35),
qty int, createddatetime datetime, availphysical int, rownum int)

insert into #S1 (prdnum, wrkctr, item , serialnumber, qty , createddatetime , availphysical, rownum )
select 'PRC006833560','FTA','TPG1264-IT','A00000180',1,'2016-02-13 05:13:32.000',1, 1 union all
select 'PRC006833202','LCOp','TPG1307-IT','A00000215',1,'2016-02-12 16:25:28.000',1,1 union all
select 'PRC006833202','LCOp','TPG1307-IT','A00000215',1,'2016-02-12 16:25:14.000',1 ,2 union all
select 'PRC006802035','FTA','TPG1264-IT','A00001558',1,'2016-02-06 05:26:40.000',1,1 union all
select 'PRC006734658','LCOp','TPG1088','A00001558',1,'2016-01-23 22:51:25.000',1,2 union
select 'PRC006845612','LCOp','TPG1307-IT','A00009577',1,'2016-02-15 21:46:54.000',1,1 union all
select 'PRC006845612','LCOp','TPG1307-IT','A00009577',1,'2016-02-15 21:46:30.000',1,2 union all
select 'PRC006729389','LCOp','TPG1264-IT','A00010811',1,'2016-01-22 15:55:08.000',1,1 union all
select 'PRC006713745','LCOp','TPG1264-IT','A00010811',1,'2016-01-20 03:56:21.000',1,2 union all
select 'PRC006940957','LCOp','TPG1307-IT','A00015669',1,'2016-03-08 19:51:30.000',1,1 union all
select 'PRC006821194','LCOp','TPG1264-IT','A00011604',1,'2016-02-10 11:06:07.000',1,1 union all
select 'PRC006793218','LCOp','TPG1264-IT','A00011604',1,'2016-02-04 14:57:03.000',1,2 union all
select 'PRC006793218','LCOp','TPG1264-IT','A00011604',1,'2016-02-04 14:56:50.000',1,3

prdnum , wrkctr, item , serialnumber , qty, createddatetime , availphysical, rownum
from #S1 order by serialnumber, prdnum

prdnum wrkctr item serialnumber qty createddatetime availphysical rownum

PRC006833560 FTA TPG1264-IT A00000180 1 2016-02-13 05:13:32.000 1 1
PRC006833202 LCOp TPG1307-IT A00000215 1 2016-02-12 16:25:28.000 1 1
PRC006802035 FTA TPG1264-IT A00001558 1 2016-02-06 05:26:40.000 1 1
PRC006845612 LCOp TPG1307-IT A00009577 1 2016-02-15 21:46:54.000 1 1
PRC006729389 LCOp TPG1264-IT A00010811 1 2016-01-22 15:55:08.000 1 1
PRC006821194 LCOp TPG1264-IT A00011604 1 2016-02-10 11:06:07.000 1 1
PRC006940957 LCOp TPG1307-IT A00015669 1 2016-03-08 19:51:30.000 1[/code]

Is rule that prdnum and serialnumber are specific i means
(for example
'a' prdnum have 123 serialnumber
'b' prdnum have 345 serialnumber

by obeserving above data.. i am supposing that each prdnum have a specific serialnumber