Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH ScanData_CTE AS
- (
- SELECT LastScanPerProject.ProjectID, LastScanPerProject.LastScanID, TaskScans.StartTime as LastScanDate, TaskScans.RiskLevel, TaskScanEnvironment.LOC, TaskScanEnvironment.FailedLOC,
- TaskScans.High, TaskScans.Medium, TaskScans.Low, TaskScans.Information, TaskScans.QueuedOn, TaskScans.StartTime , TaskScans.FinishTime, TaskScans.StatisticsCalcDate
- FROM
- (
- SELECT MAX(TaskScans.Id) AS LastScanID , TaskScans.ProjectId AS ProjectID
- FROM TaskScans with (NOLOCK)
- WHERE TaskScans.ScanType = 1 AND is_deprecated = 0 AND IsPublic = 1
- GROUP BY TaskScans.ProjectId
- ) AS LastScanPerProject
- INNER JOIN TaskScans with (NOLOCK) ON TaskScans.Id = LastScanPerProject.LastScanID
- INNER JOIN TaskScanEnvironment with (NOLOCK) on TaskScanEnvironment.ScanId = LastScanPerProject.LastScanID
- )
- SELECT count(Projects.ID) as ProjectCount
- FROM Projects with (NOLOCK)
- INNER JOIN [v_UserVisibleTeams] AS [UserTeams] ON [UserTeams].[UserId] = 2 AND [Projects].[Owning_Team] = [UserTeams].[TeamId]
- LEFT JOIN ScanData_CTE with (NOLOCK) on (Projects.Id = ScanData_CTE.ProjectId)
- where (Projects.[is_deprecated] = 0)
- ; WITH ScanData_CTE AS
- (
- SELECT LastScanPerProject.ProjectID, LastScanPerProject.LastScanID, TaskScans.StartTime as LastScanDate, TaskScans.RiskLevel, TaskScanEnvironment.LOC, TaskScanEnvironment.FailedLOC,
- TaskScans.High, TaskScans.Medium, TaskScans.Low, TaskScans.Information, TaskScans.QueuedOn, TaskScans.StartTime , TaskScans.FinishTime, TaskScans.StatisticsCalcDate
- FROM
- (
- SELECT MAX(TaskScans.Id) AS LastScanID , TaskScans.ProjectId AS ProjectID
- FROM TaskScans with (NOLOCK)
- WHERE TaskScans.ScanType = 1 AND is_deprecated = 0 AND IsPublic = 1
- GROUP BY TaskScans.ProjectId
- ) AS LastScanPerProject
- INNER JOIN TaskScans with (NOLOCK) ON TaskScans.Id = LastScanPerProject.LastScanID
- INNER JOIN TaskScanEnvironment with (NOLOCK) on TaskScanEnvironment.ScanId = LastScanPerProject.LastScanID
- )
- SELECT Projects.ID as ProjectId, Projects.[Name] as ProjectName, ScanData_CTE.LastScanID, ScanData_CTE.StartTime as LastScanDate,
- Projects.[Owning_Team] as Team, Projects.[Is_Public], Projects.[Owner] , ScanData_CTE.RiskLevel, ScanData_CTE.LOC, ScanData_CTE.FailedLOC , ScanData_CTE.High, ScanData_CTE.Medium,
- ScanData_CTE.Low, ScanData_CTE.Information, ScanData_CTE.QueuedOn, ScanData_CTE.StartTime , ScanData_CTE.FinishTime, ScanData_CTE.StatisticsCalcDate
- ,[teamPath].FullName as TeamName
- FROM Projects with (NOLOCK)
- INNER JOIN [v_UserVisibleTeams] AS [UserTeams] ON [UserTeams].[UserId] = 2 AND [Projects].[Owning_Team] = [UserTeams].[TeamId]
- LEFT join CxEntities.Team as [teamPath] on [teamPath].Id =[UserTeams].TeamId
- LEFT JOIN ScanData_CTE with (NOLOCK) on (Projects.Id = ScanData_CTE.ProjectId)
- where (Projects.[is_deprecated] = 0)
- order by LastScanDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement