Simple SQL Query Help

[rkrause]

i have a simple query i have attached below. one of my fields is called LOTNUMBER which looks like this for example 240-32(240 is the julian date, and 32 is the bag number.

what my issue is when sort them in asc order they end up looking like this:
240-1
240-10
240-11
240-12 ect
and i want it to look like this
240-1
240-2
240-3
240-4 ect

Any ideas?




SELECT *
FROM [DSIPAR].[dbo].[bk_v_nif_OnHandInventory_BaseWithTests]
where Receiptdate = @ProductionDate and item = '07603'
ORDER BY lotnumber asc

 

[MarkK]

This is one of the costs of storing two data points in one field. There is no way to reliably sort on the second point. What I would do is store these data in separate fields. But this might work ....

ORDER BY CLng(Split(LotNumber, "-")(1))

... but another cost of combining these data is that you don't know that every field contains a "-" or that the bag number is numeric.
Best of luck,

 

[rkrause]

I know every field has a "-" in it, i also know the whole field isnt numeric.

 

[rkrause]

SELECT *
FROM [DSIPAR].[dbo].[bk_v_nif_OnHandInventory_BaseWithTests]
where Receiptdate = '8-28-2010' and item = '07603'
order by CLng(Split(LotNumber, "-")(1))

When i run that, i get an error saying:

Msg 195, Level 15, State 10, Line 7
'Split' is not a recognized built-in function name.

 

[boblarson]

Create two different fields in your query (you don't need to show them) -

One for the first part and the second for the second part. Then set the sort on each, the first one first and the second part second.
(Air Code - untested)

FirstPart:Left([FieldNameHere], Len(Instr(1, [FieldNameHere],"-")-1)
 
SecondPart:Mid([FieldNameHere,Instr(1,[FieldNameHere], "-") + 1)