• To get maximum performance you want a merge join between a and b and you want an index on a so you can use a stream aggregation for the group by.

    This can be achieved by using the following indexes:

    create nonclustered index IX_a on a

    (policy, form, date, location_date, location_pin, state)

    include (premium)

    create nonclustered index IX_b on b

    (policy, form, date, location_date, location_pin, state)

    include (premprcnt)