Need help in Sql Query

Please find attached excel for sql server table data. I wrote the below query

select distinct WP.PickListNo,WP.OrderNo,WP.ArtNo,WP.Destination,WP.Size,WP.Source,WP.Concept,wp.Status,WP.Category,WP.slotcode,

WP.ActualQty,isnull(WP.ToPicked,0) ToPicked,isnull(WP.PickedQty,0) PickedQty,isnull(WP.LoadQty,0) LoadQty,Picker ,PickListCreatedOn,isnull(convert(varchar,PickingOn,120),'Not Picked Yet') PickingOn,

isnull(convert(varchar,LoadedOn,120),'Not Loaded Yet') LoadedOn

from wms_picking WP where CONVERT(datetime,CONVERT(varchar,PickListCreatedOn,101)) between

CONVERT(datetime,CONVERT(varchar,'2013-05-22',101)) and CONVERT(datetime,CONVERT(varchar,'2013-05-22',101))

I need result distinct picklistno with there sizes 070/060 in single single row article wise.

NEED HELP.........

2 Replies

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

Rajesh Kumar Jha13y ago
Hi Ravi, Please have a look at the following query. WITH temp AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY PickListNo ORDER BY PickListNo) AS rn from wms_picking ) select * from temp where rn=1 and Size In ('070','060')
Hanook13y ago
Can you post the expected result format and smal set of sample data....
I doesn't have permissions to access sample.rar file.....