I am making a system that assigns analysts to customer requirements. I wrote a procedure that searches for the ideal analyst to be assigned to a requirement.

The main tables are:

* grc\_analyst (Table of analysts who solve requirements)
* grc\_requirement (Table of requirements) Many to One relationship with grc\_analyst. The requirements have a field "due\_date" which represents the deadline by which a requirement must be resolved.

The rules for finding the analyst is as follows:

* The number of requirements associated with each analyst must be counted.
* If that number is the same for everyone, that is, analysts have the same amount of requirements assigned,then select the analyst whose average due date for your requirements is furthest from the current date.
* If the number is different, that is, analysts have different number of requirements assigned, then select the analyst with the least amount of requirements assigned.

Here is the code:

```
    DECLARE @totalRequirementsAnalysts int
    DECLARE @idAnalyst int = null 

    SELECT @totalRequirementsAnalysts =count(distinct(a.requirements)) 
    FROM ( 
        SELECT 
            a.id,count(r.id) requirements
        FROM
            grc_analyst a
            INNER JOIN  grc_analyststate ea     ON a.id_analyststate = ea.id AND ea.code = 'A'
            INNER JOIN  grc_analyst_category ac ON a.id = ac.id_analyst 
            INNER JOIN  grc_category c              ON c.id = ac.id_category AND c.code = 'SOAP'        
            LEFT JOIN   grc_requirement r           ON a.id = r.id_analyst AND r.id_requirementstate in (
                SELECT id from grc_requirementstate er where er.code IN ('AS','ER','DL','DC') 
            )
        group by a.id 
    ) a

    IF  (@totalRequirementsAnalysts = 1)
    BEGIN 
        PRINT 'Analysts have the same amount of requirements assigned'

        SET @idAnalyst = (
            SELECT a.id from (
                SELECT TOP(1)
                    a.id,avg (DATEDIFF(DAY,getdate(),r.due_date))average_due_date
                FROM
                    grc_analyst a
                    INNER JOIN  grc_analyststate ea     ON a.id_analyststate = ea.id AND ea.code = 'A'
                    INNER JOIN  grc_analyst_category ac ON a.id = ac.id_analyst 
                    INNER JOIN  grc_category c              ON c.id = ac.id_category AND c.code = 'SOAP'        
                    LEFT JOIN   grc_requirement r           ON a.id = r.id_analyst AND r.id_requirementstate in (
                        SELECT id from grc_requirementstate er where er.code IN ('AS','ER','DL','DC') 
                    )
                group by a.id 
                order by average_due_date DESC
            ) a
        )

    END
    ELSE
    BEGIN
        PRINT 'Analysts have different number of requirements assigned'
        SET @idAnalyst = (
            SELECT a.id from (
                SELECT TOP(1)
                    a.id,count(r.id) requirements
                FROM
                    grc_analyst a
                    INNER JOIN  grc_analyststate ea     ON a.id_analyststate = ea.id AND ea.code = 'A'
                    INNER JOIN  grc_analyst_category ac ON a.id = ac.id_analyst 
                    INNER JOIN  grc_category c              ON c.id = ac.id_category AND c.code = 'SOAP'        
                    LEFT JOIN   grc_requirement r           ON a.id = r.id_analyst AND r.id_requirementstate in (
                        SELECT id from grc_requirementstate er where er.code IN ('AS','ER','DL','DC') 
                    )
                group by a.id 
                order by requirements ASC
            ) a
        )
    END

    SELECT ga.id from grc_analyst ga where ga.id = @idAnalyst

```

As you can see I am using three queries but "from" part is the same for all three (same join tables with same conditions).This procedure complies with the rule and it works, but I want to reduce the number of queries since there is code that repeats itself.

Thanks in advance!