generate performance-column

It seems to be a easy problem, but I haven't solved it.
I have the following table with two columns:
date, price
2005-1-1, 4.000
2005-1-2, 4.400
2005-1-3, 4.600
2005-1-4, 3800
2005-1-6, 4000
I'm looking for a sql-procedure which generates a new table with a new
price-series. This procedure should have two input variables:
Start_Date, Start_Figure. The result should be a table starting from
the Start_Date, and the price-series is reblanced by the Start_Figure:
for example: executing the prozedure with the values
Start_Date = 2005-1-2
Start_Figure = 100
should result the following table:
date, price
2005-1-2, 100
2005-1-3, 100,54 (=4600/4400*100)
2005-1-4, 86,36 (=3800/4600*100,54)
2005-1-6, 90,91 (=4000/3800*86,36)
Do you have an hints for solving this problem?Hi,manfred
I'd doing such reports on the client
CREATE TABLE #Test (dt DATETIME NOT NULL PRIMARY KEY,Price DECIMAL(18,3))
INSERT INTO #Test VALUES('20050101',4.000)
INSERT INTO #Test VALUES('20050102',4.400)
INSERT INTO #Test VALUES('20050103',4.600)
INSERT INTO #Test VALUES('20050104',3800)
INSERT INTO #Test VALUES('20050105',4000)
DECLARE @.dt DATETIME , @.pr DECIMAL(18,3)
SET @.dt='20050102'
SET @.pr=100
SELECT dt1,MAX(price1/price *100) AS data
FROM
(
SELECT #Test.*,t.dt AS dt1,t.price AS price1
FROM #Test JOIN #Test t ON T.dt>#Test.dt
WHERE #Test.dt>=@.dt
) AS Der
GROUP BY dt1
<manfred_d@.iname.com> wrote in message
news:1140435130.440166.300040@.g43g2000cwa.googlegroups.com...
> It seems to be a easy problem, but I haven't solved it.
> I have the following table with two columns:
> date, price
> 2005-1-1, 4.000
> 2005-1-2, 4.400
> 2005-1-3, 4.600
> 2005-1-4, 3800
> 2005-1-6, 4000
> I'm looking for a sql-procedure which generates a new table with a new
> price-series. This procedure should have two input variables:
> Start_Date, Start_Figure. The result should be a table starting from
> the Start_Date, and the price-series is reblanced by the Start_Figure:
> for example: executing the prozedure with the values
> Start_Date = 2005-1-2
> Start_Figure = 100
> should result the following table:
> date, price
> 2005-1-2, 100
> 2005-1-3, 100,54 (=4600/4400*100)
> 2005-1-4, 86,36 (=3800/4600*100,54)
> 2005-1-6, 90,91 (=4000/3800*86,36)
>
> Do you have an hints for solving this problem?
>|||thank you for your fast answer. i coded your interesting solution but
with the MAX(Price1/Price*100) function I don't get the correct data,
because instead of 90,91 in 2005-1-6 I get 105,26 ... ?|||No, please take a look at this example
CREATE TABLE #Test (dt DATETIME NOT NULL PRIMARY KEY,Price DECIMAL(18,3))
INSERT INTO #Test VALUES('20050101',4.000)
INSERT INTO #Test VALUES('20050102',4.400)
INSERT INTO #Test VALUES('20050103',4.600)
INSERT INTO #Test VALUES('20050104',3800)
INSERT INTO #Test VALUES('20050106',4000)
DECLARE @.dt DATETIME , @.pr DECIMAL(18,3)
SET @.dt='20050102'
SET @.pr=100
SELECT dt1,MAX(price1/price *100) AS data
FROM
(
SELECT #Test.*,t.dt AS dt1,t.price AS price1
FROM #Test JOIN #Test t ON T.dt>#Test.dt
WHERE #Test.dt>=@.dt
) AS Der
GROUP BY dt1
dt1 data
---
2005-01-03 00:00:00.000 104.5454545454545455
2005-01-04 00:00:00.000 86363.6363636363636364
2005-01-06 00:00:00.000 90909.0909090909090909\
<manfred_d@.iname.com> wrote in message
news:1140443079.644395.154610@.g43g2000cwa.googlegroups.com...
> thank you for your fast answer. i coded your interesting solution but
> with the MAX(Price1/Price*100) function I don't get the correct data,
> because instead of 90,91 in 2005-1-6 I get 105,26 ... ?
>|||many thanks for your help!!!