1

I am amazed this question doesn't seem to have been asked before, but if so, my pretty exhaustive searches have not discovered it.

I have a simple ring buffer of temperatures in a column from which I want calculate the average value.

The sql table looks like this (column names shown in top row) ...

uid Temp
1   70
2   70
3   70
4   70
5   70
6   69
7   69
8   69
9   69
10  69

Every time I obtain a new temperature, I want to shift the "Temp" column up one row before writing the newest value into slot '10' so I can get the average value of the column data.

There must be a simple way to do this, that I haven't figured out yet. :-(

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
burky39
  • 65
  • 1
  • 9

2 Answers2

2

You can do the shift like this:

delete from buffer where id = 1;

update buffer set id = id - 1;

insert into buffer(id, temp) values(10, @temp);

However, I would keep all the temperature values and use another method to get the 10-unit moving average.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the help. The temps are recorded every 10 minutes running from 00:00-24:00 hours and then circling back around to 00:00 to start a new day, but at the 00:00 mark the entire 'old' day is moved one column to the right (history column) and then the (left) 'new' column cleared in readiness for the new day's temperatures. The new/old day temps are then charted for comparison. SO long story short. Rather than chase numbers around I thought it easier to just keep the last 10 readings in a stable cyclic array. Does this change your "another method' thoughts? – burky39 Dec 17 '14 at 18:01
  • Tried the suggested code but its a no-go. I simplified the problem for this discussion. There are actually many columns in the full Table. The above method deletes the first row (and thus other needed data). I cannot delete or insert rows. I must just manipulate the 'Temp' column data ONLY. [:-)} – burky39 Dec 17 '14 at 18:25
  • In other words, I need to UPDATE all Temp's from uid= 1 to 9 where temp(n)=temp(n+1), Then I will update temp(10) with the new data. – burky39 Dec 17 '14 at 18:53
0

Got it !!

-- shift column values up one row.
UPDATE [myTable] 
SET [temp] = (SELECT [temp]
                  FROM [myTable] AS s2
                  WHERE s2.[uid] = ([myTable].[uid] +1))
WHERE [uid] <= 9;

This moves just the specific cells up one row, as desired.

It was more complicated than I thought it would be, but this seems to work exactly as I needed. [:-)}

burky39
  • 65
  • 1
  • 9