May 15, 2019 at 10:11 am
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.
May 15, 2019 at 10:33 am
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
May 15, 2019 at 3:39 pm
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
May 15, 2019 at 3:55 pm
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_unit
FROM Tally t
CROSS 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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply