Author 
Topic 

landau66
Yak Posting Veteran
Austria
61 Posts 
Posted  01/17/2014 : 14:22:33

hi everyone!
I have a clustered index in a table. this column is of datatype date. how can i retrieve the following?:
select [date], valueColumn from myTable where [date] = '20000103' and ('20000103'+1) and ('20000103'+2)
My Goal ist to retrieve 3 values of valueColumn of 3 subsequent days, calcualte the average of this 3 values and insert this average in a third colum called [average3days].
thank you very much in advance landau 

James K
Flowing Fount of Yak Knowledge
3323 Posts 
Posted  01/17/2014 : 15:48:24

Like shown below. The thing to keep in mind is that, if you don't have data for next two days, what do you want to do? This will just take what is available and average it. So for the very last day, it will just be the data for that day.
Also, if there are gaps in dates, what do you need to do. Do you still want to average only the next 2 calendar days, or do you want to pick the next 3 values. The following picks the next 3 values.SELECT
y1.[date],
AVG(y2.valueColumn) AS average3days
FROM
YourTable y1
CROSS APPLY
(
SELECT TOP (3) y2.valueColumn FROM YourTable y2
WHERE y2.date >= y1.date
ORDER BY y2.date
) y2 


James K
Flowing Fount of Yak Knowledge
3323 Posts 
Posted  01/17/2014 : 15:50:02

BTW, if you want to update a column in your table with the computed data, join to that table again  like shown belowUPDATE x1 SET average3days = x2.average3days
FROM
YourTable x1
INNER JOIN
(
SELECT
y1.[date],
AVG(y2.valueColumn) AS average3days
FROM
YourTable y1
CROSS APPLY
(
SELECT TOP (3) y2.valueColumn FROM YourTable y2
WHERE y2.date >= y1.date
ORDER BY y2.date
) y2
) x2 ON x1.[date] = x2.[date]; 


landau66
Yak Posting Veteran
Austria
61 Posts 
Posted  01/17/2014 : 17:02:55

Yes, the Dates have gaps. In this Connection I have a question: Is there a way how to address the next or the previous date since the datefield has as well a clustered index? 


James K
Flowing Fount of Yak Knowledge
3323 Posts 
Posted  01/18/2014 : 12:07:48

If you want to get the next date even if there are gaps, use what I had posted earlier. That is,.....
CROSS APPLY
(
SELECT TOP (3) y2.valueColumn FROM YourTable y2
WHERE y2.date >= y1.date
ORDER BY y2.date
) y2
.... If you want to get the dates only if they are consecutive, add an additional condition like this: CROSS APPLY
(
SELECT TOP (3) y2.valueColumn FROM YourTable y2
WHERE y2.date >= y1.date
AND dateadd(dd,2,y1.date) <= y2.date
ORDER BY y2.date
) y2 


visakh16
Very Important crosS Applying yaK Herder
India
52249 Posts 
Posted  01/19/2014 : 03:19:46

If by any chance you're on SQL 2012, its much easier. just do
UPDATE t
SET average3days = [3DayAvg]
FROM (SELECT average3days,AVG(valueColumn * 1.0) OVER (ORDER BY [date] ROWS BETWEEN CURRENT ROW AND PRECEDING 2) AS [3DayAvg]
FROM Table
)t
 SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs



landau66
Yak Posting Veteran
Austria
61 Posts 
Posted  01/20/2014 : 14:47:42

hi! thank you so much. it was exactly what i needed. it took me some days to understand. by the way i am on an SQLServer 2012
many greetings landau 


visakh16
Very Important crosS Applying yaK Herder
India
52249 Posts 

landau66
Yak Posting Veteran
Austria
61 Posts 
Posted  02/04/2014 : 03:37:19

