Tuesday, March 27, 2012

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 Wimwhat whould happen to the due-date if the priority changed?|||what whould happen to the due-date if the priority changed?

The due date has to change to!|||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|||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?|||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|||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

No comments:

Post a Comment