Query Result for sequential range

  • I have been fighting this and wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.

    The data looks like:

    ABCD-001869202

    ABCD-001869203

    ABCD-001869204

    ABCD-001869205

    ABCD-001869209

    ABCD-0018692010

    ABCD-0018692011

    ABCD-001869309

    ABCD-001869310

    ABCD-001869311

    ABCD-001869312

    ABCD-001869313

    ABCD-001869314

    Desired out put:

    From To

    ABCD-001869202 ABCD-001869205

    ABCD-001869209 ABCD-0018692011

    ABCD-001869309 ABCD-001869314

    I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:

    SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher

    INTO #tempVoucher

    FROM LEDGERJOURNALTRANS

    where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'

    WITH T

    AS (SELECT DENSE_RANK() OVER (ORDER BY voucher) - voucher AS Grp,

    voucher

    FROM #tempVoucher)

    -- WHERE status = 0)

    SELECT MIN(voucher) AS [From],

    MAX(voucher) AS [To]

    FROM T

    GROUP BY Grp

    ORDER BY MIN(voucher)

    Thanks in advance for the assistance.

  • The first issue based on your sample data: 209 usually isn't followed by 2010 but rather 309.

    The "classic" gaps'n'islands will fail here since there's no consecutive pattern.

    A solution could be to modify your sample data (at least for the query by using a cte) to return consecutive numbers where you'll expect them.

    This would mean to turn 209 into 2009 and all 14-digit values into 15-digit values, respectively.

    After that, remove the first 11 character (ABCD-001869) and apply the gaps'n'islands solution (you might want to look at Jeffs article on how to do it efficiently).



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks. I will try that and as for the 2 sets of numbers 2010 and 2011, those are typos. Should be 210 and 211.

  • Ok, I am able to gather the missing gaps with the following:

    SELECT distinct voucher vouchers,right(voucher, charindex('-', voucher) + 4) voucher

    INTO #tempVoucher

    FROM LEDGERJOURNALTRANS

    where TRANSDATE between '10/1/2013' and '07/31/2014' and VOUCHER like 'APIN%'

    select start, stop from (

    select m.voucher + 1 as start,

    (select min(voucher) - 1 from #tempVoucher as x where x.voucher > m.voucher) as stop

    from #tempVoucher as m

    left outer join #tempVoucher as r on m.voucher = r.voucher - 1

    where r.voucher is null

    ) as x

    where stop is not null

    order by start

    However, though this gives me only the missing gaps, I am wanting to do the same thing, but instead of missing gaps, I want to see the non missing gaps. I have used CTE and tried various ways and I am beginning to wonder if it is possible.

    Thanks

  • Here's a slightly different approach:

    create table #x (col1 int)

    insert into #x

    values

    (202),(203),(204),(205),(209),(210),(211),(309),

    (310),(311),(312),(313),(314);

    WITH cte as

    (

    SELECT col1, ROW_NUMBER() OVER (ORDER BY col1) as id, col1 - ROW_NUMBER() OVER (ORDER BY col1) as grp

    FROM #x

    )

    SELECT MIN(col1) as [start], MAX(col1) as [end]

    FROM cte

    GROUP BY grp

    ORDER BY grp

    DROP TABLE #x



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks so much. That worked. I was getting to the point that I could not see the trees through the forest.

  • Just a quick note about duplicates.

    If the possibility exists for duplicate values then you would use DENSE_RANK instead of ROW_NUMBER. In the examples below I took Lutz' code and added a duplicate value (312) so you can see what I am talking about.

    create table #x (col1 int)

    insert into #x

    values

    (202),(203),(204),(205),(209),(210),(211),(309),

    (310),(311),(312),(312),(313),(314);

    WITH cte as

    (

    SELECT col1, ROW_NUMBER() OVER (ORDER BY col1) as id, col1 - ROW_NUMBER() OVER (ORDER BY col1) as grp

    FROM #x

    )

    SELECT MIN(col1) as [start], MAX(col1) as [end]

    FROM cte

    GROUP BY grp

    ORDER BY grp

    DROP TABLE #x;

    GO

    create table #x (col1 int)

    insert into #x

    values

    (202),(203),(204),(205),(209),(210),(211),(309),

    (310),(311),(312),(312),(313),(314);

    WITH cte as

    (

    SELECT col1, DENSE_RANK() OVER (ORDER BY col1) as id, col1 - DENSE_RANK() OVER (ORDER BY col1) as grp

    FROM #x

    )

    SELECT MIN(col1) as [start], MAX(col1) as [end]

    FROM cte

    GROUP BY grp

    ORDER BY grp

    DROP TABLE #x

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 7 posts - 1 through 6 (of 6 total)

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