• Here is another suggestion on how to establish the grouping, the rest should be straight forward, select the last desired value from each group.

    😎

    CREATE TABLE #TEMP (T_ID INT IDENTITY(1,1),UserName varchar(20),Drink varchar(20),Value int,CreatedDate date)

    INSERT INTO #TEMP VALUES ('ABC','Coke',100,'2014-01-12') --Just one pepsi record in this loop would be displayed

    INSERT INTO #TEMP VALUES ('ABC','Fanta',50,'2014-01-13')

    INSERT INTO #TEMP VALUES ('ABC','Pepsi',50,'2014-01-14')

    INSERT INTO #TEMP VALUES ('ABC','HardDrink',75,'2014-01-18')

    INSERT INTO #TEMP VALUES ('ABC','Fanta',36,'2014-01-20') -- No Pepsi in this loop so no pepsi record would be displayed

    INSERT INTO #TEMP VALUES ('ABC','XXX',45,'2014-01-28')

    INSERT INTO #TEMP VALUES ('ABC','XXX',75,'2014-02-04')

    INSERT INTO #TEMP VALUES ('ABC','XXX',96,'2014-02-06')

    INSERT INTO #TEMP VALUES ('ABC','HardDrink',12,'2014-02-10')

    INSERT INTO #TEMP VALUES ('ABC','Fanta',93,'2014-02-22') -- 2 Pepsi in this loop but the one with the value 510 would be shown because it is latest

    INSERT INTO #TEMP VALUES ('ABC','XXX',101,'2014-02-22')

    INSERT INTO #TEMP VALUES ('ABC','Pepsi',150,'2014-02-23')

    INSERT INTO #TEMP VALUES ('ABC','Pepsi',510,'2014-02-24')

    INSERT INTO #TEMP VALUES ('ABC','HardDrink',175,'2014-02-25')

    INSERT INTO #TEMP VALUES ('ABC','Fanta',136,'2014-02-26') --Since there is no end, Hence Either Fanta or Coke which ever latest record would be displayed. In this case Fanta

    INSERT INTO #TEMP VALUES ('ABC','XXX',145,'2014-02-27')

    INSERT INTO #TEMP VALUES ('ABC','XXX',175,'2014-02-28')

    INSERT INTO #TEMP VALUES ('ABC','XXX',196,'2014-03-01')

    INSERT INTO #TEMP VALUES ('ABC','XXX',112,'2014-03-02')

    INSERT INTO #TEMP VALUES ('ABC','XXX',193,'2014-03-03')

    ;WITH BASE_DATA AS

    (

    SELECT

    T_ID

    ,T.UserName

    ,T.Drink

    ,T.Value

    ,T.CreatedDate

    FROM #TEMP T

    )

    SELECT

    BD.T_ID

    ,ISNULL(X.T_ID,(SELECT MAX(T_ID) FROM BASE_DATA)) AS GROUP_ID

    ,BD.UserName

    ,BD.Drink

    ,BD.Value

    ,BD.CreatedDate

    FROM BASE_DATA BD

    OUTER APPLY

    (

    SELECT MIN(T_ID) AS T_ID FROM BASE_DATA B2

    WHERE B2.Drink = 'HardDrink'

    AND BD.T_ID < B2.T_ID

    ) AS X

    DROP TABLE #TEMP;

    Results

    T_ID GROUP_ID UserName Drink Value CreatedDate

    ----------- ----------- -------------------- -------------------- ----------- -----------

    1 4 ABC Coke 100 2014-01-12

    2 4 ABC Fanta 50 2014-01-13

    3 4 ABC Pepsi 50 2014-01-14

    4 9 ABC HardDrink 75 2014-01-18

    5 9 ABC Fanta 36 2014-01-20

    6 9 ABC XXX 45 2014-01-28

    7 9 ABC XXX 75 2014-02-04

    8 9 ABC XXX 96 2014-02-06

    9 14 ABC HardDrink 12 2014-02-10

    10 14 ABC Fanta 93 2014-02-22

    11 14 ABC XXX 101 2014-02-22

    12 14 ABC Pepsi 150 2014-02-23

    13 14 ABC Pepsi 510 2014-02-24

    14 20 ABC HardDrink 175 2014-02-25

    15 20 ABC Fanta 136 2014-02-26

    16 20 ABC XXX 145 2014-02-27

    17 20 ABC XXX 175 2014-02-28

    18 20 ABC XXX 196 2014-03-01

    19 20 ABC XXX 112 2014-03-02

    20 20 ABC XXX 193 2014-03-03