SQL Query for Saved Search

Version 1

    Details

     

    We have a Saved Search where it returns only Incidents owned by the Service Desk, created in a date range where all Tasks (regardless of count) are owned by the Team of Service Desk. I'm trying to turn this into a SQL Query but it's returning All Incidents for the month with Tasks to other Team(s). Can you assist me with creating a SQL Query from this. Below is a screen shot of the Saved Search.




     


    Resolution

     

    The following SQL SELECT statement will find all Incidents created in a date range where the OwnerTeam is Service Desk and ALL the Tasks associated to that Incident have an OwnerTeam value of Service Desk.


    SELECT *
    FROM Incident
    WHERE OwnerTeam = 'Service Desk'

    AND (Incident.CreatedDateTime >= '2016-03-12 00:00:00.000' AND Incident.CreatedDateTime <=  '2017-03-13 23:59:59.000'
    AND (SELECT COUNT(Task.RecId)
    FROM Task WHERE Task.ParentLink_RecID = Incident.RecId) =
    (SELECT COUNT(Task.RecId)
    FROM Task
    WHERE Task.ParentLink_RecID = Incident.RecId
    AND (Task.OwnerTeam = 'Service Desk'))
    AND 0 < (SELECT COUNT(Task.RecId) FROM Task
    WHERE Task.ParentLink_RecID = Incident.RecId AND (Task.OwnerTeam = 'Service Desk')))