Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Unanswered: Auto date through priority

    Hi,

    I have a table which contains a Create datetimefield which has a default on the current date , a priorityfield and another datetimefield which will be the due date and has to be calculated by the first date and the priority field,

    How can i do this and what fields must i have.

    Does someone does this?
    can someone help me with this?

    Kind regards Wim

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    what whould happen to the due-date if the priority changed?

  3. #3
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Quote Originally Posted by Kaiowas
    what whould happen to the due-date if the priority changed?
    The due date has to change to!

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    use a computed column for the due date, fe:
    CREATE TABLE TAB1 (
    FIRSTDATE DATETIME DEFAULT GETDATE()
    , PRIO INTEGER
    , DUEDATE AS DATEADD(DD, PRIO, GETDATE())
    )
    GO

    INSERT INTO TAB1 (PRIO) VALUES (1)
    GO

    SELECT * FROM TAB1
    UPDATE TAB1 SET PRIO = 10
    SELECT * FROM TAB1
    GO

    DROP TABLE TAB1
    GO


    EDIT: Just to be sure; either have a default prio or have a not null constraint

  5. #5
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Quote Originally Posted by Kaiowas
    use a computed column for the due date, fe:
    CREATE TABLE TAB1 (
    FIRSTDATE DATETIME DEFAULT GETDATE()
    , PRIO INTEGER
    , DUEDATE AS DATEADD(DD, PRIO, GETDATE())
    )
    GO

    INSERT INTO TAB1 (PRIO) VALUES (1)
    GO

    SELECT * FROM TAB1
    UPDATE TAB1 SET PRIO = 10
    SELECT * FROM TAB1
    GO

    DROP TABLE TAB1
    GO


    EDIT: Just to be sure; either have a default prio or have a not null constraint
    And what if i use the priority through an foreign key an have several prioritys which uses different time like:

    prio 1 = 1day
    prio 2 =1 week
    prio3 = 1 month

    How will it look like then?

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    well, it could look like:

    CREATE TABLE TAB1 (
    FIRSTDATE DATETIME DEFAULT GETDATE()
    , PRIO INTEGER NOT NULL
    , DUEDATE AS CASE PRIO
    WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
    WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
    WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
    ELSE DATEADD(YY, 1, FIRSTDATE)
    END
    )
    GO

  7. #7
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Quote Originally Posted by Kaiowas
    well, it could look like:

    CREATE TABLE TAB1 (
    FIRSTDATE DATETIME DEFAULT GETDATE()
    , PRIO INTEGER NOT NULL
    , DUEDATE AS CASE PRIO
    WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
    WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
    WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
    ELSE DATEADD(YY, 1, FIRSTDATE)
    END
    )
    GO
    Thanx alot man, that hit the spot!!
    I really appreciated your help..

    Cheers Wim

Posting Permissions

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