This may give you some ideas. I used a placeholder of <your_tally_table> for the tally table. Replace that with your own tally table name, either a physical tally table or cte tally table. Naturally also change the column name "tally" to whatever the column name in your tally table is.

SELECT

ClientNum,

DATEADD(DAY, (days.tally - 1), InDate_Midnight) AS HousingDate,

SUM(

CASE WHEN is_first_day = 1

THEN CASE WHEN is_last_day = 1

THEN DATEDIFF(MINUTE, hl.InDate, hl.OutDate)

ELSE DATEDIFF(MINUTE, hl.InDate, DATEADD(DAY, days.tally, InDate_Midnight))

END

WHEN is_last_day = 1 THEN DATEDIFF(MINUTE, DATEADD(DAY, (days.tally - 1), InDate_Midnight), hl.OutDate)

ELSE 1440 END

) AS MinutesInFacility

FROM #HsLog hl

CROSS APPLY (

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, hl.InDate), 0) AS InDate_Midnight

) AS assign_alias_names1

INNER JOIN <your_tally_table> days ON days.tally BETWEEN 1 AND DATEDIFF(DAY, hl.InDate, hl.OutDate) + 1

CROSS APPLY (

SELECT

CAST(CASE WHEN days.tally = 1 THEN 1 ELSE 0 END AS bit) AS is_first_day,

CAST(CASE WHEN DATEDIFF(DAY, DATEADD(DAY, (days.tally - 1), hl.InDate), hl.OutDate) = 0 THEN 1 ELSE 0 END AS bit) AS is_last_day

) AS assign_alias_names

GROUP BY ClientNum, DATEADD(DAY, (days.tally - 1), InDate_Midnight)

ORDER BY ClientNum, HousingDate