Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    2

    Unanswered: Using Computed Column Specification

    Hello everyone,

    I am new to the forums and to sql. Not exactly new to SQL but more a beginner which actually never understood this programming language. But sometimes I need to learn some new stuff.

    I have a question about Computed Column Specification which you can specify as a formula for each column inside a table.

    I have now columns named Age and Class.
    Classes are "Kids" (ID #1) , "Junior" (ID #2) and "Senior" (ID #3)
    Kids, which is for age of 6 till 12
    Junior, which is for 12 till 16
    Senior, 16 and above.

    I have already searched for hours (I really did) on the internet for a solution, but ended with more questions because of the complicated solutions.

    Now the Age is shown as a result of a formule of DOB (Date of Birth column), now I want the exact same thing, but the age must specify which Class the user is in.
    Example, when I add a user with the birthdate 25/03/1988 (DD/MM/YYYY) he/she gets 24 as age. With this formula:
    (datediff(year,[age],getdate()))

    Now I want that the user gets "Senior" as Class (same table).
    Senior is ID 3 in this case.
    Now I do know how Case, When and Then works, but the validation fails. After reading some forums I understood that I should use a create function method. I am not really experienced with creating functions and therefore I am looking for some help. Also the coding looks more different as I am used to. Can anyone help me finding me a solution or at least tell me where to start with. Also I do not know how to link the Computed Column to a created formula.

    Thank you in advance.

    Kind regards,
    Umit
    Last edited by galaumiy; 12-13-12 at 20:26.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    If you are looking to figure out how to write a user defined function, just go to the Books Online, or search in Google, for one of 10 million examples you might find. Then copy it into Management Studio, rip out the guts and the complications of whatever you might have found, and substitute a very simple logic where the function might add two input values and return a result.

    That's how I learn anything . . . I just try doing it.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Dec 2012
    Posts
    2
    So, I should use a "Create Function" function and link the column to this function I guess? Is this the correct way?

    I am absolutely not asking for an complete solution. I just need to find the correct way to solve this. Do you have any suggestion for a book or website?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm going to be evil and give you the full solution, then let you figure it out. You'll probably wish that I hadn't, but it will be good for you!
    Code:
    SELECT d
    ,  CASE
          WHEN d < DATEADD(YEAR, -16, GETDATE()) THEN 'Senior'
          WHEN d < DATEADD(year, -12, Getdate()) THEN 'Junior'
          WHEN d < DATEADD(year,  -6, GetDate()) THEN 'Kid'
          ELSE 'Pipsqueak!'
       END AS 'Class' 
       FROM (SELECT DATEADD(month, DateDiff(month, 0, GetDate()) - number, 0) AS d
          FROM master.dbo.spt_values
          WHERE 'P' = type) AS sampledates
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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