From HICC: "Our developers have created a bug item for this issue. We will be working on rewriting the sp_UpdateClientHousingStatus. This work will take some time, but we’re aiming to make improvements as we deploy new releases of HIFIS."

This was reported fixed in 60.3 but it's definitely still happening.

On January 22 I created a large family (clients 30 through 38) and when I look at the HIFIS_ClientHistoryChanges table I can see that each of these clients has 3 duplicate records indicating they are Unknown on Jan 22. 

From @Ryan Booker ​:

I ran your query and it returned 37,573 records. Some of these clients have 268 duplicates.

I used the clientID from one of these duplicates and executed the sp_UpdateClientHousingStatus stored procedure with the @isDaily variable set to 1

EXEC dbo.sp_UpdateClientHousingStatus 12345, 1

This added another duplicate 'Unknown' housing status record to the HIFIS_ClientHistoryChanges table.

Each subsequent execution of the sp_UpdateClientHousingStatus stored procedure using the same values resulted in an additional duplicate 'Unknown' housing status record being added to the HIFIS_ClientHistoryChanges table for the specified client. This also occurs when the clientID is set to NULL and @isDaily is set to 1

EXEC sp_UpdateClientHousingStatus NULL, 1

Looking at the sp_UpdateClientHousingStatus, there is code near the start that will delete records from the HIFIS_ClientHistoryChanges table.

--JLD on 2020/12/17 Delete also when @IsDaily = 1DELETE FROM [dbo].[HIFIS_ClientHistoryChanges] WHERE 	FieldTypeID = @fieldClientHousingStatusIDAND 	(ClientID = @ClientID OR @ClientID IS NULL)AND (	@IsDaily = 0		OR 	(@IsDaily = 1 	AND CAST([dbo].[HIFIS_ClientHistoryChanges].DateOfChange AS DATE) = CAST(@todayDate AS DATE)	))

When @isDaily is set to 1, this code should delete all housing status records from the HIFIS_ClientHistoryChanges table for the client that were created 'today'.

@isDaily = 1 AND CAST([dbo].[HIFIS_ClientHistoryChanges].DateOfChange AS DATE) = CAST(@todayDate AS DATE)

However, since the duplicate 'Unknown' housing status records are all using the client's created date for their DateofChange, they are not deleted.

The INSERT statement at the end of the procedure then adds a duplicate 'Unknown' housing status record to the HIFIS_ClientHistoryChanges table when the client (or all clients if no clientID is provided) does not have any housing or shelter history in the vw_HousingandShelterHistory view.

INSERT INTO [dbo].[HIFIS_ClientHistoryChanges](	[ClientID]	,[FieldTypeID]	,[Value]	,[DateOfChange]	,[CreatedDate]	,[LastUpdatedDate]	,[LastUpdatedBy]	,[CreatedBy])SELECT 	CL.ClientID	,@fieldClientHousingStatusID AS [FieldTypeID]	,@HousingStatusUnknown AS [Value]	,IIF(ISNULL(CL.CreatedDate, @todayDate) <= @todayDate, ISNULL(CL.CreatedDate  ,@todayDate), @todayDate) AS DateOfChange	,@TimeStamp AS CreatedDate	,@TimeStamp AS LastUpdatedDate	,@UserID AS LastUpdatedBy	,@UserID AS CreatedByFROM	[dbo].[HIFIS_Clients] CLWHERE 	(CL.ClientID = @ClientID OR @ClientID IS NULL)AND 	CL.ClientID NOT IN (SELECT DISTINCT cKnown.ClientID FROM vw_HousingAndShelterHistory AS cKnown)

This issue appears to have started in HIFIS 4.0.60.

I checked our prod HIFIS (version 4.0.59.7.1) for the same client and the client did not have any 'Unknown' housing status records in the HIFIS_ClientHistoryChanges table.

Although this was reported fixed in 4.0.60.2, it is still present (or present again) in 4.0.60.5.1 

Reported fixed in 4.0.60.2, BUT there is still a related outstanding issue related to duplicated Unknown Housing Status. 13465: Duplicated Unknown housing status on client created date