SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to insert result of Calculation in new Column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

landau66
Yak Posting Veteran

Austria
61 Posts

Posted - 01/17/2014 :  14:22:33  Show Profile  Reply with Quote
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] = '2000-01-03' and
('2000-01-03'+1) and
('2000-01-03'+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

3332 Posts

Posted - 01/17/2014 :  15:48:24  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 01/17/2014 :  15:50:02  Show Profile  Reply with Quote
BTW, if you want to update a column in your table with the computed data, join to that table again - like shown below
UPDATE 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];
Go to Top of Page

landau66
Yak Posting Veteran

Austria
61 Posts

Posted - 01/17/2014 :  17:02:55  Show Profile  Reply with Quote
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 date-field has as well a clustered index?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 01/18/2014 :  12:07:48  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/19/2014 :  03:19:46  Show Profile  Reply with Quote
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
Go to Top of Page

landau66
Yak Posting Veteran

Austria
61 Posts

Posted - 01/20/2014 :  14:47:42  Show Profile  Reply with Quote
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 SQL-Server 2012

many greetings
landau
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/21/2014 :  05:32:28  Show Profile  Reply with Quote
quote:
Originally posted by landau66

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 SQL-Server 2012

many greetings
landau


cool
see what all you can do with new analytical functions in 2012

http://visakhm.blogspot.com/2012/07/extended-analytic-and-window-functions.html

http://visakhm.blogspot.com/2012/07/extended-analytic-and-window-functions_07.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

landau66
Yak Posting Veteran

Austria
61 Posts

Posted - 02/04/2014 :  03:37:19  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/04/2014 :  06:16:26  Show Profile  Reply with Quote
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
Go to Top of Page

landau66
Yak Posting Veteran

Austria
61 Posts

Posted - 02/04/2014 :  09:56:24  Show Profile  Reply with Quote
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
Go to Top of Page

landau66
Yak Posting Veteran

Austria
61 Posts

Posted - 02/04/2014 :  10:55:04  Show Profile  Reply with Quote
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 SQL-Programmer!

thank you very much visakh! you gave me valuable hints!!!
Landau
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/10/2014 :  08:59:11  Show Profile  Reply with Quote
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 SQL-Programmer!

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000