SELECT x.projecttitle, x.ProjectID, x.salesrepid, 30 AS dspDaysInactive, x.MaxDate,
x.projectdate, x.ThreadUpdate, x.sentdate
FROM (
SELECT p.projecttitle, p.ProjectID, p.salesrepid, 30 AS dspDaysInactive,
MaxDate = CASE
WHEN Max(s.sentdate) > Max(pu.submitDate) THEN Max(s.sentdate)
ELSE
CASE
WHEN p.projectDate > Max(pu.submitDate) THEN p.projectDate
ELSE Max(pu.submitDate)
END
END
, Max(p.projectdate) AS ProjectDate, Max(pu.submitDate) AS ThreadUpdate, Max(s.sentdate) AS SentDate
FROM tbl_PRJ_Projects p
INNER JOIN tbl_PRJ_ProjectUpdates pu ON (p.projectid = pu.projectid)
LEFT OUTER JOIN tbl_PRJ_Samples s ON (pu.updateid = s.updateid)
WHERE p.OwnerID =
AND p.OwnerType =
AND p.projectStatus = 4
GROUP BY p.ProjectID, p.projectDate, p.salesrepid, p.projecttitle ) as X
WHERE MaxDate = #variables.InactiveDate#
ORDER BY x.projectTitle, x.maxDate
UPDATE TBL_PRJ_PROJECTS
SET projectStatus =
WHERE OwnerID =
AND OwnerType =
AND ProjectID =