HitTheSky HitTheSky - 3 months ago 11
SQL Question

SQL Server 2012 - Create a calculated column showing percentage in query

I need to create a query for a report and capture what percentage of data has been filled in and display that as a calculated column.

So far I have wrote a query using joins to get me the required data:

Query:

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


Calculation

There are 16 columns in the above query, I need to check if each column has data or not, if there is data then assign a 1 or if it's null then assign a 0.

Once I've got the sum total for columns which have data, I then want to perform a calculation like so - *100 * Completed Columns / Total Columns* I then want to display this as a column for that query.

Essentially this data will be used in a report.

Any advice on how I can do this in SQL would be great, Thanks

Answer
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],