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