rolling products - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

rolling products

Author

Topic

zmithwani
Starting Member

1 Post

Posted - 2010-09-20 : 19:11:57

Taking the first three columns as input,I am looking to create the fourth column as an output:
Id Date Value Rolling product
1 10/02/2010 11 11*12*13*15
1 11/02/2010 NULL 12*13*15
1 12/02/2010 12 12*13*15
1 13/02/2010 NULL 13*15
1 14/02/2010 13 13*15
1 15/02/2010 15 15
2 20/05/2010 NULL 32*30*1
2 21/05/2010 NULL 32*30*1
2 22/05/2010 NULL 32*30*1
2 23/05/2010 32 32*30*1
2 24/05/2010 30 30*1
2 25/05/2010 NULL 1
2 26/05/2010 NULL 1

Any help would be great

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-09-20 : 23:35:34

what is the rule ?
how do you get the data in the 4th column ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-21 : 04:17:15

[code]DECLARE @Sample TABLE
(
ID INT,
DT DATE,
Value INT
)

SET DATEFORMAT DMY

INSERT @Sample
(

ID,
DT,
Value
)
VALUES (1, '10/02/2010', 11),
(1, '11/02/2010', NULL),
(1, '12/02/2010', 12),
(1, '13/02/2010', NULL),
(1, '14/02/2010', 13),
(1, '15/02/2010', 15),
(2, '20/05/2010', NULL),
(2, '21/05/2010', NULL),
(2, '22/05/2010', NULL),
(2, '23/05/2010', 32),
(2, '24/05/2010', 30),
(2, '25/05/2010', NULL),
(2, '26/05/2010', NULL)

SELECT s.ID,
s.DT,
s.Value,
f.Mult,
STUFF(g.Mult, 1, 1, '') AS Explained
FROM @Sample AS s
CROSS APPLY (
SELECT EXP(SUM(LOG(x.Value)))
FROM @Sample AS x
WHERE x.ID = s.ID
AND x.DT >= s.DT
) AS f(Mult)
CROSS APPLY (
SELECT '*' + ISNULL(CAST(x.Value AS VARCHAR(12)), '1')
FROM @Sample AS x
WHERE x.ID = s.ID
AND x.DT >= s.DT
FOR XML PATH('')
) AS g(Mult)[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources