Nested SELECT statement

Question

SQL is not my forte, but I'm working on it - thanks for the answers.

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

I calculated the full percentage for each individual service for a particular contract Contract_ServiceID, but how can I return all the percentage of services for all contracts? Something like that:

Contract                                        

---

abc abc Company Service 2 100%
xyz Company                  

Here is what I still have:

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

It's me too:

Contract_serviceid Percent Complete

---

[id] 100%

EDIT: Tables did not appear in the message.

Answer 1

I'm not sure if I understand the problem, if the result is approved for service_contract, you can Service message

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 that you have all the columns that you select from the Contract table in the proposal group

Answer 2

You should be able to add the name of the company and its group, as well as the service identifier in your name and cut out the where clause ...

Perhaps so:

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 3

, , , , WHERE, , GROUP BY .

, , , .

Answer 4

where . INNER JOIN LEFT JOIN ( ) ISNULL . , StandardReportId, , .

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 5

serviceid, , where, .

dbo.Standard serviceid, dbo.Standard, .

I hope this makes sense ... My SQL gets rusty after switching to a data structure.

Answer 6

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

If CompletionPercentage is an int field, you will have problems with integer math. Each time you divide by an integer, you need to multiply it by 1.0 to make sure that it treats the number as a decimal. Otherwise, 49/100 will be = 0.