Nested SELECT Statement

Question

SQL is not my forte, but I'm working on it - thank you for the replies.

I am working on a report that will return the completion percent of services for indiviudals in our contracts. There is a master table "Contracts," each individual Contract can have multiple services from the "services" table, each service has multiple standards for the "standards" table which records the percent complete for each standard.

I've gotten as far as calculating the total percent complete for each individual service for a specific Contract_ServiceID, but how do I return all the services percentages for all the contracts? Something like this:

Contract           Service           Percent complete

---

abc Company   service 1        98%
abc Company   service 2      100%
xyz Company   service 1        50%

Here's what I have so far:

SELECT  
    Contract_ServiceId, 
    (SUM(CompletionPercentage)/COUNT(CompletionPercentage)) * 100 as "Percent Complete"  
FROM    dbo.Standard sta WITH (NOLOCK) 
     INNER JOIN dbo.Contract_Service conSer ON sta.ServiceId = conSer.ServiceId
     LEFT OUTER JOIN dbo.StandardResponse standResp ON sta.StandardId = standResp.StandardId 
      AND conSer.StandardReportId = standResp.StandardReportId

WHERE Contract_ServiceId = '[an id]'
GROUP BY Contract_ServiceID

This gets me too:

Contract_serviceid      Percent Complete

---

[an id]                        100%

EDIT: Tables didn't show up in post.

Answer 1

You should be able to add in your select the company name and group by that and the service id and ditch the where clause...

Perhaps like this:

SELECT  
    Contract,
    Contract_ServiceId, 
    (SUM(CompletionPercentage)/COUNT(CompletionPercentage)) * 100 as "Percent Complete"         
FROM    dbo.Standard sta WITH (NOLOCK) 
        INNER JOIN dbo.Contract_Service conSer ON sta.ServiceId = conSer.ServiceId
        LEFT OUTER JOIN dbo.StandardResponse standResp ON sta.StandardId = standResp.StandardId 
                AND conSer.StandardReportId = standResp.StandardReportId
GROUP BY Contract, Contract_ServiceID

Answer 2

I'm not sure if I understand the problem, if the result is ok for a service_contract you canContract Service

SELECT con.ContractId, 
       con.Contract,
       conSer.Contract_ServiceID,
       conSer.Service, 
       (SUM(CompletionPercentage)/COUNT(CompletionPercentage)) * 100 as "Percent Complete"         
FROM    dbo.Standard sta WITH (NOLOCK) 
        INNER JOIN dbo.Contract_Service conSer ON sta.ServiceId = conSer.ServiceId
        INNER JOIN dbo.Contract con ON con.ContractId = conSer.ContractId
        LEFT OUTER JOIN dbo.StandardResponse standResp ON sta.StandardId = standResp.StandardId 
                AND conSer.StandardReportId = standResp.StandardReportId
GROUP BY con.ContractId, con.Contract, conSer.Contract_ServiceID, conSer.Service

make sure you have all the columns you select from the Contract table in the group by clause

Answer 3

Assuming your query works for just the one service, looks like you're most of the way there, leave off the WHERE clause to obtain all results, your GROUP BY will take care of one service per result.

Just join on the Contract table to show the contract related to each service, and you're done.

Answer 4

Because you are grouping by the contract serviceid I think you can just remove the where clause and it should calculate the percentage for all contact serviceids.

If there are no records in dbo.Standard for that contract serviceid, you may need to left outer join instead from the contract service table to the dbo.Standard table in order to show contracts without completion records.

I hope that makes sense... My SQL is getting rusty after migrating to a data framework.

Answer 5

In addition to removing the where clause and adding more group conditions, you also will want to watch out for null records in each of your tables. This requires changing an INNER JOIN to a LEFT JOIN (unless you don't want to see those rows) and some ISNULL's to clean up data. I'm not sure where the StandardReportId concept falls in here, but it looks like a filtering mechanism that I won't toy with.

SELECT  
    ContractID
    ISNULL(Contract_ServiceId, '-1') -- or some other stand in value
    ISNULL((SUM(CompletionPercentage)/COUNT(CompletionPercentage)) * 100, 0) as "Percent Complete"         
FROM    
    Contract AS con
    LEFT OUTER JOIN dbo.Contract_Service conSer ON con.ContractID = conSer.ContractID
    LEFT OUTER JOIN dbo.Standard sta WITH (NOLOCK) ON conSer.ServiceId = sta.StandardID
    LEFT OUTER JOIN dbo.StandardResponse standResp ON sta.StandardId = standResp.StandardId 
        AND conSer.StandardReportId = standResp.StandardReportId
GROUP BY 
    ContractID, Contract_ServiceID

Answer 6

(SUM(CompletionPercentage)/COUNT(CompletionPercentage)) * 100

If CompletionPercentage is an int field you will have trouble with integer math. Anytime you divide by an integer you need to multiply it by 1.0 to make sure it is considering the number as a decimal. Otherwise 49/100 would = 0.