Business View Problem - Last Completed & Next (Over)Due Activity

[horatiog]

Hi,

Hope you can help here. I have a requirement to write a business view from a table of Activities (ACTIVITY), to determine the next due (or overdue) activity and last completed activity for each pair of who & what entities.

Simplified, the structure of the ACTIVITY table is as follows:

ID                VARCHAR2(255),
WHOID             VARCHAR2(255),
WHATID            VARCHAR2(255),
TYPE              VARCHAR2(255),
HEADER            VARCHAR2(255),
DETAIL            CLOB,
DATETIME_DUE      DATE,
DATETIME_ACTIONED DATE

I am using SQL*Plus on Oracle 10g Enterprise Edition Release 10.1.0.3.0.

The ID is the unique primary key.

Unfortunately, there are a couple of additonal problems:

1) I need to return additional columns besides the above date & times. (E.g. HEADER, DETAIL, etc. of the two activities)

2) I am only provided with the WHOID & WHATID (For each such pair there may be none, one or (usually) more than one activity.)

3) Further, the DATETIME_DUE & DATETIME_ACTIONED datestamps may not be unqiue, even with the WHOID & WHATID. (i.e. there may be more than one activity for the pair at exactly the same date & time.)

The structure of the table is outwith my control. I am provided with the WHOID & WHATID and, for each such pair provided, need to return the following information:

the ID, SUBJECT & maximum DATETIME_ACTIONED where DATETIME_ACTIONED < SYSDATE (i.e details of the most recently completed activity completed prior to "now")

the ID, SUBJECT & the minimum DATETIME_DUE for the provided column pair where DATETIME_ACTIONED is NULL (i.e details of the most overdue (or next due) activity. An activity row with a value in DATETIME_DUE but not in DATETIME_ACTIONED indicates an uncompleted activity.)


I created the following view:

SELECT
	LAST_ACTIVITY_DETAILS.ID,
	LAST_ACTIVITY.DATETIME,
	LAST_ACTIVITY.TYPE,
	LAST_ACTIVITY.HEADER,
	LAST_ACTIVITY.DETAIL,
	NEXT_ACTIVITY_DETAILS.ID,
	NEXT_ACTIVITY.DATETIME,
	NEXT_ACTIVITY.TYPE,
	NEXT_ACTIVITY.HEADER,
	NEXT_ACTIVITY.DETAIL
FROM
	(	SELECT
 			WHOID,
 			WHATID,
 			MAX(DATETIME_ACTIONED) DATETIME
		FROM
 			ACTIVITY
		WHERE
 			DATETIME_ACTIONED < SYSDATE
		GROUP BY
 			WHOID,
 			WHATID
	) 						LAST_ACTIVITY,
	ACTIVITY					LAST_ACTIVITY_DETAILS,
	(	SELECT
 			WHOID,
 			WHATID,
 			MAX(DATETIME_DUE) DATETIME
		FROM
 			ACTIVITY
		WHERE
 			DATETIME_ACTIONED IS NULL
		GROUP BY
 			WHOID,
 			WHATID
	) 						NEXT_ACTIVITY,
	ACTIVITY					NEXT_ACTIVITY_DETAILS
WHERE
	(	LAST_ACTIVITY.WHOID (+) = NEXT_ACTIVITY.WHOID
		AND
		LAST_ACTIVITY.WHATID (+) = NEXT_ACTIVITY.WHATID 
	)
	AND
	(	NEXT_ACTIVITY_DETAILS.WHOID (+) = NEXT_ACTIVITY.WHOID
		AND
		NEXT_ACTIVITY_DETAILS.WHATID (+) = NEXT_ACTIVITY.WHATID
		AND
		NEXT_ACTIVITY_DETAILS.DATETIME_DUE (+) = NEXT_ACTIVITY.DATETIME
	)
	AND
	(	LAST_ACTIVITY_DETAILS.WHOID (+) = LAST_ACTIVITY.WHOID
		AND
		LAST_ACTIVITY_DETAILS.WHATID (+) = LAST_ACTIVITY.WHATID
		AND
		LAST_ACTIVITY_DETAILS.DATETIME_ACTIONED (+) = LAST_ACTIVITY.DATETIME
	)

However, I think this is quite inefficient and also it returns duplicates because there can be multiple rows with the same WHOID, WHATID & DATETIME info.

My question is, is there a more efficient way of doing this which will only return one row per WHOID & WHATID. In the case of multiple activities for the pair with the same date & time, I don't really mind with activity is selected.

Hope somebody can offer some help. Many thanks in anticipation.

 

[cmmrfrds]

Please investigate Oracles Analytical Functions, which can give a "Window" on multiple rows of data. For example, the window size could be all the rows that have the same whoid and whatid or just the same whoid or any other size. Some Analytical Functions to look at are;
LEAD
LAG
where you can look at rows on either side of the current row and
RANK
DENSE_RANK
where you can rank or order the data in a "Window", created in a subquery that can be evaluated in the main query or any other way you want.