Home Forums SQL Server 2008 T-SQL (SS2K8) pulling records from a table between date ranges in another table RE: pulling records from a table between date ranges in another table

  • August 17, 2017 at 8:03 am

    #1955655

    serg-52 - Tuesday, March 18, 2014 2:08 AM

    If i got it right ...Suppose there's date range lookup table with N datescreate table #RangeLookup (dt date)declare @strtDate date = '20140101'declare @step int = 30declare @N int = 20insert #RangeLookup select top (@N) dateadd(dd, @step*(row_number() over (order by (select null))-1), @strtDate)from sys.all_columnsStep is not supposed to be always fixed as it is in the above example.Now one can build a view or cte kind of;with c1 as (select dt, rn= row_number() over (order by dt)from #RangeLookup),RangeLookup2 as ( -- N-1 intervals with tagsselect dStart = a.dt , dEnd = dateadd (dd, -1, b.dt), tag = datediff (dd, (select top 1 dt from #RangeLookup order by dt), b.dt)from c1 ajoin c1 b on a.rn= b.rn-1)select * from RangeLookup2Then just compare myDate in question against interval to get the tag.RgdsSerg

    a simpler and i think faster aproach


    DECLARE @strtDate    DATE = '20140101'
    DECLARE @step        INT = 30
    DECLARE @N            INT = 20

    ;WITH RANGES AS (
        SELECT    1                                      AS id
                , @strtDate                             AS dt_ini
                , DATEADD(DAY, @step - 1, @strtDate)    AS dt_end
                , @step                                    AS mlst
        UNION ALL
        SELECT    R.id + 1                                AS id
                , DATEADD(DAY, @step, R.dt_ini)            AS dt_ini
                , DATEADD(DAY, @step, R.dt_end)            AS dt_end
                , R.mlst + @step                        AS mlst
        FROM    RANGES R
        WHERE    R.id < @step -- if you want @step to be more than 100 you need to add "option (maxrecursion 0)" at the end of statment
    )
    SELECT    *
    FROM    RANGES