Sql Query for my below tbl

Question

Table name ->datatbl Columns ->lineworkordernobundlenostylecodescandatescantime Actual sqlserver TBL like this Line Workorderno Bundleno Style code scandate ScanTime l1 123 1210 AC234 2 03-08-2013 10.11 l1 123 1210 AC234 1 03-08-2013 11.15 l1 123 1210 AC234 4 03-08-2013 13.17 l1 123 1210 AC234 3 03-08-2013 15.32 l1 123 1210 AC234 2 03-08-2013 10.14 l1 123 1210 AC234 1 05-08-2013 11.21 l1 123 1211 AS232 8 03-04-2013 14.52 l1 123 1211 AS232 7 04-04-2013 10.45 code -> odd Numbers are Send Scan code AND Even Numbers Are Recv Scan Code EXPECTED RESULT Line Workorderno Bundleno Style code senddate SendTime RecvDate RecvTime l1 123 1210 AC234 03-08-2013 10.11 03-08-2013 11.15 l1 123 1210 AC234 03-08-2013 13.17 03-08-2013 15.32 l1 123 1210 AC234 03-08-2013 10.14 05-08-2013 11.21 l1 123 1211 AS232 03-04-2013 14.52 04-04-2013 10.45 in code column odd number or even number of particular value column value is enough to show for example if 21 means display 2 is enough Please Help Me Thanks in ADVS

Iftikar Hussain · Answer

what result you are getting?

Iftikar Hussain · Answer

Hi Try like this ;WITH Data AS ( SELECT LineWorkordernoBundlenoStyleCodescandateScanTime ROW_NUMBER() OVER (PARTITION BY (Code % 2) ORDER BY cast(scandate+' '+replace(ScanTime'.'':') as datetime)) as Rn FROM YourTable ) select A.LineA.WorkordernoA.BundlenoA.StyleA.Code A.[scandate] as [SendDate] A.[ScanTime] as [SendTime] B.[scandate] as [RecvDate] B.[ScanTime] as [RecvTime] from Data A INNER JOIN Data B ON A.Line = B.Line and A.Workorderno = B.Workorderno AND A.Rn=B.Rn AND cast(A.scandate+' '+replace(A.ScanTime'.'':') as datetime) ORDER BY A.ScanTime Regards Iftikar

Parthiban B · Answer

I attached what i need Please Find the Atachment

Parthiban B · Answer

Select a.linea.workordernoa.bundlenoa.style a.scandate as sendDate a.scantime as sendTimeB.scandate recDateB.scantime recTime from (select *ROW_NUMBER() OVER (ORDER BY code) as Rn from #Temp where code %2 =0) as A join (select *ROW_NUMBER() OVER (ORDER BY code) as Rn from #Temp where code %2 =1) B on A.bundleno = b.bundleno and a.Rn = b.Rn and a.style = b.style above query i didn't get full result

Iftikar Hussain · Answer

have you tried like this ;WITH Data AS ( SELECT LineWorkordernoBundlenoStyleCodescandateScanTime ROW_NUMBER() OVER (PARTITION BY (Code % 2) ORDER BY cast(scandate+' '+replace(ScanTime'.'':') as datetime)) as Rn FROM YourTable ) select A.LineA.WorkordernoA.BundlenoA.StyleA.Code A.[scandate] as [SendDate] A.[ScanTime] as [SendTime] B.[scandate] as [RecvDate] B.[ScanTime] as [RecvTime] from Data A INNER JOIN Data B ON A.Line = B.Line and A.Workorderno = B.Workorderno AND A.Rn=B.Rn AND cast(A.scandate+' '+replace(A.ScanTime'.'':') as datetime) ORDER BY A.ScanTime

Parthiban B · Answer

no my frnd there is no unique value in tbl that y iam asking some help

Jignesh Trivedi · Answer

hi Try... create table #Temp ( line varchar(3) workorderno int bundleno int style varchar(20) code int scandate datetime scantime time ) Insert into #Temp values ('l1'123 1210 'AC234' 2 '03-08-2013' '10:11') ('l1'123 1210 'AC234' 1 '03-08-2013' '11:15') ('l1'123 1210 'AC234' 4  '03-08-2013' '13:17') ('l1'123 1210 'AC234' 3  '03-08-2013' '15:32') ('l1'123 1210 'AC234' 2  '03-08-2013' '10:14') ('l1'123 1210 'AC234' 1  '05-08-2013' '11:21') ('l1'123 1211 'AS232' 8  '03-04-2013' '14:52') ('l1'123 1211 'AS232' 7  '04-04-2013' '10:45') Select a.linea.workordernoa.bundlenoa.style a.scandate as sendDate a.scantime as sendTimeB.scandate recDateB.scantime recTime from (select *ROW_NUMBER() OVER (ORDER BY code) as Rn from #Temp where code %2 =0) as A join (select *ROW_NUMBER() OVER (ORDER BY code) as Rn from #Temp where code %2 =1) B on A.bundleno = b.bundleno and a.Rn = b.Rn and a.style = b.style hope this will help you.

Jignesh Trivedi · Answer

hi I think It is very difficult to write query because there are so many duplicate value in your table. in your datavalue there is no unique value on that we can partition. can please provide more information?

Line	Workorderno	Bundleno	Style	code	scandate	ScanTime
l1	123	1210	AC234	2	03-08-2013	10.11
l1	123	1210	AC234	1	03-08-2013	11.15
l1	123	1210	AC234	4	03-08-2013	13.17
l1	123	1210	AC234	3	03-08-2013	15.32
l1	123	1210	AC234	2	03-08-2013	10.14
l1	123	1210	AC234	1	05-08-2013	11.21
l1	123	1211	AS232	8	03-04-2013	14.52
l1	123	1211	AS232	7	04-04-2013	10.45