SQL Server 2012 -创建一个显示查询百分比的计算列

Question

我需要为报告创建一个查询，并捕获已填写的数据的百分比，并将其显示为计算列。

到目前为止，我已经编写了一个使用连接来获取所需数据的查询:

查询:

SELECT
    SU.ID,
    MAX(SU.[GenderID]), 
    MAX(SU.[TelephoneNumber]), 
    MAX(SU.[MobileNumber]), 
    MAX(SU.[AddressID]), 
    MAX(SU.[WebSite]), 
    MAX(SU.[Narrative]), 
    MAX(SU.[IndividualTitleID]), 
    MAX(SU.[DateOfBirth]),

    MAX(SUED.SystemUserID) AS [SUEDID],
    MAX(SUE.ID) AS [SUEID],
    MAX(SUQ.ID) AS [SUQID],
    MAX(SUPB.ID) AS [SUPB],
    MAX(SUPQ.ID) AS [SUPQID],
    MAX(SUPI1.ID) AS [Skill],
    MAX(SUPI2.ID) AS [Achievement],
    MAX(SUPI3.ID) AS [Interest]
FROM 
    [Employed].[SystemUser] SU
LEFT OUTER JOIN 
    [Profile].[SystemUserEducation] SUED ON SU.ID = SUED.SystemUserID
LEFT OUTER JOIN 
    [Profile].[SystemUserQualification] SUQ ON SUED.ID = SUQ.SystemUserEducationID
LEFT OUTER JOIN 
    [Profile].[SystemUserEmployment] SUE ON SU.ID = SUE.SystemUserID
LEFT OUTER JOIN 
    [Profile].[SystemUserProfessionalBody] SUPB ON SU.ID = SUPB.SystemUserID
LEFT OUTER JOIN 
    [Profile].[SystemUserProfessionalQualification] SUPQ ON SU.ID = SUPQ.SystemUserID
LEFT OUTER JOIN 
    [Profile].[SystemUserProfileItem] SUPI1 ON SU.ID = SUPI1.SystemUserID  AND SUPI1.SystemUserProfileItemTypeID = 1 -- Skills
LEFT OUTER JOIN 
    [Profile].[SystemUserProfileItem] SUPI2 ON SU.ID = SUPI2.SystemUserID  AND SUPI2.SystemUserProfileItemTypeID = 2 -- Achievement 
LEFT OUTER JOIN 
    [Profile].[SystemUserProfileItem] SUPI3 ON SU.ID = SUPI3.SystemUserID  AND SUPI3.SystemUserProfileItemTypeID = 3 -- Interest
WHERE
    SU.ID = 4604
GROUP BY
    SU.ID 

计算

上面的查询中有16列，我需要检查每列是否有数据，如果有数据则分配1，或者如果它是空则分配0。

一旦我得到了有数据的列的总和，然后我想执行如下计算- *100 * Completed columns/total columns *然后我想将其显示为该查询的列。

基本上这些数据将在报告中使用。

任何关于如何在SQL中做到这一点的建议将是伟大的，谢谢

Answer 1

Select
SU.ID,
SU.[GenderID],
SU.[TelephoneNumber], 
SU.[MobileNumber], 
SU.[AddressID], 
SU.[WebSite],
SU.[Narrative], 
SU.[IndividualTitleID], 
SU.[DateOfBirth],

MAX(SUED.SystemUserID) AS [SUEDID],
MAX(SUE.ID) AS [SUEID],
MAX(SUQ.ID) AS [SUQID],
MAX(SUPB.ID) AS [SUPB],
MAX(SUPQ.ID) AS [SUPQID],
MAX(SUPI1.ID) AS [Skill],
MAX(SUPI2.ID) AS [Achievement],
MAX(SUPI3.ID) AS [Interest],
100 * (
    CASE WHEN MAX(SUED.SystemUserID) IS NULL THEN 0 ELSE 1 END
    + CASE WHEN MAX(SUE.ID) IS NULL THEN 0 ELSE 1 END
    + CASE WHEN MAX(SUQ.ID) IS NULL THEN 0 ELSE 1 END
    + CASE WHEN MAX(SUPB.ID) IS NULL THEN 0 ELSE 1 END
    + CASE WHEN MAX(SUPQ.ID) IS NULL THEN 0 ELSE 1 END
    + CASE WHEN MAX(SUPI1.ID) IS NULL THEN 0 ELSE 1 END
    + CASE WHEN MAX(SUPI2.ID) IS NULL THEN 0 ELSE 1 END
    + CASE WHEN MAX(SUPI3.ID) IS NULL THEN 0 ELSE 1 END
) / 8.0
FROM [Employed].[SystemUser] SU
LEFT OUTER JOIN [Profile].[SystemUserEducation] SUED ON SU.ID = SUED.SystemUserID
LEFT OUTER JOIN [Profile].[SystemUserQualification] SUQ ON SUED.ID = SUQ.SystemUserEducationID
LEFT OUTER JOIN [Profile].[SystemUserEmployment] SUE ON SU.ID = SUE.SystemUserID
LEFT OUTER JOIN [Profile].[SystemUserProfessionalBody] SUPB ON SU.ID = SUPB.SystemUserID
LEFT OUTER JOIN [Profile].[SystemUserProfessionalQualification] SUPQ ON SU.ID = SUPQ.SystemUserID
LEFT OUTER JOIN [Profile].[SystemUserProfileItem] SUPI1 ON SU.ID = SUPI1.SystemUserID  AND SUPI1.SystemUserProfileItemTypeID = 1 -- Skills
LEFT OUTER JOIN [Profile].[SystemUserProfileItem] SUPI2 ON SU.ID = SUPI2.SystemUserID  AND SUPI2.SystemUserProfileItemTypeID = 2 -- Achievement 
LEFT OUTER JOIN [Profile].[SystemUserProfileItem] SUPI3 ON SU.ID = SUPI3.SystemUserID  AND SUPI3.SystemUserProfileItemTypeID = 3 -- Interest
Where SU.ID = 4604
Group by
    SU.ID,
    SU.[GenderID],
    SU.[TelephoneNumber], 
    SU.[MobileNumber], 
    SU.[AddressID], 
    SU.[WebSite],
    SU.[Narrative], 
    SU.[IndividualTitleID], 
    SU.[DateOfBirth],