¶ Basic Information
| Key | Value |
|---|---|
| Table | DS09 CC Log |
| Severity | MAJOR |
| Unique ID | 9090444 |
| Summary | Is the hours delta for this CC Log entry misaligned with what is in the CC Log detail table? |
| Error message | hours_delta <> SUM(DS10.hours_delta) (by CC_log_ID). |
¶ 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 "Log Hours Delta Misaligned With Log Detail Delta" is designed to ensure that the hours delta recorded in the DS09 CC Log aligns with the corresponding data in the DS10 CC Log detail table.
The test is checking if the hours delta for a specific CC Log entry is different from the sum of the hours delta in the CC Log detail table for the same CC Log entry. If there is a discrepancy between these two values, the test will flag an error.
The fields causing the issue are the 'hours_delta' field in the DS09 CC Log and the 'hours_delta' field in the DS10 CC Log detail table. The expected values for these fields should be the same for each corresponding CC Log entry.
If an error is flagged, it is likely due to an incorrect entry in either the DS09 CC Log or the DS10 CC Log detail table. This could be a result of a data entry error or a problem with the system that is recording the data. It is important to correct these discrepancies to ensure accurate tracking of hours in the project management data.
¶ Why do we check this?
This test is being performed on the 'DS09 CC Log' table to check for any discrepancies between the hours delta recorded in the CC Log entry and the sum of the hours delta in the CC Log detail table. The test is checking if 'hours_delta' is not equal to the sum of 'DS10.hours_delta' for each 'CC_log_ID'.
The importance of this check is to ensure that the total hours recorded in the log entry match with the detailed log entries. Any misalignment could lead to inaccurate tracking of hours, which could impact project management decisions, such as resource allocation and scheduling.
The severity of this test is marked as 'MAJOR', which means that while it may not prevent the data from being reviewed, any discrepancies found could potentially cause problems during data analysis. Therefore, it is crucial to address any issues found during this test to maintain the integrity and quality of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS09_CCLog_IsHoursDeltaMisalignedWithDS10] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CCLogDetail as (
SELECT CC_log_ID, SUM(hours_delta) HrsDelt
FROM DS10_CC_log_detail
WHERE upload_ID = @upload_ID
GROUP BY CC_log_ID
)
SELECT
L.*
FROM
DS09_CC_log L INNER JOIN CCLogDetail D ON L.CC_log_ID = D.CC_log_ID
WHERE
upload_ID = @upload_ID
AND L.hours_delta <> D.HrsDelt
)