Got it!!!!!!!!!

with MstrTbl as (Select N.Source, N.Sink, N.TimePoint, D.TPIntDate, D.TPHour, N.Delta From dbo.testnode N

Inner Join dbo.TestDate D

On N.TimePoint = D.TimePoint

)

Update dbo.TestNode

set TestNode.MovingAverage = CteMA.DeltaB

From (

Select A.Source, A.Sink, A.TimePoint as...

Sean,

The Select statement returns a calculated value, the 20 day moving average. I need to update the empty field MovingAverage with the results that are returned.

The detail for creating...

Here is my best attempt. But it fails miserably.

with MstrTbl as (Select N.Source, N.Sink, N.TimePoint, D.TPIntDate, D.TPHour, N.Delta From dbo.testnode N

Inner Join dbo.TestDate D

On N.TimePoint = D.TimePoint

)

Select A.Source, A.Sink, A.TimePoint...

Fair enough. Let me add a little complexity. Below is the CTE which calculates the 20 day moving average using a Date lookup table.

with MstrTbl as (Select N.Source, N.Sink, N.TimePoint,...

PROBLEM SOLVED!!!!

First, let me thank everyone for the interest and comments. It was a process for me to arrive at the final code.

Step 1:

I needed to visualize how this...

ChrisM,

I use a lookup Table, TestDate. I agree the article is very well written and informative. Still I was not able to adapt his strategy to utilize a lookup table....

ChrisM and DwainC,

Thank you for the reference. I had actually seen that before. I used it as best I could. But since it didn't use the lookup table, I was...

Dwain,

Thanks. I was presenting the most simplistic example I would need to process. I was hoping once this basic query was working I would be able to increase its complexity...

sqlGDBA,

I am looking for ways to improve the response time of the query. This query takes about 30 seconds. However, this is only one of several dozen similar queries. The...

Lowell,

Yes, the item numbers are char(15) variables. The item numbers follow the format XXX-XXX-XXXX. For simplicity, I dropped the “-“ and the 10th numeric digit because it is rarely present....

Jason,

Thanks for your reply. I do not understand "the index the engine is choosing is 6 in your list (IX_GMFPick_5)". I see several indexes and a Heap. How/why did you...

Jason,

I have executed Reindex on all the indexes. Currently, fragmentation is < .02 or = 0 on every index.

I included the fields suggested in the non clustered Row Index lookup...

I will keep that in mind.

thanks.

Thanks for the additional information.

Do you think if the file is found by the stored procedure that the SQL user would also have access to the file? I do...

Found part of the error.

The format file must have a blank line at the end. I had purposely made sure there was NOT a blank line. My previous experience...