tsql - Creating a column to correctly group by effort -


i'm trying create column correctly label changes in effort estimator.

i've been able close using below code dense_rank() function, it's not quite i'm looking for. i'm having trouble identifying start , end points organize by. i've include current , desired output below.

current code:

select *, dense_rank() on (order estimator, effort) group #estimating_with_breakpoints order estimator, date, datetype 

current output:

job name                    datetype    date        effort  group hidden lakes apartments     start       3/8/2017    50      6 hidden lakes apartments     breakpoint  4/13/2017   50      6 hidden lakes apartments     finish      4/13/2017   0       4 dr. biggs joint institute   breakpoint  5/1/2017    0       4 dr. biggs joint institute   start       5/1/2017    33      5 bonita springs library      breakpoint  5/22/2017   33      5 north ft. myers library     breakpoint  5/22/2017   83      7 bonita springs library      start       5/22/2017   83      7 north ft. myers library     start       5/22/2017   133     9 dr. biggs joint institute   breakpoint  6/5/2017    133     9 dr. biggs joint institute   finish      6/5/2017    100     8 bonita springs library      breakpoint  6/19/2017   100     8 north ft. myers library     breakpoint  6/19/2017   50      6 bonita springs library      finish      6/19/2017   50      6 north ft. myers library     finish      6/19/2017   0       4 

desired output:

job name                    datetype    date        effort  group hidden lakes apartments     start       3/8/2017    50      1 hidden lakes apartments     breakpoint  4/13/2017   50      1 hidden lakes apartments     finish      4/13/2017   0       2 dr. biggs joint institute   breakpoint  5/1/2017    0       2 dr. biggs joint institute   start       5/1/2017    33      3 bonita springs library      breakpoint  5/22/2017   33      3 north ft. myers library     breakpoint  5/22/2017   83      3 bonita springs library      start       5/22/2017   83      3 north ft. myers library     start       5/22/2017   133     4 dr. biggs joint institute   breakpoint  6/5/2017    133     4 dr. biggs joint institute   finish      6/5/2017    100     5 bonita springs library      breakpoint  6/19/2017   100     5 north ft. myers library     breakpoint  6/19/2017   50      5 bonita springs library      finish      6/19/2017   50      5 north ft. myers library     finish      6/19/2017   0       5 


Comments

Popular posts from this blog

html - How to set bootstrap input responsive width? -

javascript - Highchart x and y axes data from json -

javascript - Get js console.log as python variable in QWebView pyqt -