Give IDs to variation on gaps and islands problem











up vote
1
down vote

favorite












This dataset contains one ordered timestamp column (A) along with a pair of marker columns (B + C) that represent the start and end of a 'block', what I'm looking to produce is (D).



I've had a hard time of explaining this problem to colleagues, but essentially I need a way of giving an ID to these blocks of varying row count but note that on row 8 as an example a block can sometimes only occupy one row.



|           A         |  B  |  C  |  D  |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |









share|improve this question




















  • 2




    I don't understand what you want, could u please add "expected output" to your question
    – Eray Balkanli
    Nov 11 at 4:57










  • Hint for you:- LAG()
    – Ajan Balakumaran
    Nov 11 at 5:10










  • Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
    – Rob Ruizuki
    Nov 11 at 6:03















up vote
1
down vote

favorite












This dataset contains one ordered timestamp column (A) along with a pair of marker columns (B + C) that represent the start and end of a 'block', what I'm looking to produce is (D).



I've had a hard time of explaining this problem to colleagues, but essentially I need a way of giving an ID to these blocks of varying row count but note that on row 8 as an example a block can sometimes only occupy one row.



|           A         |  B  |  C  |  D  |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |









share|improve this question




















  • 2




    I don't understand what you want, could u please add "expected output" to your question
    – Eray Balkanli
    Nov 11 at 4:57










  • Hint for you:- LAG()
    – Ajan Balakumaran
    Nov 11 at 5:10










  • Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
    – Rob Ruizuki
    Nov 11 at 6:03













up vote
1
down vote

favorite









up vote
1
down vote

favorite











This dataset contains one ordered timestamp column (A) along with a pair of marker columns (B + C) that represent the start and end of a 'block', what I'm looking to produce is (D).



I've had a hard time of explaining this problem to colleagues, but essentially I need a way of giving an ID to these blocks of varying row count but note that on row 8 as an example a block can sometimes only occupy one row.



|           A         |  B  |  C  |  D  |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |









share|improve this question















This dataset contains one ordered timestamp column (A) along with a pair of marker columns (B + C) that represent the start and end of a 'block', what I'm looking to produce is (D).



I've had a hard time of explaining this problem to colleagues, but essentially I need a way of giving an ID to these blocks of varying row count but note that on row 8 as an example a block can sometimes only occupy one row.



|           A         |  B  |  C  |  D  |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |






sql azure ssms partition gaps-and-islands






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 11 at 8:40









Mohammad Mohabbati

428312




428312










asked Nov 11 at 4:54









Rob Ruizuki

153




153








  • 2




    I don't understand what you want, could u please add "expected output" to your question
    – Eray Balkanli
    Nov 11 at 4:57










  • Hint for you:- LAG()
    – Ajan Balakumaran
    Nov 11 at 5:10










  • Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
    – Rob Ruizuki
    Nov 11 at 6:03














  • 2




    I don't understand what you want, could u please add "expected output" to your question
    – Eray Balkanli
    Nov 11 at 4:57










  • Hint for you:- LAG()
    – Ajan Balakumaran
    Nov 11 at 5:10










  • Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
    – Rob Ruizuki
    Nov 11 at 6:03








2




2




I don't understand what you want, could u please add "expected output" to your question
– Eray Balkanli
Nov 11 at 4:57




I don't understand what you want, could u please add "expected output" to your question
– Eray Balkanli
Nov 11 at 4:57












Hint for you:- LAG()
– Ajan Balakumaran
Nov 11 at 5:10




Hint for you:- LAG()
– Ajan Balakumaran
Nov 11 at 5:10












Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
– Rob Ruizuki
Nov 11 at 6:03




Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
– Rob Ruizuki
Nov 11 at 6:03












2 Answers
2






active

oldest

votes

















up vote
0
down vote



accepted










You can try to use LAG window function in subquery then use SUM window function with condition aggregate function.



SELECT A,B,C,SUM(CASE WHEN preC = 1 THEN 1 ELSE 0 END) OVER(ORDER BY A,preC) +1  'D'
FROM (
SELECT *,
LAG(C,1,C) OVER(ORDER BY A) preC
FROM T
) t1


sqlfiddle



Result



