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.