radu.gheorghiu (8/8/2016)

---

Here's the code for the clustered indexes:

create clustered index cls_ix_stores_for_budgets on #StoresBudgets (storeID, ProgramID);

create clustered index cls_ix_tmp_report_final on #temp_report_data (store_ID, newSourceID);

The other columns in the SELECT and GROUP BY are similar to the ones already posted in the query, except there are about 5 times more ISNULL(SUM()) functions.. (I have a feeling this might have been important information which I should have shared earlier).

The SELECT part of the actual query is more like:

SELECT

case when b.StoreDivision IS null and b.ProgramDescription = 'New' then 'Unassigned' else b.StoreDivision end as Division

, b.ProgramName

, b.Region

,case when b.AM IS null and b.ProgramName IS not null

then 'Unassigned'

else b.AM

end as AM

, rtrim(ltrim(b.Store)) Store

, ltrim(trf.Affiliate) Affiliate

, trd.Store_ID

, b.appliesToPeriod

, trd.AffiliateID

, isnull(trd.countLeadActual,0) as Actual

, isnull(sum(case when b.budgetType = 0 and b.budgetMonth between @start_date and @end_date then b.budgetValue else 0 end),0) as Budget

, isnull(sum(case when b.budgetType = 0 and b.budgetMonth between @start_date and @end_date and (trd.considerMe = -1 or b.StoreID < 0) then b.budgetValue else 0 end),0) as CleanBudget

, isnull(...)

, isnull(sum(case when x and y between @start and @end then end))

, isnull(sum(case when x and y between @start and @end and (trd.considerMe ... ) then end))

, isnull(...)

, isnull(sum(case when x and y between @start and @end then end))

for a total of 10 SUM operations, which indeed follow the same pattern as the two filled in above.