Quick though, first move towards an SQL set based solution is to change the word Loop to Group. Each Group starts with Coke/Fanta and ends in a HardDrink:doze:

😎

Here is an example to get you started, it is not a full solution.

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 DRINK_WEIGHT(WEIGHT_VAL,DRINK_NAME) AS

(SELECT WEIGHT_VAL,DRINK_NAME FROM (

VALUES (1,'Coke'),(2,'Fanta'),(3,'Pepsi'),(4,'XXX'),(5,'HardDrink')

) AS X( WEIGHT_VAL,DRINK_NAME))

,BASE_DATA AS

(

SELECT

T_ID

,T.UserName

,T.Drink

,T.Value

,T.CreatedDate

,DW.WEIGHT_VAL

FROM #TEMP T

OUTER APPLY DRINK_WEIGHT DW

WHERE T.Drink = DW.DRINK_NAME

)

SELECT

BD.T_ID

,BD.UserName

,BD.Drink

,BD.Value

,BD.CreatedDate

,BD.WEIGHT_VAL

FROM BASE_DATA BD

DROP TABLE #TEMP;