¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 1060252 |
| Summary | Is this role name duplicated across roles? |
| Error message | Resource_name repeats across distinct resource_ids. |
¶ What causes this error?
The following text was generated by an AI tool and hasn't been reviewed for accuracy by a human! It might be useful, but it also might have errors. Are you a human? You can help by reviewing it for accuracy! Edit it as needed then remove this message.
The Data Integrity and Quality (DIQ) check titled "Duplicate Role Name" is designed to ensure that each role name in the DS06 Resources table is unique. This check is important because having duplicate role names can lead to confusion and errors in data analysis and reporting.
The DIQ check works by comparing the 'role_name' field across different 'role_id' fields within the same 'schedule_type'. If the same role name is found for different role IDs within the same schedule type, the DIQ check will flag this as an error.
The error message "Resource_name repeats across distinct resource_ids" indicates that there are one or more role names that are being used for more than one role ID. This is likely caused by data entry errors where the same role name was mistakenly assigned to different role IDs.
To resolve this issue, you should review the role names and IDs in the DS06 Resources table. Each role name should be associated with only one role ID within the same schedule type. If you find any role names that are associated with more than one role ID, you should correct these so that each role name is unique.
¶ Why do we check this?
This test is being performed to check for any instances of duplicate role names in the 'DS06 Resources' table of the EVMS construction project management data. The purpose of this check is to ensure that each role name is unique and corresponds to a distinct resource ID. This is important because having duplicate role names can lead to confusion and errors in data analysis, as it may not be clear which role is being referred to in each case.
The severity of this issue is classified as an MINOR. This means that while it is not a critical error that would prevent the data from being reviewed, it is still a potential issue that could cause minor problems or indicate that the data does not fully adhere to best practices. Therefore, it is recommended to address this issue to maintain the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsRoleNameDuplicated] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Flags as (
SELECT BLName1.role_id, BLName1.role_name, BLName1.schedule_type
FROM
(SELECT role_id, role_name, schedule_type FROM DS06_schedule_resources WHERE upload_ID = @upload_ID AND schedule_type = 'BL' GROUP BY schedule_type, role_id, role_name) BLName1,
(SELECT role_id, role_name, schedule_type FROM DS06_schedule_resources WHERE upload_ID = @upload_ID AND schedule_type = 'BL' GROUP BY schedule_type, role_id, role_name) BLName2
WHERE
BLName1.role_name = BLName2.role_name
AND BLName1.role_id <> BLName2.role_id
UNION
SELECT FCName1.role_id, FCName1.role_name, FCName1.schedule_type
FROM
(SELECT role_id, role_name, schedule_type FROM DS06_schedule_resources WHERE upload_ID = @upload_ID AND schedule_type = 'FC' GROUP BY schedule_type, role_id, role_name) FCName1,
(SELECT role_id, role_name, schedule_type FROM DS06_schedule_resources WHERE upload_ID = @upload_ID AND schedule_type = 'FC' GROUP BY schedule_type, role_id, role_name) FCName2
WHERE
FCName1.role_name = FCName2.role_name
AND FCName1.role_id <> FCName2.role_id
)
SELECT
R.*
FROM
DS06_schedule_resources R
INNER JOIN Flags F ON R.schedule_type = F.schedule_type
AND R.role_id = F.role_id
AND R.role_name = F.role_name
WHERE
upload_id = @upload_ID
)