# Calculating value within a range of given data and at fixed intervals

• kirti

Valued Member

Points: 64

User input : Distance range, interval .

Need a t-sql which can navigate a set of data based upon the inpur range and returns the sum of value at equal intervals.

For ex, calculate value between 10 to 50 kn at every 5 km of interval.

so, first record should be  10-15 km --- sum(value)

15-20 km -- sum(value)

Also, its not necessary that in database the data is stored at 5 km interval only. It can be  1 km interval also.

• DesNorton

SSC-Insane

Points: 22440

kirti wrote:

User input : Distance range, interval . Need a t-sql which can navigate a set of data based upon the inpur range and returns the sum of value at equal intervals. For ex, calculate value between 10 to 50 kn at every 5 km of interval. so, first record should be  10-15 km --- sum(value) 15-20 km -- sum(value)   Also, its not necessary that in database the data is stored at 5 km interval only. It can be  1 km interval also.

Can you please provide us with usable data and the expected results

How to post data/code on a forum to get the best help.[/url]
Make sure that you include code in the appropriate IFCode tags.

• Andrey

Old Hand

Points: 366

such one?

`with input ([start],[end],[quant]) as (	select 10,50,5), [data] as (	select 		[start]		,[quant]		,[end]		,[quant] [ttl]	from input	union all	select 		[start]+[quant]		,[quant]		,[end]		,[ttl]+[quant]	from [data]	where [start]+[quant]<=[end]), kms as (	select 	convert(varchar(10),[start])+ '-'+ convert(varchar(10),lead([start],1,NULL) over (order by [start]))+' km' [range]	,[quant] [diff]	,ttl [total]from data )select * from kms where [range] is not null`
• drew.allen

SSC Guru

Points: 76413

An rCTE is probably one of the worst ways to build a set of intervals.  You are much better of using a tally table with `LEAD()` or `LAG()`.

`DECLARE @begin_unit int = 10,	@end_unit int = 50,	@interval int = 5;WITH Base AS(	SELECT *	FROM ( VALUES(0), (0), (0), (0) ) t(n)), Tally AS(	SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) - 1 AS n	FROM Base A	CROSS JOIN Base b)SELECT i.begin_unit, LEAD(i.begin_unit, 1, @end_unit) OVER(ORDER BY i.begin_unit) AS end_unitFROM Tally tCROSS APPLY (VALUES(@begin_unit + @interval * t.n) ) i(begin_unit)WHERE i.begin_unit < @end_unit`

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

How to post data/code on a forum to get the best help[/url].How to Post Performance Problems[/url]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply