Results 1 to 3 of 3

Thread: column formula

  1. #1
    Join Date
    Nov 2002
    Posts
    59

    Unanswered: column formula

    Hi everybody,

    I have an company table and it has 2 columns, Company Code and User Code, I am incrementing "User Code" with "column identity" property of MS SQL Server 2K. I have different companies and those companies have different users. When I increment User Code one by one, of course it doesnt consider whether it is the same company or not.

    Question 1: How can I satisfy this condition below?

    EX:

    company user
    1---------1
    1---------2
    2---------3
    2---------4
    3---------5
    4---------6


    What I want is

    company user
    1---------1
    1---------2
    1---------3
    2---------1
    2---------2
    3---------1
    3---------2
    3---------3

    etc.


    Question 2: I want to know that whether it is possible to do that by writing column formula or not?
    Last edited by cuneyt; 12-20-02 at 05:18.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Post Re: column formula

    Question 1: How can I satisfy this condition below?
    EX:
    company user
    1---------1
    1---------2
    2---------3
    2---------4
    3---------5
    What I want is
    company user
    1---------1
    1---------2
    1---------3
    2---------1
    2---------2
    3---------1
    Question 2: I want to know that whether it is possible to do that by writing column formula or not?
    A1 One approach in supporting such a business requirement: one may implement a "key assignment" table that privately tracks and assigns user ID numbers for each company.

    A2 It is not exactly clear what is meant by a "column formula"? However, the built in MS Sql Server 2k identity column support / functionality likely won't help much in implementing a typical "key assignment" table. (A "key assignment" table approach, as described in A1, would likely be implemented primarily using stored procedures / triggers, and / or user functions).

  3. #3
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: column formula

    /*
    create table companyuser (
    "user" int identity(1,1) primary key
    ,company int not null
    )
    */

    --ad 1
    select
    company
    ,newusernum=(select count(*) from companyuser cu2 where cu1.company=cu2.company and cu1."user"<=cu2."user")
    ,origusernum="user"
    from companyuser cu1

    --OR on large table
    create table companyuserTMP (
    "id" int identity(1,1) primary key
    ,origusernum int null
    ,company int not null
    )
    insert companyuserTMP(company,origusernum)
    select company,"user"
    from companyuser
    order by company,"user"
    select
    tmp.company
    ,newusernum=tmp."id"-XXX."id"+1
    ,origusernum
    from companyuserTMP tmp
    join (
    select "id"=min("id"),company
    from companyuserTMP
    group by company
    ) XXX on tmp.company=XXX.company
    drop table companyuserTMP


    --ad 2-- computed columns can use one row information only, use TR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •