I want sql Query for below table

Parthiban B

13y1.7k

Table name ->datatbl

Columns ->line,workorderno,bundleno,style,code,scandate,scantime

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 2,1 means display 2 is enough

Please Help Me
Thanks in ADVS

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi13y ago
hi,

I think this post is same as http://www.c-sharpcorner.com/Forums/Thread/225932/
so please mark it as close or duplicate.
0
Iftikar Hussain13y ago
Have you tried the below query or not

;WITH Data AS
(
SELECT Line,Workorderno,Bundleno,Style,Code,scandate,ScanTime,
ROW_NUMBER() OVER (PARTITION BY (Code % 2) ORDER BY cast(scandate+' '+replace(ScanTime,'.',':') as datetime)) as Rn
FROM YourTable
)

select A.Line,A.Workorderno,A.Bundleno,A.Style,A.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
0
Parthiban B13y ago
i need answer for my above question please help me
0
Iftikar Hussain13y ago
Hi,
Try like this

;WITH Data AS
(
SELECT Line,Workorderno,Bundleno,Style,Code,scandate,ScanTime,
ROW_NUMBER() OVER (PARTITION BY (Code % 2) ORDER BY cast(scandate+' '+replace(ScanTime,'.',':') as datetime)) as Rn
FROM YourTable
)

select A.Line,A.Workorderno,A.Bundleno,A.Style,A.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
0
Parthiban B13y ago
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
0
Anurag Sarkar13y ago
You need to combine the data on the basis of code am i right ?
0
Anurag Sarkar13y ago
What is the use of code there ?
0
Anurag Sarkar13y ago
You have not mentioned send date & recv date in table why ?
0
Parthiban B13y ago
Thx for Spent your time
I want filter this from senddate to recvdate(date between send and recv)
0
Anurag Sarkar13y ago
On what basis do you want to filter the records please explain it more clearly?
0