First, thanks to all who have taken an interest in this.

I've played around with this code. One observation - any form of UNIQUE constraint on the temp table results in the poor execution plan. Leaving the temp table as a heap gives the quickest performance. I've boiled the case down to essentials; here is the SQL code in two parts. The first part generates the test data; the second part runs the query. You'll need a tally table to help generate the test data.

-- Note: You will need a tally table to create the test data

-- Early data is monthly; later data is daily

-- Define a mapping table [DateMap] that maps early monthly data to daily dates

-- and later daily data to daily dates so data can be pulled for daily

-- dates over the entire date range

IF OBJECT_ID('dbo.DateMap') IS NOT NULL drop table dbo.DateMap

CREATE TABLE [dbo].[DateMap]

(

[adate] [smalldatetime] NOT NULL,

[calcdate] [smalldatetime] NULL)

INSERT INTO dbo.DateMap(adate)

SELECT CAST(N AS SMALLDATETIME)

FROM dbo.tally2 -- First use of Tally Table

WHERE N BETWEEN 29249 AND 42275

UPDATE dbo.DateMap

SET calcdate = adate

WHERE adate >= '31-aug-2008'

-- Define two CTEs

;WITH datecalc AS(

SELECT *, y= Year(adate), m= Month(adate), rn = ROW_NUMBER()OVER(ORDER BY adate)

FROM dbo.DateMap)

, monthends AS(

SELECT DC1.*

FROM datecalc DC1

JOIN datecalc DC2

ON DC1.rn = DC2.rn-1

WHERE DC1.m <> DC2.m)

UPDATE DM

SET calcDate = X.adate

FROM dbo.dateMap DM

OUTER APPLY( SELECT TOP 1 adate

FROM monthends M

WHERE DM.adate >= M.adate

ORDER BY M.adate DESC

) X

WHERE DM.calcDate IS NULL

ALTER TABLE dbo.DateMap ADD CONSTRAINT PK_DateMap PRIMARY KEY CLUSTERED(Adate ASC)

CREATE NONCLUSTERED INDEX IX_DateMap_calcdate_adate ON dbo.DateMap(CalcDate ASC, Adate ASC)

-- Create the test data

IF OBJECT_ID('dbo.TestCalcs') IS NOT NULL drop table dbo.TestCalcs

CREATE TABLE dbo.TestCalcs(adate SMALLDATETIME NOT NULL,

hid INT NOT NULL,

value FLOAT NULL,

PRIMARY KEY (adate ASC, hid ASC))

INSERT INTO dbo.testCalcs

SELECT X.adate,Y.hid, RAND(Y.hid)

FROM (select distinct calcdate as adate from dbo.dateMap) X

CROSS JOIN (select top 4000 N as hid from dbo.tally2 order by N) Y -- second use of tally table

CREATE NONCLUSTERED INDEX IX_testCalcs ON dbo.testCalcs(hid ASC, adate ASC)

-- Create the view that joins the test data to the mapping table

IF OBJECT_ID('dbo.TestCalcsView') IS NOT NULL drop view dbo.TestCalcsView

GO

CREATE VIEW dbo.testCalcsView AS

SELECT

DM.adate,

CL.hid,

CL.value

FROM

rc_temp.dbo.DateMap DM

JOIN

rc_temp.dbo.testCalcs CL

ON DM.CalcDate = CL.Adate

GO

Once the test data is generated, here is the test run code.

-- Test Run

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

-- Temp tables

IF OBJECT_ID('tempdb..#hidvals') IS NOT NULL DROP TABLE #hidvals

CREATE TABLE #hidvals(hid INT NOT NULL,

-- hid INT NOT NULL UNIQUE,

extel2code CHAR(2),

start SMALLDATETIME,

finish SMALLDATETIME,

value1 FLOAT,

value2 FLOAT)

-- CONSTRAINT hidvals_unique_hid UNIQUE (hid))

-- PRIMARY KEY CLUSTERED(hid))

--CREATE UNIQUE CLUSTERED INDEX IDX_hidvals ON #hidvals(hid)

INSERT INTO #hidvals(hid, start, finish)

SELECT hid ,'17-sep-2015', '18-sep-2015'

FROM testCalcs

WHERE adate = '18-sep-2015'

-- UPDATE STATISTICS #hidvals -- this makes no differen

-- Here's the statement with the slow/fast plan (depending)

UPDATE H

SET value1 = X1.[value], value2 = X2.[value]

FROM #hidvals H

LEFT JOIN rc_temp.dbo.testCalcsView X1

ON H.hid = X1.hid AND H.start = X1.adate

LEFT JOIN rc_temp.dbo.testCalcsView X2

ON H.hid = X2.hid AND H.finish = X2.adate