Why not just use a cartesian of possible combinations of start and end day (42 excluding same start and end day) together with the number of days and then do a simple lookup


SELECT
DATEDIFF(day, COALESCE(date0,dateStart),date1),
DATEDIFF(day, COALESCE(date1,date0,dateStart),date2),
DATEDIFF(day, COALESCE(date2,date1,date0,dateStart),date3),
DATEDIFF(day, COALESCE(date3,date2,date1,date0,dateStart),date4),
DATEDIFF(day, COALESCE(date4,date3,date2,date1,date0,dateStart),date5),
DATEDIFF(day, COALESCE(date5,date4,date3,date2,date1,date0,dateStart),date6),
DATEDIFF(day, COALESCE(date6,date5,date4,date3,date2,date1,date0,dateStart),date7)
FROM temp


Obscure variation

REPLACE(LTRIM(REPLACE(REPLACE(REPLACE([value],'000.',''),'.000',''),'0',' ')),' ','0')+'H'


SELECT a.UserAccounts,a.AccountType,a.BillingAccountKey,b.BillingCode,b.[Rank]
FROM #UserAccount a
CROSS APPLY (SELECT TOP (1) t.BillingCode,r.[Rank]
FROM #BillingTransactions t
JOIN #BillingCodeRank r ON r.BillingCode = t.BillingCode
WHERE...

Check the output row count.
IIRC Reporting Services Excel renderer has a worksheet row limit (circa 65K) and will not render if the number of rows exceeds that limit