HI!
I have to come back to this Topic. I thought I understood it but still I have got some Problems with it. The solution of visakh suggests the following:
UPDATE t
SET average3days = [3DayAvg]
FROM (SELECT average3days,AVG(valueColumn * 1.0) OVER (ORDER BY [date] ROWS BETWEEN CURRENT ROW AND PRECEDING 2) AS [3DayAvg]
FROM Table
)t
Just the select Statement within the parethesis is a Problem. I think it is not in order. At least it does not function for me. The following solution works:
select valueColum, AVG(valueColum) over (order by [date] rows between 2 preceding and current row ) as [3DayAvg]
from DAX
Is it for some reason important to multiplie the valueColumn by 1 like visakh suggested it?
The way visakh updates the table (DAX) by this new computed data is not clear to me either. Maybe someone can tell me what kind of update this is or what field within SQL it touches.
I really try hard to understand it!
Thank you very much Landau 


visakh16
Very Important crosS Applying yaK Herder
India
52249 Posts 
Posted  02/04/2014 : 06:16:26

quote: Originally posted by landau66
HI!
I have to come back to this Topic. I thought I understood it but still I have got some Problems with it. The solution of visakh suggests the following:
UPDATE t
SET average3days = [3DayAvg]
FROM (SELECT average3days,AVG(valueColumn * 1.0) OVER (ORDER BY [date] ROWS BETWEEN CURRENT ROW AND PRECEDING 2) AS [3DayAvg]
FROM Table
)t
Just the select Statement within the parethesis is a Problem. I think it is not in order. At least it does not function for me. The following solution works:
select valueColum, AVG(valueColum) over (order by [date] rows between 2 preceding and current row ) as [3DayAvg]
from DAX
Is it for some reason important to multiplie the valueColumn by 1 like visakh suggested it?
The way visakh updates the table (DAX) by this new computed data is not clear to me either. Maybe someone can tell me what kind of update this is or what field within SQL it touches.
I really try hard to understand it!
Thank you very much Landau
this is new caluse introduced in SQL 2012 as an extension to PARTITION BY. I'm creating a dervied table using select statement and then using it to update the main table field with AVG value. The * 1.0 is to cast it to float datatype to get decimal part in result. you can ignore it if you want only integer part Also it would help if you can post exact query you tried and also error message you got.
 SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs



landau66
Yak Posting Veteran
Austria
61 Posts 
Posted  02/04/2014 : 09:56:24

Hi! Thank you very much for your immediate reply!
update dax2
set [MA3] = d.[ma] from
(select [close], AVG([close]*1.0) over (order by [date] rows between 2 preceding and current row ) as [ma]
from dax2)d
This Code updated all the rows of my table. Unfortunatly the value is in all rows the same. It has now in every row the value of the first row of [ma].
Thanks for the Explanation for the multiplication by one and that it is a derived table we are using here.
Greetings Landau 


landau66
Yak Posting Veteran
Austria
61 Posts 
Posted  02/04/2014 : 10:55:04

Hi all! After reading a lot I found a way to do it:
update dax2
set [MA3] = t.[ma] from
(select [date], [close], AVG([close]*1.0) over (order by [date] rows between 2 preceding and current row ) as [ma]
from dax2 ) t
where dax2.[date] = t.[date]
I hope it is a way which is acceptable for an experienced SQLProgrammer!
thank you very much visakh! you gave me valuable hints!!! Landau 


visakh16
Very Important crosS Applying yaK Herder
India
52249 Posts 
Posted  02/10/2014 : 08:59:11

quote: Originally posted by landau66
Hi all! After reading a lot I found a way to do it:
update dax2
set [MA3] = t.[ma] from
(select [MA3], AVG([close]*1.0) over (order by [date] rows between 2 preceding and current row ) as [ma]
from dax2 ) t
where dax2.[date] = t.[date]
I hope it is a way which is acceptable for an experienced SQLProgrammer!
thank you very much visakh! you gave me valuable hints!!! Landau
sorry that was actually a typo. you dont need the where condition at all just this is enough check and see the difference
update dax2 t
set [MA3] = t.[ma] from
(select [date], [close], AVG([close]*1.0) over (order by [date] rows between 2 preceding and current row ) as [ma]
from dax2 ) t
where dax2.[date] = t.[date]
 SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Edited by  visakh16 on 02/10/2014 08:59:45 



Topic 
