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  |





sql azure ssms partition gaps-and-islands







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  |





sql azure ssms partition gaps-and-islands







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  |





sql azure ssms partition gaps-and-islands







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




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

            Image
            Subprefecture and commune in Nouvelle-Aquitaine, France Bressuire Subprefecture and commune Chateau de Bressuire and the Eglise Notre-Dame Coat of arms Location of Bressuire Bressuire Show map of France Bressuire Show map of Nouvelle-Aquitaine Coordinates: 46°50′27″N 0°29′14″W  /  46.8408°N 0.4872°W  / 46.8408; -0.4872 Coordinates: 46°50′27″N 0°29′14″W  /  46.8408°N 0.4872°W  / 46.8408; -0.4872 Country France Region Nouvelle-Aquitaine Department Deux-Sèvres Arrondissement Bressuire Canton Bressuire Government  • Mayor .mw-parser-output .nobold{font-weight:normal} (2014–20) Jean Michel Bernier Area 1 180.59 km 2 (69.73 sq mi) Population (2014) 2 19,300  • Density 110/km 2 (280/sq mi) Time zone UTC+01:00 (CET)  • Summer (DST) UTC+02:00 (CEST) INSEE/Postal code 79049 /79300 Elevation 98–236 m (322–774 ft) (avg. 173 m or 568 ft) 1 French Land Register data, which exclude...
            Read more

            Vorschmack

            Image
            Vorschmack Ukrainian Jewish-style vorschmack served on rye bread Course Hors d'oeuvre Region or state Eastern Europe Associated national cuisine Ashkenazi Jewish, Finnish, German, Ukrainian, Polish, Russian Main ingredients Ground meat and/or fish Cookbook: Vorschmack   Media: Vorschmack Vorschmack or forshmak (Yiddish: פֿאָרשמאַק ‎, from archaic German Vorschmack , "foretaste" [1] or "appetizer" [2] ) is an originally East European dish made of salty minced fish or meat. Different variants of this dish are especially common in Ashkenazi Jewish and Finnish cuisine. Some varieties are also known in Russian and Polish cuisine. Contents 1 In Jewish cuisine 2 In Russian cuisine 3 In Polish cuisine 4 In Finnish cuisine 5 See also 6 References In Jewish cuisine According to Gil Marks, the German name points to the possible Germanic origin of this dish. [1] William Pokhlyobkin descr...
            Read more

            Quarantine

            Image
            For other uses, see Quarantine (disambiguation). Signal flag "Lima" called the "Yellow Jack" which when flown in harbor means ship is under quarantine. A simple yellow flag (also called the "Yellow Jack") had historically been used to signal quarantine (it stands for Q among signal flags), but now indicates the opposite, as a signal of a ship free of disease that requests boarding and inspection. A quarantine is used to separate and restrict the movement of people; it is 'a restraint upon the activities or communication of persons or the transport of goods designed to prevent the spread of disease or pests', for a certain period of time. [1] This is often used in connection to disease and illness, such as those who may possibly have been exposed to a communicable disease. [2] The term is often erroneously used to mean medical isolation, which is "to separate ill persons who have a communicable disease from those who are healthy...
            Read more