Just in case you missed this from the previous instance of the question, modified to return blank if no HardDrink

😎

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

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

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

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

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

SET STATISTICS IO OFF;

DROP TABLE #TEMP;

Results

UserName Drink Value CreatedDate

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

ABC Pepsi 50 2014-01-14

ABC Fanta 36 2014-01-20

ABC Pepsi 510 2014-02-24

Table '#TEMP'. Scan count 2, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.