February 21, 2011 at 4:26 am
Hi,
I have a join consisting of two tables (VCTMovements and VCTHaulageMileageLog).
SELECT * FROM VCTMovements vm JOIN VCTHaulageMileageLog vhml ON
vm.VCTCollectZipcodeId = vhml.ZipcodeFrom
AND vm.VCTDeliveryZipcodeId = vhml.ZipcodeTo
AND vm.DataAreaId = vhml.DataAreaId
WHERE vm.VCTCollectZipcodeId IS NOT NULL
AND vm.VCTDeliveryZipcodeId IS NOT NULL
AND vm.VCTCollectZipcodeId <> ''
AND vm.VCTDeliveryZipcodeId <> ''
AND vhml.MatrixType = 2
Ordinarily the above code would be fine. However, there are some records in the VCTHaulageMileageLog table where partial data was entred in the ZipcodeFrom and ZipcodeTo fields.
These fields are both NVARCHAR(10).
I could exeucte a lengthy statement to catch each permutation (i.e. where length of ZipcodeFrom = 1 and length of ZipcodeTo = 1), but is there a better approach than the following example?
SELECT * FROM VCTMovements vm JOIN VCTHaulageMileageLog vhml ON
(
SUBSTRING(vm.VCTCollectZipcodeId, 1, 1) = vhml.ZipcodeFrom
AND
SUBSTRING(vm.VCTDeliveryZipcodeId, 1, 1) = vhml.ZipcodeTo
)
OR
(
SUBSTRING(vm.VCTCollectZipcodeId, 1, 2) = vhml.ZipcodeFrom
AND
SUBSTRING(vm.VCTDeliveryZipcodeId, 1, 1) = vhml.ZipcodeTo
)
... etc
... etc
OR
(
SUBSTRING(vm.VCTCollectZipcodeId, 1, 10) = vhml.ZipcodeFrom
AND
SUBSTRING(vm.VCTDeliveryZipcodeId, 1, 10) = vhml.ZipcodeTo
)
WHERE vm.VCTCollectZipcodeId IS NOT NULL
AND vm.VCTDeliveryZipcodeId IS NOT NULL
AND vm.VCTCollectZipcodeId <> ''
AND vm.VCTDeliveryZipcodeId <> ''
AND vhml.MatrixType = 2
Any ideas, please?
Thanks in advance,
Neal
February 21, 2011 at 4:55 am
-- either (LEN ignores trailing spaces)
LEFT(vm.VCTCollectZipcodeId, LEN(vhml.ZipcodeFrom)) = vhml.ZipcodeFrom
-- or
vm.VCTCollectZipcodeId LIKE vhml.ZipcodeFrom + '%'
February 21, 2011 at 5:03 am
Many thanks Ken
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply