round_robin.sql

SELECT *
INTO #Temp2
FROM
(Select aa.OID, ROW_NUMBER() 
        OVER (ORDER BY aa.OID) AS RowN
FROM VCG_PersonToTeam_Grid_View aa
inner join CG_UserLog (nolock) bb
on aa.PersonMemberOfTeams = bb.UserID
Where aa.GCRecord is null AND aa.TeamName='E-mail Team' and bb.UserLoggedIn=1 )a


/*check if logged in*/
/*
Select aa.OID, ROW_NUMBER() 
        OVER (ORDER BY aa.OID) AS RowN
FROM VCG_PersonToTeam_Grid_View aa
inner join CG_UserLog (nolock) bb
on aa.PersonMemberOfTeams = bb.UserID
Where aa.GCRecord is null AND aa.TeamName='E-mail Team' and bb.UserLoggedIn=1
*/

/*
select *
from VCG_PersonToTeam_Grid_View
where VCG_PersonToTeam_Grid_View.TeamName='E-mail Team' and oid=9

select *
from ChangeGear.dbo.CG_UserLog (nolock) xx
inner join ChangeGear.dbo.VCG_PersonToTeam_Grid_View zz
on xx.userid=zz.PersonMemberOfTeams
where UserLoggedIn=1 and zz.TeamName='E-mail Team'
*/

SELECT *
INTO #Temp
FROM
(Select distinct ST2.IncidentRequestHistoryRecords,
    substring(
        (
            Select ','+ST1.ModifiedFields  AS [text()]
            From CG_IncidentRequestHistoryRecord ST1
            Where ST1.IncidentRequestHistoryRecords= ST2.IncidentRequestHistoryRecords
            ORDER BY ST1.IncidentRequestHistoryRecords
            For XML PATH ('')
        ), 2, 1000) [ModifiedFields]
FROM         CG_IncidentRequestHistoryRecord ST2)s
SELECT *
INTO #Temp3
FROM
(
SELECT    TOP 1 #Temp2.RowN
FROM         CG_IncidentRequest INNER JOIN #Temp ON CG_IncidentRequest.OID = #Temp.IncidentRequestHistoryRecords
INNER JOIN #Temp2 ON CG_IncidentRequest.AssignedTo = #Temp2.OID
Where #Temp.ModifiedFields NOT LIKE '%Assigned To%' AND AssignedTo is not null
Order by SubmitDate DESC)b
Declare @RCnt int
Set @RCnt = (Select CASE WHEN COUNT(RowN) > 1 Then 1 Else 0 End FROM #Temp2)
print @RCnt
Declare @ID int
Set @ID = (Select RowN From #Temp3) + @RCnt
Select OID  as AssignedTo from #Temp2
Where RowN = @ID
Drop Table #Temp
Drop Table #Temp2
Drop Table #Temp3