Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

interpolation script needs to loop through data values

2 posts in General Discussion Last posting was on 2011-10-11 21:52:08.0Z
roberto Posted on 2011-10-11 15:47:50.0Z
Sender: 19e6.4e9464c7.1804289383@sybase.com
From: roberto
Newsgroups: sybase.public.ase.general
Subject: interpolation script needs to loop through data values
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e946526.1a07.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Oct 2011 08:47:50 -0700
X-Trace: forums-1-dub 1318348070 10.22.241.41 (11 Oct 2011 08:47:50 -0700)
X-Original-Trace: 11 Oct 2011 08:47:50 -0700, 10.22.241.41
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30583
Article PK: 73475

Hello All,

As will soon be obvious to you, I am new to SQL programming.
I am writing a script to compare two data sets; each set
consists of a date and a value. The dates on the two sets do
not match, so I need to create a 3rd set of (interpolated)
values on the equivalent dates.

For the interpolated data set, I need to get the list of
dates from Set1 and run the interpolation script on Set2
(repeat/loop for each of the Set1 values). How do I setup
this "looping" method in SQL?

Thanks for your help!

My current script follows:


DECLARE @current_offset int

-- temporary solution: manually define the current_offset;
***instead, want to loop through all values defined in
data1***
SELECT @current_offset = 10
--SELECT @current_offset = 13
--SELECT @current_offset = 16
--SELECT @current_offset = 19
--SELECT @current_offset = 22


/* want to loop through all offset day values in data1
-- generate list of offset days we want to populate for new
curve
SELECT date_offset FROM data1

-- returns list of date_offset values defined in data1
-- 10,13,16,19,22,25,28,31

-- FOR EACH date_offset returned above, DO the rest of
script
-- i.e. repeat the following script 8 times
*/


-- returns two values to interpolate against, for given
date_offset being tested
SELECT TOP 2 * INTO #tmpInterpolationData
FROM
(
SELECT * FROM data2 a
) b
ORDER BY ABS (b.date_offset - @current_offset)


-- calculate interpolated data2 value for date_offset
defined by data1 (>> data3)
DECLARE @offset1 int
DECLARE @offset2 int
DECLARE @dataValue1 float
DECLARE @dataValue2 float

DECLARE @interpolatedValue float

SELECT @offset1 = MIN(curve_offset_day) FROM
#tmpInterpolationData
SELECT @offset2 = MAX(curve_offset_day) FROM
#tmpInterpolationData
SELECT @dataValue1 = data_value FROM #tmpInterpolationData
WHERE date_offset = @offset1
SELECT @dataValue2 = data_value FROM #tmpInterpolationData
WHERE date_offset = @offset2

-- piecewise linear interpolation
SELECT @interpolatedValue = @dataValue2 - (@dataValue2 -
@dataValue1) * (@offset2 - @current_offset_day) / (@offset2
- @offset1)


"Mark A. Parsons" <iron_horse Posted on 2011-10-11 21:52:08.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: interpolation script needs to loop through data values
References: <4e946526.1a07.1681692777@sybase.com>
In-Reply-To: <4e946526.1a07.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e94ba88$1@forums-1-dub>
Date: 11 Oct 2011 14:52:08 -0700
X-Trace: forums-1-dub 1318369928 10.22.241.152 (11 Oct 2011 14:52:08 -0700)
X-Original-Trace: 11 Oct 2011 14:52:08 -0700, vip152.sybase.com
Lines: 121
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30584
Article PK: 73471

From a strictly technical point of view ... use a cursor, eg:

==========================
declare offset_cur cursor
for
select date_offset
from data1
order by date_offset
for read only
go

declare @offset int -- datatype?

open offset_cur

-- get first offset to work with

fetch offset_cur into @offset

while @@sqlstatus = 0
begin
-- run your code based on current offset
-- stored in @offset

... your @offset specific code ...

-- get next offset to work with

fetch offset_cur into @offset
end

close offset_cur
go

deallocate cursor offset_cur
go
==========================


There may be a way to do what you want without a cursor ... but that would require a bit more analysis of what you're
trying to do ...

On 10/11/2011 11:47, roberto wrote:
> Hello All,
>
> As will soon be obvious to you, I am new to SQL programming.
> I am writing a script to compare two data sets; each set
> consists of a date and a value. The dates on the two sets do
> not match, so I need to create a 3rd set of (interpolated)
> values on the equivalent dates.
>
> For the interpolated data set, I need to get the list of
> dates from Set1 and run the interpolation script on Set2
> (repeat/loop for each of the Set1 values). How do I setup
> this "looping" method in SQL?
>
> Thanks for your help!
>
> My current script follows:
>
>
> DECLARE @current_offset int
>
> -- temporary solution: manually define the current_offset;
> ***instead, want to loop through all values defined in
> data1***
> SELECT @current_offset = 10
> --SELECT @current_offset = 13
> --SELECT @current_offset = 16
> --SELECT @current_offset = 19
> --SELECT @current_offset = 22
>
>
> /* want to loop through all offset day values in data1
> -- generate list of offset days we want to populate for new
> curve
> SELECT date_offset FROM data1
>
> -- returns list of date_offset values defined in data1
> -- 10,13,16,19,22,25,28,31
>
> -- FOR EACH date_offset returned above, DO the rest of
> script
> -- i.e. repeat the following script 8 times
> */
>
>
> -- returns two values to interpolate against, for given
> date_offset being tested
> SELECT TOP 2 * INTO #tmpInterpolationData
> FROM
> (
> SELECT * FROM data2 a
> ) b
> ORDER BY ABS (b.date_offset - @current_offset)
>
>
> -- calculate interpolated data2 value for date_offset
> defined by data1 (>> data3)
> DECLARE @offset1 int
> DECLARE @offset2 int
> DECLARE @dataValue1 float
> DECLARE @dataValue2 float
>
> DECLARE @interpolatedValue float
>
> SELECT @offset1 = MIN(curve_offset_day) FROM
> #tmpInterpolationData
> SELECT @offset2 = MAX(curve_offset_day) FROM
> #tmpInterpolationData
> SELECT @dataValue1 = data_value FROM #tmpInterpolationData
> WHERE date_offset = @offset1
> SELECT @dataValue2 = data_value FROM #tmpInterpolationData
> WHERE date_offset = @offset2
>
> -- piecewise linear interpolation
> SELECT @interpolatedValue = @dataValue2 - (@dataValue2 -
> @dataValue1) * (@offset2 - @current_offset_day) / (@offset2
> - @offset1)