Hi,

Report Request please.

MSSQL.

Please can I have the below Problem report amended to also include:

Select to include the date of the most recent note on the problem record

Where to exclude any record which has had a note added in the last 7 days

Thanks in advance,

P

SELECT prob.PROBLEMID "Problem ID",longtodate(prob.REPORTEDTIME) "Reported Date",prob.TITLE "Title",ownaaa.FIRST_NAME "Technician",longtodate(prob.UPDATEDTIME) "Updated Date",longtodate(prob.CLOSEDTIME) "Closed Date",orgaaa.FIRST_NAME "Reported by",priodef.PRIORITYNAME "Priority",urgdef.NAME "Urgency",statdef.STATUSNAME "Status",catadef.CATEGORYNAME "Category",subcatadef.NAME "Subcategory",itemdef.NAME "Item",impactdef.NAME "Impact",probdesc.FULL_DESCRIPTION "Description",sdo.NAME "Site" FROM Problem prob

LEFT JOIN SDUser orgsd ON prob.ORIGINATORID=orgsd.USERID

LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID

LEFT JOIN SDUser ownsd ON prob.OWNERID=ownsd.USERID

LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID

LEFT JOIN StatusDefinition statdef ON prob.STATUSID=statdef.STATUSID

LEFT JOIN PriorityDefinition priodef ON prob.PRIORITYID=priodef.PRIORITYID

LEFT JOIN UrgencyDefinition urgdef ON prob.URGENCYID=urgdef.URGENCYID

LEFT JOIN CategoryDefinition catadef ON prob.CATEGORYID=catadef.CATEGORYID

LEFT JOIN SubCategoryDefinition subcatadef ON prob.SUBCATEGORYID=subcatadef.SUBCATEGORYID

LEFT JOIN ItemDefinition itemdef ON prob.ITEMID=itemdef.ITEMID

LEFT JOIN ProblemResolution probResol ON prob.PROBLEMID=probResol.PROBLEMID

LEFT JOIN ImpactDefinition impactdef ON probResol.IMPACTID=impactdef.IMPACTID

LEFT JOIN ProblemToDescription probdesc ON prob.PROBLEMID=probdesc.PROBLEMID

LEFT JOIN SiteDefinition siteDef ON prob.SITEID=siteDef.SITEID

LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID

WHERE  (((statdef.STATUSNAME != N'Closed' COLLATE Latin1_General_CS_AI) AND (statdef.STATUSNAME != N'Resolved' COLLATE Latin1_General_CS_AI)) OR (statdef.STATUSNAME IS NULL))