thanks Alan,

what if the controlstart and controlend range is larger, ex: 1000 to 1025? Then I can't use the union all correct?

Thank you very much for your solution. However, I probably didn't explain clearly enough.

The first sequence number doesn't necessarily start out with 1, it could start with 567891011 or...

I got it working

set m1.TRACK1 = STUFF(m1.TRACK1 collate database_default , LEN(m1.TRACK1 collate database_default) - LEN(c1.track1 collate database_default) - 14, LEN(c1.track1 collate database_default), c1.track1 collate database_default)

thank you all for helping me. yes STUFF did work for me and it should be -1 instead of +1. Thanks again.

Thank you very much. what if my column1 have value 'abcdef0000' and column 2 value is 123. Then I cannot convert them to integer to add them together....

Thanks. I forgot to mention that these columns are string not integer.

Thanks Steve for all the help.

I came up with this code:

DECLARE @BUNDLE INT

SELECT @bundle = 10780

SELECT CONTROLNUM,NUMPACK,

case WHEN ((NUMPACK % 10) = 0) THEN

SET...

Hi Steve, thanks for the suggestion. Sorry, you are correct everything is based on column a (the counter for other columns).

thanks Craig! That works.

Thanks Kingston. So do I need to create an after insert trigger on the orders table and that trigger would automatically insert a status in the status table?

curiosity is not the same as rude or unprofessional. In a real business world, you won't last for an hour with this kind of attitude. I'm really feel...

Mr Moden

Please read my before you starting to criticize me. Didn't I say "Greatly appreciate all the helps" in my first post????? Besides, I already very patiently telling...

Hi Jeff - I will be using this view to generate a report for accounting.

OK. So I have a fictitous view with these columns: buyer name, address, product number, product description (which is a join between purchaser and product table).

john doe, 123 usa, 1000,...