| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | CRITICAL |
| Unique ID | 9030331 |
| Summary | Does every DS03 cost period_date have a matching DS22 financial calendar period_date? |
| Error message | DS03_cost.period_date missing in DS22_financial_calendar.period_date list. |
This DIQ check ensures every cost reporting period in DS03 is defined in the financial calendar (DS22).
The check fails when:
This typically occurs due to:
The check compares each DS03.period_date against the full list of DS22.period_date values.
This test ensures every cost period is anchored to a recognized financial calendar period. This validation is important because:
As a CRITICAL check, this must be resolved before data can be processed further. Without this alignment, cost reported against an undefined period would not map to a calendar period during analysis.
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_PeriodMissingFromDS22] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
/* Flags DS03 period_dates that have no matching DS22 period_date */
SELECT *
FROM DS03_cost
WHERE upload_ID = @upload_id
AND period_date NOT IN (
SELECT period_date
FROM DS22_financial_calendar
WHERE upload_ID = @upload_id
)
)