I want sql Query for below table

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

Anurag Sarkar · Answer

On what basis do you want to filter the records please explain it more clearly?

Jignesh Trivedi · Answer

hi I think this post is same as http://www.c-sharpcorner.com/Forums/Thread/225932/ so please mark it as close or duplicate.

Iftikar Hussain · Answer

Have you tried the below query or not ;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 need answer for my above question please help me

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

Send date and Rev date in scan date column if the code is even number that is send date if the code is odd number that is Rev data

Anurag Sarkar · Answer

You need to combine the data on the basis of code am i right ?

Anurag Sarkar · Answer

What is the use of code there ?

Anurag Sarkar · Answer

You have not mentioned send date & recv date in table why ?

Parthiban B · Answer

Thx for Spent your time I want filter this from senddate to recvdate(date between send and recv)

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