none
caclulated column RRS feed

  • Question

  • i have a table in which there are 9 columns as

    premium_id, customer_id, plan_id, premium_payment_date, due_date, premium_amount ,IsActive, surrender_date, fine.

    there will be so many customers in this table.I want my due_date column automatically calculate date at the time of premium payment.

    In the premium_payment_date column 1st entry for each customer is same as policy purchase date,hence the due date will be next 30 days from the policy purchase date.

    please me out.


    deepak kumar

    Monday, September 10, 2012 7:38 AM

Answers

  • U can Either Write an update statement or u can also alter the table

    If Update

    Update TableName

    Set Due_Date = DateAdd(DD,30,Premium_Payment_Date)

    If u want to alter the table

    Alter Tablename alter Column Due_Date As DateAdd(DD,30,Due_date)


    Please have look on the comment

    Monday, September 10, 2012 7:43 AM
  • create a computed column. see the e.g bellow

    CREATE TABLE [dbo].[tbale_1]
    (
    [premium_id] [int] NULL,
    plan_id int,
    premium_payment_date [datetime] NULL,
    due_date AS (dateadd(dd,30,premium_payment_date)) PERSISTED
    )
    
    insert into [tbale_1] values(1,1,getdate())
    select * from [tbale_1]

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, September 10, 2012 7:46 AM

All replies

  • U can Either Write an update statement or u can also alter the table

    If Update

    Update TableName

    Set Due_Date = DateAdd(DD,30,Premium_Payment_Date)

    If u want to alter the table

    Alter Tablename alter Column Due_Date As DateAdd(DD,30,Due_date)


    Please have look on the comment

    Monday, September 10, 2012 7:43 AM
  • create a computed column. see the e.g bellow

    CREATE TABLE [dbo].[tbale_1]
    (
    [premium_id] [int] NULL,
    plan_id int,
    premium_payment_date [datetime] NULL,
    due_date AS (dateadd(dd,30,premium_payment_date)) PERSISTED
    )
    
    insert into [tbale_1] values(1,1,getdate())
    select * from [tbale_1]

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, September 10, 2012 7:46 AM
  • Computed columns will work for you if you are on SQL 2008. With computed columns you can have the column value generated at one single place. You can make it persisted or not.

    If not SQL 2008, then use a general update statement.

    Hope your requirement is not that basic. Is there any other detail you are missing.


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Monday, September 10, 2012 7:49 AM