We've observed inconsistencies in client states within HIFIS, for example, some clients are marked as 'Active' despite not receiving services in over 90 days, while others are marked as 'Inactive' even though they still have open case management goals.

In HIFIS, client state is updated by the [dbo].[sp_checkClientState] stored procedure. This procedure can be executed for an individual client or for all clients in the database. When run for all clients, it processes them in sequential batches of 5,000 (e.g., ClientID 1–5,000; 5,001–10,000; etc.).

However, gaps in the ClientID sequence can cause the procedure to skip clients. For example, our database contains a gap between ClientIDs 25,000 and 35,000. As a result, the client state for all clients with a ClientID above 25,000 may not be updated.

Explanation

When processing all clients, the [dbo].[sp_checkClientState] procedure relies on the [dbo].[fn_checkClientStateData] function to retrieve client state data for each batch. If no data is returned for a given batch (e.g., when a gap exists in the ClientID range) the [dbo].[sp_checkClientState] procedure exits early. This prevents the remaining client batches from being evaluated, leading to incomplete or inaccurate client state data.