Re: Looking for help with a calendar logic

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 22:01:35 +0100

Let me suggest a change to your design. Materializing an additional row for
each day is an overhead you don't need. Also, the Status is essentially a
derived column. The following is a more compact way to track the same
information:

CREATE TABLE Tasks (userid VARCHAR(25) NOT NULL, start_dt DATETIME NOT NULL
DEFAULT CURRENT_TIMESTAMP, task VARCHAR(250) NOT NULL, priority INTEGER NOT
NULL, tasktype VARCHAR(20) NOT NULL, working_on CHAR(1) NOT NULL DEFAULT
'N', CHECK (working_on IN ('Y','N') AND (completed_dt IS NULL OR
working_on='N')), completed_dt DATETIME NULL PRIMARY KEY
(start_dt,userid,task))

Notice the addition of Completed_Dt and the removal of Status.

Insert 3 tasks as sample data:

INSERT INTO Tasks
 (userid, start_dt, task, priority, tasktype, completed_dt)
 SELECT 'Mike', '20041001', 'Task A', 1, 'X', '20041003' UNION ALL
 SELECT 'Mike', '20041003', 'Task B', 1, 'X', NULL UNION ALL
 SELECT 'Mike', '20041007', 'Task C', 1, 'X', NULL

Now you can easily display the result in the form you asked for:

SELECT userid,
 CASE
  WHEN completed_dt IS NOT NULL THEN 'Completed'
  WHEN working_on = 'Y' THEN 'Working on'
  WHEN DATEDIFF(DAY,start_dt,CURRENT_TIMESTAMP)=0 THEN 'New'
  WHEN DATEDIFF(DAY,start_dt,CURRENT_TIMESTAMP)>0 THEN 'Forwarded'
  ELSE 'Pending' END
 AS status,
 start_dt, task, priority, tasktype
 FROM Tasks

The duration of a task was being recorded in your original model by the
number of rows for the task. In my version the duration is simply the
difference between the start_dt and the completed_dt (or the current date).

Hope this helps. 

-- 
David Portas
SQL Server MVP
--
Loading