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_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


    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