Advertisement
Guest User

Untitled

a guest
Apr 21st, 2019
112
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C# 3.09 KB | None | 0 0
  1. WITH  ScanData_CTE AS
  2.                 (
  3.                 SELECT  LastScanPerProject.ProjectID, LastScanPerProject.LastScanID, TaskScans.StartTime as LastScanDate, TaskScans.RiskLevel, TaskScanEnvironment.LOC, TaskScanEnvironment.FailedLOC,
  4.                 TaskScans.High, TaskScans.Medium, TaskScans.Low, TaskScans.Information, TaskScans.QueuedOn, TaskScans.StartTime , TaskScans.FinishTime, TaskScans.StatisticsCalcDate
  5.                 FROM
  6.                     (
  7.                         SELECT MAX(TaskScans.Id) AS LastScanID , TaskScans.ProjectId  AS ProjectID
  8.                         FROM TaskScans with (NOLOCK)
  9.                         WHERE TaskScans.ScanType = 1 AND is_deprecated = 0 AND IsPublic = 1
  10.                         GROUP BY TaskScans.ProjectId
  11.                     ) AS LastScanPerProject  
  12.                     INNER JOIN TaskScans with (NOLOCK) ON TaskScans.Id = LastScanPerProject.LastScanID
  13.                     INNER JOIN TaskScanEnvironment with (NOLOCK) on TaskScanEnvironment.ScanId = LastScanPerProject.LastScanID
  14.                 )
  15.             SELECT count(Projects.ID)  as ProjectCount
  16.             FROM Projects with (NOLOCK)
  17.             INNER JOIN [v_UserVisibleTeams] AS [UserTeams] ON [UserTeams].[UserId] = 2 AND [Projects].[Owning_Team] = [UserTeams].[TeamId]
  18.             LEFT JOIN ScanData_CTE with (NOLOCK)  on (Projects.Id = ScanData_CTE.ProjectId)
  19.             where (Projects.[is_deprecated] = 0)  
  20.             ;  WITH  ScanData_CTE AS
  21.                 (
  22.                 SELECT  LastScanPerProject.ProjectID, LastScanPerProject.LastScanID, TaskScans.StartTime as LastScanDate, TaskScans.RiskLevel, TaskScanEnvironment.LOC, TaskScanEnvironment.FailedLOC,
  23.                 TaskScans.High, TaskScans.Medium, TaskScans.Low, TaskScans.Information, TaskScans.QueuedOn, TaskScans.StartTime , TaskScans.FinishTime, TaskScans.StatisticsCalcDate                       
  24.                 FROM
  25.                     (
  26.                         SELECT MAX(TaskScans.Id) AS LastScanID , TaskScans.ProjectId  AS ProjectID
  27.                         FROM TaskScans with (NOLOCK)
  28.                         WHERE TaskScans.ScanType = 1 AND is_deprecated = 0 AND IsPublic = 1
  29.                         GROUP BY TaskScans.ProjectId
  30.                     ) AS LastScanPerProject  
  31.                     INNER JOIN TaskScans with (NOLOCK) ON TaskScans.Id = LastScanPerProject.LastScanID
  32.                     INNER JOIN TaskScanEnvironment with (NOLOCK) on TaskScanEnvironment.ScanId = LastScanPerProject.LastScanID
  33.                 )
  34.             SELECT Projects.ID as ProjectId, Projects.[Name] as ProjectName, ScanData_CTE.LastScanID, ScanData_CTE.StartTime as LastScanDate,
  35.                     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,
  36.                     ScanData_CTE.Low, ScanData_CTE.Information, ScanData_CTE.QueuedOn, ScanData_CTE.StartTime , ScanData_CTE.FinishTime, ScanData_CTE.StatisticsCalcDate
  37.             ,[teamPath].FullName as TeamName       
  38.             FROM Projects with (NOLOCK)
  39.             INNER JOIN [v_UserVisibleTeams] AS [UserTeams] ON [UserTeams].[UserId] = 2 AND [Projects].[Owning_Team] = [UserTeams].[TeamId]
  40.             LEFT join CxEntities.Team as [teamPath]  on  [teamPath].Id =[UserTeams].TeamId         
  41.             LEFT JOIN ScanData_CTE with (NOLOCK)  on (Projects.Id = ScanData_CTE.ProjectId)
  42.             where (Projects.[is_deprecated] = 0)  
  43.             order by  LastScanDate DESC   OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement