Sergiy,

Thank you for that example. Since I am programming in VB.Net that would be an "easy" solution. However, I am using this as an opportunity in increase my SQL knowledge...

Below you will find the CTE again and data. The data is preceeded by the Create table statement and the Alter statements for creating the indexes.

CTE Update

with MstrTbl as (Select...

Gail,

I do not understand. If there is no activity other than this query and the TempDb grows so dramatically in size, what would the cause be?

Also, I have attached the...

Car problems. Will post in 3-4 hours. Sorry.

Let’s begin with the actual query that is giving me fits.

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

Inner Join dbo.EcoDate D

On N.TimePoint = D.TimePoint

Where D.TimePoint...

Jason,

I haven’t read/studied that article yet. Thank you. There was another article which is referenced in a previous post I made when I was trying to build the query which...

I completely agree with you. Whatever is being created in the TempDb is not being reused between when the query runs the second, third, fourth, etc times. I do not...

Bill,

It worked like a charm.

Thanks,

pat

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...