|           A         |  B  |  C  |  D  |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |





share|improve this answer





















  • Thanks very much this works fine
    – Rob Ruizuki
    Nov 12 at 13:12










  • No problem glad to help you can accept this question if that help you.
    – D-Shih
    Nov 12 at 14:30


















up vote
0
down vote













I don't see what C has to do with the problem. This is just a cumulative sum on B:



select a, b, c,
sum(b) over (order by a) as d
from t;





share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53245960%2fgive-ids-to-variation-on-gaps-and-islands-problem%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    0
    down vote



    accepted










    You can try to use LAG window function in subquery then use SUM window function with condition aggregate function.



    SELECT A,B,C,SUM(CASE WHEN preC = 1 THEN 1 ELSE 0 END) OVER(ORDER BY A,preC) +1  'D'
    FROM (
    SELECT *,
    LAG(C,1,C) OVER(ORDER BY A) preC
    FROM T
    ) t1


    sqlfiddle



    Result



    |           A         |  B  |  C  |  D  |
    -----------------------------------------
    | 06/10/2018 13:17:40 | 1 | 0 | 1 |
    | 06/10/2018 13:17:56 | 0 | 0 | 1 |
    | 06/10/2018 13:18:08 | 0 | 1 | 1 |
    | 06/10/2018 13:18:21 | 1 | 0 | 2 |
    | 06/10/2018 13:18:26 | 0 | 0 | 2 |
    | 06/10/2018 13:18:26 | 0 | 0 | 2 |
    | 06/10/2018 13:18:28 | 0 | 1 | 2 |
    | 06/10/2018 13:18:28 | 1 | 1 | 3 |
    | 06/10/2018 13:18:31 | 1 | 0 | 4 |
    | 06/10/2018 19:49:26 | 0 | 0 | 4 |
    | 06/10/2018 19:50:24 | 0 | 1 | 4 |





    share|improve this answer





















    • Thanks very much this works fine
      – Rob Ruizuki
      Nov 12 at 13:12










    • No problem glad to help you can accept this question if that help you.
      – D-Shih
      Nov 12 at 14:30















    up vote
    0
    down vote



    accepted










    You can try to use LAG window function in subquery then use SUM window function with condition aggregate function.



    SELECT A,B,C,SUM(CASE WHEN preC = 1 THEN 1 ELSE 0 END) OVER(ORDER BY A,preC) +1  'D'
    FROM (
    SELECT *,
    LAG(C,1,C) OVER(ORDER BY A) preC
    FROM T
    ) t1


    sqlfiddle



    Result



    |           A         |  B  |  C  |  D  |
    -----------------------------------------
    | 06/10/2018 13:17:40 | 1 | 0 | 1 |
    | 06/10/2018 13:17:56 | 0 | 0 | 1 |
    | 06/10/2018 13:18:08 | 0 | 1 | 1 |
    | 06/10/2018 13:18:21 | 1 | 0 | 2 |
    | 06/10/2018 13:18:26 | 0 | 0 | 2 |
    | 06/10/2018 13:18:26 | 0 | 0 | 2 |
    | 06/10/2018 13:18:28 | 0 | 1 | 2 |
    | 06/10/2018 13:18:28 | 1 | 1 | 3 |
    | 06/10/2018 13:18:31 | 1 | 0 | 4 |
    | 06/10/2018 19:49:26 | 0 | 0 | 4 |
    | 06/10/2018 19:50:24 | 0 | 1 | 4 |





    share|improve this answer





















    • Thanks very much this works fine
      – Rob Ruizuki
      Nov 12 at 13:12










    • No problem glad to help you can accept this question if that help you.
      – D-Shih
      Nov 12 at 14:30













    up vote
    0
    down vote



    accepted







    up vote
    0
    down vote



    accepted






    You can try to use LAG window function in subquery then use SUM window function with condition aggregate function.



    SELECT A,B,C,SUM(CASE WHEN preC = 1 THEN 1 ELSE 0 END) OVER(ORDER BY A,preC) +1  'D'
    FROM (
    SELECT *,
    LAG(C,1,C) OVER(ORDER BY A) preC
    FROM T
    ) t1


    sqlfiddle



    Result



    |           A         |  B  |  C  |  D  |
    -----------------------------------------
    | 06/10/2018 13:17:40 | 1 | 0 | 1 |
    | 06/10/2018 13:17:56 | 0 | 0 | 1 |
    | 06/10/2018 13:18:08 | 0 | 1 | 1 |
    | 06/10/2018 13:18:21 | 1 | 0 | 2 |
    | 06/10/2018 13:18:26 | 0 | 0 | 2 |
    | 06/10/2018 13:18:26 | 0 | 0 | 2 |
    | 06/10/2018 13:18:28 | 0 | 1 | 2 |
    | 06/10/2018 13:18:28 | 1 | 1 | 3 |
    | 06/10/2018 13:18:31 | 1 | 0 | 4 |
    | 06/10/2018 19:49:26 | 0 | 0 | 4 |
    | 06/10/2018 19:50:24 | 0 | 1 | 4 |





    share|improve this answer












    You can try to use LAG window function in subquery then use SUM window function with condition aggregate function.



    SELECT A,B,C,SUM(CASE WHEN preC = 1 THEN 1 ELSE 0 END) OVER(ORDER BY A,preC) +1  'D'
    FROM (
    SELECT *,
    LAG(C,1,C) OVER(ORDER BY A) preC
    FROM T
    ) t1


    sqlfiddle



    Result



    |           A         |  B  |  C  |  D  |
    -----------------------------------------
    | 06/10/2018 13:17:40 | 1 | 0 | 1 |
    | 06/10/2018 13:17:56 | 0 | 0 | 1 |
    | 06/10/2018 13:18:08 | 0 | 1 | 1 |
    | 06/10/2018 13:18:21 | 1 | 0 | 2 |
    | 06/10/2018 13:18:26 | 0 | 0 | 2 |
    | 06/10/2018 13:18:26 | 0 | 0 | 2 |
    | 06/10/2018 13:18:28 | 0 | 1 | 2 |
    | 06/10/2018 13:18:28 | 1 | 1 | 3 |
    | 06/10/2018 13:18:31 | 1 | 0 | 4 |
    | 06/10/2018 19:49:26 | 0 | 0 | 4 |
    | 06/10/2018 19:50:24 | 0 | 1 | 4 |






    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Nov 11 at 8:51









    D-Shih

    24.3k61431




    24.3k61431












    • Thanks very much this works fine
      – Rob Ruizuki
      Nov 12 at 13:12










    • No problem glad to help you can accept this question if that help you.
      – D-Shih
      Nov 12 at 14:30


















    • Thanks very much this works fine
      – Rob Ruizuki
      Nov 12 at 13:12










    • No problem glad to help you can accept this question if that help you.
      – D-Shih
      Nov 12 at 14:30
















    Thanks very much this works fine
    – Rob Ruizuki
    Nov 12 at 13:12




    Thanks very much this works fine
    – Rob Ruizuki
    Nov 12 at 13:12












    No problem glad to help you can accept this question if that help you.
    – D-Shih
    Nov 12 at 14:30




    No problem glad to help you can accept this question if that help you.
    – D-Shih
    Nov 12 at 14:30












    up vote
    0
    down vote













    I don't see what C has to do with the problem. This is just a cumulative sum on B:



    select a, b, c,
    sum(b) over (order by a) as d
    from t;





    share|improve this answer

























      up vote
      0
      down vote













      I don't see what C has to do with the problem. This is just a cumulative sum on B:



      select a, b, c,
      sum(b) over (order by a) as d
      from t;





      share|improve this answer























        up vote
        0
        down vote










        up vote
        0
        down vote









        I don't see what C has to do with the problem. This is just a cumulative sum on B:



        select a, b, c,
        sum(b) over (order by a) as d
        from t;





        share|improve this answer












        I don't see what C has to do with the problem. This is just a cumulative sum on B:



        select a, b, c,
        sum(b) over (order by a) as d
        from t;






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 11 at 12:18









        Gordon Linoff

        746k33285390




        746k33285390






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53245960%2fgive-ids-to-variation-on-gaps-and-islands-problem%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Bressuire

            Vorschmack

            Quarantine