| Key | Value |
|---|---|
| Table | DS22 Financial Calendar |
| Severity | CRITICAL |
| Unique ID | 9220002 |
| Summary | Does the DS22 financial calendar contain a period_date that is not in DS03 cost? |
| Error message | DS22_financial_calendar.period_date missing in DS03_cost.period_date list. |
This DIQ check ensures that, across the range of cost reporting periods, the financial calendar (DS22) and the cost dataset (DS03) line up period-for-period with no extra calendar periods wedged in between.
The check fails when:
DS22 is allowed to carry period_dates before the first DS03 period and after the last DS03 period — only the overlapping span is required to match one-to-one.
This typically occurs due to:
The check compares each DS22.period_date inside the DS03 span against the full list of DS03.period_date values.
This test ensures the financial calendar and cost dataset overlap one-to-one, with no calendar period interleaved between two cost periods. This validation is important because:
As a CRITICAL check, this must be resolved before data can be processed further. Without this alignment, an extra calendar period inside the cost span would break the one-to-one mapping between calendar and cost periods.
CREATE FUNCTION [dbo].[fnDIQ_DS22_FinCal_ExtraPeriodInDS03Span] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
/* Flags DS22 period_dates inside the DS03 span that have no matching DS03 period_date */
with CostCal as (
SELECT MIN(period_date) minDate, MAX(period_date) maxDate
FROM DS03_cost
WHERE upload_id = @upload_id
)
SELECT *
FROM DS22_financial_calendar
WHERE upload_id = @upload_id
AND period_date <= (SELECT maxDate from CostCal)
AND period_date >= (SELECT minDate from CostCal)
AND period_date NOT IN (
SELECT period_date
FROM DS03_cost
WHERE upload_id = @upload_id
)
)