Sequence Use. Two Sequences on one column

Question

You can use 1 or 2 sequence.

Let's say I have a table tblOrder and 2 columns in it, OrderID and Company. I only use two companies in my table, IBM and Airtel.

What I want is when is Company = IBM then OrderID insert as 1 and when company is Airtel then it should insert OrderID as 1001 and repeat steps with increment 1, meaning next OrderID for IBM should be 2 and for Airtel should be 1002.

I think I need to use two sequences, on one column with case expression.

Answer 1

EDIT: more ideas: 1) You could have a VIEW with ROW_NUMBER() OVER(PARTITION BY Company ORDER BY OrderID) . This will always deliver a gap-less running number for each group - but maybe not the same in each call.

2) Use two separate tables each with an IDENTITY and combine them with UNION

3) Use a function to get the next ID (in an after insert trigger?)

CREATE FUNCTION dbo.GetNextID(@Company VARCHAR(MAX))
RETURNS INT
AS
BEGIN
    RETURN ISNULL((SELECT MAX(OrderID) FROM YourTable WHERE Company=@Company) + 1,0);
END

Hope this helps...

--old text

If your sequences don't have to be without gaps, you could try something like this:

There are other approaches (e.g. use two different tables and combine them in a UNION SELECT).

DECLARE @tbl TABLE (OrderID INT IDENTITY
                   ,Company VARCHAR(100)
                   ,ResolvedID AS CASE Company WHEN 'Airtel' THEN OrderID+1000
                                               WHEN 'Something' THEN OrderID + 2000 
                                               ELSE OrderID END);
INSERT INTO @tbl VALUES('Airtel')
                      ,('IBM')
                      ,('Something');

SELECT * FROM @tbl;