SCSM Change Request Details

USE DWDataMart

Open CR by Assigned User

Select [Assigned User], COUNT( [Assigned User]) NumOfOpenCRs from APPNAME_ChangeRequestvw where status not in ('Closed', 'Canceled')
and CreatedDate > '2012-06-30 23:59:59.000'
group by [Assigned User]
order by COUNT( [Assigned User])desc

All Open CRs

Select ID,DisplayName,DATEDIFF(DAY, createddate, GETDATE()) as DaysOld,
Area, Category, Application ,ApprovalStatus, Status as CRStatus, [Assigned User],
[Created By],CreatedDate,Environment,ActualEndDate,ActualStartDate,AliasofSMorPMApprover,
GMException,WeekendDeployment
From APPNAME_ChangeRequestvw
where status not in ('Closed', 'Canceled')
and CreatedDate > '2012-06-30 23:59:59.000'
order by DATEDIFF(DAY, createddate, GETDATE()) desc

All Open CRs which are UnAssigned
Select ID,DisplayName,DATEDIFF(DAY, createddate, GETDATE()) as DaysOld,
Area, Category, Application,ApprovalStatus,Status as CRStatus, [Assigned User],
[Created By],CreatedDate,Environment,ActualEndDate,ActualStartDate,AliasofSMorPMApprover,
GMException,WeekendDeployment
From APPNAME_ChangeRequestvw
where status not in ('Closed', 'Canceled')
and CreatedDate > '2012-06-30 23:59:59.000'
and  [Assigned User] = 'Not Assigned'
order by DATEDIFF(DAY, createddate, GETDATE()) desc