Here is a very well performing solution which adds a covering filtered index, simply blazing fast.;-) Even without the index it is still more than three times faster than previous solutions. Of course the building of the index will take some time but my thought is that the building time will be recovered during the execution.

😎

USE tempdb;

GO

SET NOCOUNT ON;

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');

CREATE INDEX TMP_TBL_CREATEDATE_ASC_INCL_ALL ON #TEMP (CreatedDate ASC) INCLUDE (UserName,Drink,Value) WHERE (Drink <> 'XXX');

SET STATISTICS IO ON;

;WITH BASE_DATA AS

(

SELECT

ROW_NUMBER() OVER (ORDER BY T.CreatedDate) AS T_RID

,T.UserName

,T.Drink

,T.Value

,T.CreatedDate

FROM #TEMP T

WHERE T.Drink <> 'XXX'

)

SELECT

BD.UserName

,BD.Drink

,BD.Value

,BD.CreatedDate

FROM BASE_DATA BD

LEFT OUTER JOIN BASE_DATA BLEAD

ON BD.T_RID = BLEAD.T_RID - 1

WHERE BLEAD.Drink = 'HardDrink' OR BLEAD.T_RID IS NULL;

SET STATISTICS IO OFF;

DROP TABLE #TEMP;

Edit: typo