Post reply

Sequencing query

  • February 12, 2021 at 12:49 am

    #3844037

    Hello,

     

    I have a query regarding the best way to deal with a sequencing problem.

     

    This may be a little hard to explain. I have the following data (table and data script attached) where I need to regroup the depths columns by 1 foot increments and join the corresponding data which falls within that range. This needs to be reusable query for a report. I have tried sequencing, but I feel I may be barking up the wrong tree. I am pretty sure there must be a better way of doing this.

     

    For example:

    1

    as

    2

    3

    4

     

    Any help or suggestions would be greatly appreciated.

     

    Thanks!

  • February 12, 2021 at 12:54 am

    #3844038

    Sorry, could not attach SQL file. Attached it as a TXT

    Attachments:
    You must be logged in to view attached files.

  • February 12, 2021 at 3:54 am

    #3844070

    By "regroup"  does it mean "round and expand"?  Otherwise, how to handle the decimal precision?  To create 1 foot increments based on rounded ranges you could use a tally function.  Maybe you're looking for something like this

    select dt.PROJECT, dt.SITE_ID, v.dpth_from_rnd+fn.n-1 NEW_DEPTH_FROM,
           v.dpth_from_rnd+fn.n NEW_DEPTH_TO, dt.RMU
    from #DEPTH_TABLE dt
         cross apply (values (round(DEPTH_TO, 0), 
                              round(DEPTH_FROM, 0))) v(dpth_to_rnd, dpth_from_rnd)
         cross apply dbo.fnTally(1, (v.dpth_to_rnd-v.dpth_from_rnd)) fn;
    PROJECTSITE_IDNEW_DEPTH_FROMNEW_DEPTH_TORMU
    EXAMPLETEST_HOLE0.0001.00073.3333333333300
    EXAMPLETEST_HOLE1.0002.00073.3333333333300
    EXAMPLETEST_HOLE2.0003.00073.3333333333300
    EXAMPLETEST_HOLE3.0004.00068.0000000000000
    EXAMPLETEST_HOLE4.0005.00068.0000000000000
    EXAMPLETEST_HOLE5.0006.00068.0000000000000
    ...

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • February 12, 2021 at 4:07 am

    #3844071

    Thanks Steve! That is exactly what I am after. I just could not remember how to tackle this, but the tally approach worked brilliantly!

    Very much appreciated sir!

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

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