Thanks that certainly helped performance - down from 40 seconds to 1 second!. And now that I see the code, it makes sense, reducing the passes over the data.

Payments is actually a second table. Plus, I was getting a warning message "Warning: Null value is eliminated by an aggregate or other SET operation." So, for completeness, I'll include my final version of the view at the end of this message.

So, the query is 1) A rollup of claims across various filtering criteria (by caseid), 2) a rollup of payments (by caseid), 3) a join of those two rollups with a 3rd WriteOffs table (one record per caseid), plus a couple calculated fields.

It is handy for the end users to see the one-line rollup across caseid for the three tables, but be able to edit the WriteOff table from within that view. However, from SSMS, when I select "Edit Top 200 rows", it doesn't allow edits due to the structure of the join.

Is it possible to use INSTEAD OF triggers to do an update of the WriteOffs table? In pseudocode:

* User updates WriteOff (amount) or WriteOffReason fields in the view (I'll make the other columns readonly in the Access front-end)

* INSTEAD OF trigger runs to update the fields in the WriteOffs table

* Suppress any errors due to the view structure (normally it's not editable). Is this automatic behaviour in an INSTEAD OF trigger?

* Refresh the view (which will retrieve the saved data from WriteOffs). Since the view is now so fast, this will have acceptable performance. I can trigger the view refresh from the Access FE, or it may be automatic due to a timestamp field in the WriteOffs table. I can test this and see later once I write the FE.

I can probably write the trigger code (Google is my friend), so I'm just wanting to know if this is conceptually possible before I run down a rabbit hole, and if this is the best approach for this issue.

Here's the final view code:

DROP VIEW dbo.vMoney

GO

CREATE VIEW dbo.vMoney AS

WITH

Claims_Aggregates AS (

SELECT

caseid,

[total] = SUM(IsNull(benefitpaid,0)),

[unprocessed] = SUM(CASE WHEN unprocessed = 1 THEN IsNull(benefitpaid,0) ELSE 0 END),

[sentout] = SUM(CASE WHEN statusid = 2 THEN IsNull(benefitpaid,0) ELSE 0 END),

[notapplicable] = SUM(CASE WHEN applicable = 0 THEN IsNull(benefitpaid,0) ELSE 0 END),

[applicable] = SUM(CASE WHEN applicable = 1 THEN IsNull(benefitpaid,0) ELSE 0 END)

FROM dbo.fact_claims

GROUP BY caseid

),

Payments_Aggregates AS (

SELECT

caseid,

[paid] = SUM(IsNull(Total,0))

FROM dbo.fact_payments

GROUP BY caseid

)

SELECT

c.caseid,

c.casenumber,

c.analyst,

c.covno,

c.suffix,

c.lastname,

c.firstname,

Total,

Unprocessed,

SentOut,

NotApplicable,

Applicable,

Agreed,

WriteOff = CASE WHEN IsNull(Agreed,0) > 0 THEN Applicable-Agreed ELSE 0 END,

WriteOffReason,

Paid,

Outstanding = Coalesce(Agreed,Applicable) - Paid,

SettlementDate,

Notes

FROM vFact_Cases c

LEFT JOIN Claims_Aggregates t

ON t.caseid = c.caseid

LEFT JOIN Payments_Aggregates p

ON t.caseid = p.caseid

LEFT JOIN dbo.fact_writeoffs w

ON w.caseid = c.caseid

LEFT JOIN dbo.dim_WriteOffReason wr

ON wr.WriteOffReasonID = w.WriteOffReasonID

GO

SELECT * FROM dbo.vMoney