Created
November 6, 2014 18:58
-
-
Save dustyhoppe/3e3bbcffcaa0bae5d4e6 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @AccountID INT = 11, @UserID INT = NULL, @GroupID INT = NULL; | |
| declare @Users table (UserID int primary key clustered (UserID)); | |
| declare @HaveUsers bit = 1; | |
| if @UserID is not null | |
| insert into @Users (UserID) values (@UserID) | |
| else if @GroupID is not null | |
| insert into @Users (UserID) | |
| select ugu.UserID | |
| from dbo.UserGroupUser ugu | |
| inner join dbo.AccountUserView u on ugu.AccountID = u.AccountID and ugu.UserID = u.UserID and u.Deleted = 0 | |
| where ugu.AccountID = @AccountID | |
| and ugu.GroupID = @GroupID | |
| else | |
| set @HaveUsers = 0; | |
| if @HaveUsers = 1 | |
| begin | |
| with | |
| ClosedCTE (AccountID, ApplicationID, ClosedDate) as | |
| ( | |
| select jasl.AccountID, jasl.ApplicationID, min(jasl.LogDate) | |
| from JobApplicationStatusLog jasl | |
| inner join JobApplicationStatus jas on jas.AccountID = jasl.AccountID and jas.StatusID = jasl.OldStatusID and jas.[Closed] = 1 | |
| WHERE jasl.AccountID = @AccountID | |
| group by jasl.AccountID, jasl.ApplicationID | |
| ), | |
| DifferenceCte ([Difference]) as | |
| ( | |
| select datediff(hour, ja.DateCreated, ce.ClosedDate) | |
| from JobApplication ja | |
| inner join ClosedCTE ce on ce.AccountID = ja.AccountID and ce.ApplicationID = ja.ApplicationID and ce.ClosedDate is not null | |
| where ja.AccountID = @AccountID | |
| and ja.OwnerUserID in (select UserID from @Users) | |
| ) | |
| select ISNULL(avg(iif([Difference]=0,1,[Difference])), 0) | |
| from DifferenceCte; | |
| end | |
| else | |
| begin | |
| with | |
| ClosedCTE (AccountID, ApplicationID, ClosedDate) as | |
| ( | |
| select jasl.AccountID, jasl.ApplicationID, min(jasl.LogDate) | |
| from JobApplicationStatusLog jasl | |
| inner join JobApplicationStatus jas on jas.AccountID = jasl.AccountID and jas.StatusID = jasl.OldStatusID and jas.[Closed] = 1 | |
| WHERE jasl.AccountID = @AccountID | |
| group by jasl.AccountID, jasl.ApplicationID | |
| ), | |
| DifferenceCte ([Difference]) as | |
| ( | |
| select datediff(hour, ja.DateCreated, ce.ClosedDate) | |
| from JobApplication ja | |
| inner join ClosedCTE ce on ce.AccountID = ja.AccountID and ce.ApplicationID = ja.ApplicationID and ce.ClosedDate is not null | |
| where ja.AccountID = @AccountID | |
| ) | |
| select avg(iif([Difference]=0,1,[Difference])) | |
| from DifferenceCte; | |
| end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment