Complicated Computed Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complicated Computed Column

Author

Topic

asoltys
Starting Member

8 Posts

Posted - 2008-05-08 : 16:48:39

Hi,

I have three tables:

processes
activities
activities_processes

The activities_processes table has these columns:

process_id
activity_id
estimated_completion_date
completion_date

Each process has a 'status' which is a computed column that can take on one of five values according to the following rules:

status = complete: All of the process' activities have a completion date set

status = late: At least one of the process' activities' estimated date is in the past

status = endangered: At least one of the process' activities' estimated date is within two weeks of the current date

status = started: At least one of processes' activities has a completion date set

status = not started: None of the process' activities have a completion date set

The rules are listed in order of priority (i.e. if a process meets the conditions for being both late and endangered, then it is considered late):

I would like to run a query that returns a recordset with two columns, status and num_processes.

For example:

status | num_processes
not started | 5
started | 16
late | 4
endangered | 12
complete | 25

I'm having a hard time figuring out how to write a query that will produce this sort of result. Can anyone help?

Thanks in advance,

Adam Soltys
http://adamsoltys.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-08 : 17:13:03

select status, count(*) as num_processes from activities_processes
group by status

E 12°55'05.25"
N 56°04'39.16"

asoltys
Starting Member

8 Posts

Posted - 2008-05-08 : 18:31:14

Hi, I appreciate the response but 'status' is not an actual column in the activities_processes table. Rather, it's something that needs to be calculated on the fly which is what I'm actually stuck on. Sorry if that was not clear.

Maybe a good solution would be to write a STATUS() function that takes in a process_id as an argument and returns the status. I'll have to look into that since I've never written functions in SQL before. Will it be possible to GROUP BY the result of a function call though?

Or are there any better ways to solve this problem?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 03:29:33

To accomplish this, I would first create an aggregation table like this

CREATE TABLE	dbo.MyProcessStatus
		(
			Process_ID INT NOT NULL,
			Status VARCHAR(11) NOT NULL
		)

CREATE UNIQUE NONCLUSTERED INDEX IX_ProcessID ON dbo.MyProcessStatus (Process_ID) WITH FILLFACTOR = 95

To populate this table for the first time, I then would run this piece of code

-- What date is today?
DECLARE	@Now	DATETIME

SET	@Now = DATEDIFF(DAY, '19000101', GETDATE())

-- Add all ProcessID status
INSERT	dbo.MyProcessStatus
	(
		Process_ID,
		Status
	)
SELECT	Process_ID,
	CASE
		WHEN Complete = 1 THEN 'Complete'
		WHEN Late = 1 THEN 'Late'
		WHEN Endangered = 1 THEN 'Endangered'
		WHEN Started = 1 THEN 'Started'
		WHEN Started = 0 THEN 'Not started'
		ELSE '<unknown>'
	END
FROM	(
		SELECT		ap.Process_ID,
				MIN(CASE WHEN ap.Completion_Date IS NULL THEN 0 ELSE 1 END) AS Complete,
				MAX(CASE WHEN ap.Estimated_Completion_Date < @Now THEN 1 ELSE 0 END) AS Late,
				MAX(CASE WHEN ap.Estimated_Completion_Date >= @Now ap.Estimated_Completion_Date < DATEADD(DAY, 14, @Now) THEN 1 ELSE 0 END) AS Endangered,
				MAX(CASE WHEN ap.Completion_Date IS NULL THEN 0 ELSE 1 END) AS Started,
		FROM		Activities_Processes AS ap
		GROUP BY	ap.Process_ID
	) AS d

At this point I would create a trigger to keep track of all changes in the Activities_Processes table.
The trigger can look something like this

CREATE TRIGGER dbo.trgProcessStatus ON Activities_Processes
FOR UPDATE, INSERT, DELETE
AS

SET NOCOUNT ON

-- Remove all old instances of ProcessID status
DELETE		mps
FROM		dbo.MyProcessStatus AS mps
INNER JOIN	(
			SELECT	Process_ID
			FROM	inserted

			UNION ALL

			SELECT	Process_ID
			FROM	deleted
		) AS x ON x.Process_ID = mps.Process_ID

-- What date is today?
DECLARE	@Now	DATETIME

SET	@Now = DATEDIFF(DAY, '19000101', GETDATE())

-- Add new instance of ProcessID status
INSERT		dbo.MyProcessStatus
		(
			Process_ID,
			Status
		)
SELECT		Process_ID,
		CASE
			WHEN Complete = 1 THEN 'Complete'
			WHEN Late = 1 THEN 'Late'
			WHEN Endangered = 1 THEN 'Endangered'
			WHEN Started = 1 THEN 'Started'
			WHEN Started = 0 THEN 'Not started'
			ELSE '<unknown>'
		END
FROM		(
			SELECT		ap.Process_ID,
					MIN(CASE WHEN ap.Completion_Date IS NULL THEN 0 ELSE 1 END) AS Complete,
					MAX(CASE WHEN ap.Estimated_Completion_Date < @Now THEN 1 ELSE 0 END) AS Late,
					MAX(CASE WHEN ap.Estimated_Completion_Date >= @Now AND ap.Estimated_Completion_Date < DATEADD(DAY, 14, @Now) THEN 1 ELSE 0 END) AS Endangered,
					MAX(CASE WHEN ap.Completion_Date IS NULL THEN 0 ELSE 1 END) AS Started,
			FROM		Activities_Processes AS ap
			INNER JOIN	inserted as i ON i.Process_ID = ap.Process_ID
 			GROUP BY	ap.Process_ID
		) AS d

And to get your result as originally wanted, I would use this query

SELECT		Status,
		COUNT(*) AS Num_Processes
FROM		dbo.MyProcessStatus 
GROUP BY	Status

E 12°55'05.25"
N 56°04'39.16"

asoltys
Starting Member

8 Posts

Posted - 2008-05-09 : 17:32:45

Peso, that's fantastic! Thanks for taking the time to write this all up. It's going to take me a while to digest but I'm sure I'll be a better SQL programmer by the end of it.

Regards,
Adam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources