[RS 2005]
Given the starting and stopping points (time values), how do I generate values between these points.
For example, if I have 08 (representing Hour) as a starting point and 12 as a stopping point.
From this I would like to generate a data sequence like 08, 09, 10, 11, and 12.
So how do I accomplish this? In SQL or in the RS?
The only thing I can think of is using a WHILE loop and a temporary table in SQL (not to keen on doing this).
//H?kan
Use custom code in the report to achieve this. Take the start and end points as input parameters as you might be already doing. Then pass them to the function in custom code and write VB code inside that function to generate all intermediate values. Then store that array in a global variable in the custom code itself which you could refer from anywhere in your report.
Shyam
|||I would either use a function or a stored procedure or a plain query for this.
CREATE FUNCTION dbo.NumberRange
(
@.Starting INT,
@.End INT
)
RETURNS @.Numbers TABLE
(
Number INT
)
AS
BEGIN
WHILE @.Starting <= @.End
BEGIN
INSERT INTO @.Numbers VALUES (@.Starting)
SET @.Starting = @.Starting + 1
END
RETURN
END
SELECT * FROM dbo.NumberRange(1,10)
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Hakan is not keen on using SQL and WHILE loops and tables.|||I know, thats why I write the function for him.|||
H?kan,
do you need the data to be included in the dataset so that it can be rendered on the report? If so, you will have to generate it in the SQL as mentioned by Jens. If all you need to do is refer to the intermediate values from other expressions then a global array is fine, but make sure you have null and bounds checks in your custom code before trying to access the array.
|||I guess the function also has the WHILE loop and tables.
Instead he can have a master table with just one column and values from 00 to 100 and just select the necessary numbers from that table between the given start and end.
Shyam
No comments:
Post a Comment