| Key | Value |
|---|---|
| Table | DS22 Financial Calendar |
| Severity | CRITICAL |
| Unique ID | 9220001 |
| Summary | Does the project's financial calendar span to include all cost periods? |
| Error message | Min(DS22.period_date) > Min(DS03.period_date), OR MAX(DS22.period_date) < MAX(DS03.period_date). |
This DIQ check ensures the financial calendar (DS22) covers the full range of cost reporting periods in DS03.
The check fails when either:
This typically occurs due to:
The check compares the minimum and maximum DS22.period_date against the minimum and maximum DS03.period_date.
This test ensures the financial calendar defines a reporting period for every period that carries cost data. This validation is important because:
As a CRITICAL check, this must be resolved before data can be processed further. Without this alignment, cost data reported outside the calendar span would be excluded from period-based analysis.
CREATE FUNCTION [dbo].[fnDIQ_DS22_FinCal_DoesCalEncompassDS03Periods] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
/* Flags when DS22 period_dates do not span to include DS03 period_dates */
with FinCal as (
SELECT MIN(period_date) minDate, MAX(period_date) maxDate
FROM DS22_financial_calendar
WHERE upload_ID = @upload_id
), CostCal as (
SELECT MIN(period_date) minDate, MAX(period_date) maxDate
FROM DS03_cost
WHERE upload_ID = @upload_id
)
SELECT *
FROM DS22_financial_calendar cal
WHERE upload_ID = @upload_id
AND (
-- earliest calendar date, flagged only if it starts after DS03's first period
(cal.period_date = (SELECT minDate from FinCal) AND cal.period_date > (SELECT minDate from CostCal))
OR
-- latest calendar date, flagged only if it ends before DS03's last period
(cal.period_date = (SELECT maxDate from FinCal) AND cal.period_date < (SELECT maxDate from CostCal))
)
)