In our team of developer’s we use a tracking system, to track work orders, bug fixes, feature requests, and the status of those projects…

One of my recent assignments was to create a “Recently Updated List”

This list will contain the trackID, and a Title… it will sort by the date in descending order so we always see the most recent items first… It will join 2 tables – items(v_items), and events…

Items is the Main table, and Events are details about that item that happen along the tracking of that item…


For example, say you have track 897 897
========
(below are events)
—————————————–
edited by axel on 11/11/06 @ 2:00pm

We now are finished with this track
—————————————–
—————————————–
edited by axel on 11/11/06 @ 1:30pm

We did something else
—————————————–
—————————————–
edited by axel on 11/11/06 @ 1:00pm

we edited the table in the database to meet a need
—————————————–

<cfquery datasource=“#Application.AdminDsn#” name=“getFeaturesList”>
SELECT
v.itemid, v.title,
e.datecreated, e.eventid
FROM v_items v
INNER Join Events e
ON v.itemid = e.itemid
WHERE
e.eventid in (
SELECT TOP 1 e2.eventid
FROM events e2
WHERE e2.itemid = v.itemid
AND e.createdbyuserid = 92
ORDER BY e2.datecreated desc
)
AND
e.datecreated >
<cfqueryparam cfsqltype=“cf_sql_date”
value=“#DateAdd(‘m’,-1,Now())#”/>

AND v.statusID IN (5)
ORDER BY e.datecreated desc
</cfquery>