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;