¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 1060247 |
| Summary | Is this resource name duplicated across resources? |
| 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 Resource Name" is designed to identify any instances where the same resource name is used for different resource IDs in the DS06 Resources table. This check is important to ensure that each resource ID is associated with a unique resource name, which is crucial for accurate project management and resource allocation.
The error message "Resource_name repeats across distinct resource_ids" indicates that there are instances where the same resource name is associated with more than one resource ID. This could be due to a data entry error or a system glitch that has resulted in the duplication of resource names.
The fields causing this issue are 'resource_name' and 'resource_ID'. The expected values for these fields are that each 'resource_name' should be unique to a single 'resource_ID'. If the same 'resource_name' is found for different 'resource_IDs', it will trigger this DIQ check.
To resolve this issue, you should review the DS06 Resources table and correct any instances where the same resource name is associated with multiple resource IDs. This will ensure the integrity and quality of your project management data.
¶ Why do we check this?
This test is being performed to check for duplicate resource names in the 'DS06 Resources' table. The purpose of this check is to ensure that each resource name is unique and corresponds to a distinct resource ID. This is important because having duplicate resource names can lead to confusion and errors in data analysis, as it may not be clear which resource is being referred to. The severity of this issue is classified as an MINOR, which means it's not critical but it's still important to address. It might cause minor problems during analysis or indicate that the data doesn't follow all best practices. Ensuring unique resource names helps maintain data integrity and quality by preventing potential misinterpretations or misuses of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsResourceNameDuplicated] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Resources as (
SELECT resource_ID, resource_name, schedule_type, ISNULL(subproject_ID,'') SubP
FROM DS06_schedule_resources
WHERE upload_ID = @upload_ID
), BL as (
SELECT * FROM Resources WHERE schedule_type = 'BL' GROUP BY schedule_type, resource_ID, resource_name, SubP
), FC as (
SELECT * FROM Resources WHERE schedule_type = 'FC' GROUP BY schedule_type, resource_ID, resource_name, SubP
), Flags as (
SELECT BLName1.*
FROM BL BLName1 INNER JOIN BL BLName2 ON BLName1.resource_name = BLName2.resource_name AND BLName1.SubP = BLName2.SubP AND BLName1.resource_ID <> BLName2.resource_ID
UNION
SELECT FCName1.*
FROM FC FCName1 INNER JOIN FC FCName2 ON FCName1.resource_name = FCName2.resource_name AND FCName1.SubP = FCName2.SubP AND FCName1.resource_ID <> FCName2.resource_ID
)
SELECT R.*
FROM DS06_schedule_resources R INNER JOIN Flags F ON R.schedule_type = F.schedule_type
AND R.resource_ID = F.resource_ID
AND R.resource_name = F.resource_name
AND ISNULL(R.subproject_ID,'') = F.SubP
WHERE upload_id = @upload_ID
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of 'subproject_ID' field, as well as for improved readability. |