3 obvious problems from the plans:

1) Key lookup on 240k recordset (Object3). Certainly wrong choice of the clustered index.
2) 4.3 mil rows scanned from Object4.Index4 only to...

As I said - it was only 1 randomly chosen piece of the query, picked only because I stopped scrolling through the query to take a sip of coffee.

Every other...

From somewhere inside the query:

  FROM [dbo].[FctCoverageLengthDetail]

WHERE CASE

     WHEN([CoverageLengthMonthNumber]=13 or[CoverageLengthMonthNumber] =25 or[CoverageLengthMonthNumber] =37 or[CoverageLengthMonthNumber] =49

     or[CoverageLengthMonthNumber] =61 or[CoverageLengthMonthNumber]...

No, it won't miss them.

Because of LEFT join all the treatments which are not in the base set will be simply omitted.

See if it makes sense:

create table #PatientTreatment (
patientID int,
TreatmentID int
)

insert into #PatientTreatment
select 1, 111
union
select 1, 222
union

Age Calculation

If returned sets are always filtered by PVID you may use this trick:

CustomData = ROW_NUMBER() OVER (PARTITION BY PVID ORDER BY (SELECT NULL))

The query returns every single record from the "Allocations" table joined to every single relevant record in lookup tables.
Since you've got no record filtering, it may be only table...

I'd assume that "birthdays next month" is not only kind of queries which might be performed in the company.
I'd expect also "birthdays next week", "birthdays on Chritmas holidays", etc.

Ok, from what you're saying I can see several key issues.

1. It's all in a single table . Big mistake. Huge.

Manufacturer references, vendor catalogue data, sales qty and pricing, reps...

Are you trying to tell that the query

INSERT INTO #ChangeProducts ()

SELECT ...

FROM Joined Tables

With no WHERE filter

Performs better and applies less intensive locking than

SELECT ...

FROM Joined...