You're welcome.

Try this:

DROP TABLE #TEMP

CREATE TABLE #TEMP (PlayerName varchar(20),DismissFormat varchar(20),StrickRate int,CreatedDate date)

INSERT INTO #TEMP VALUES ('ABC','Catch Out',100,'2014-01-12') --Date is in YYYY/MM/DD format

INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',50,'2014-01-13')

INSERT INTO #TEMP VALUES ('ABC','Bowled',50,'2014-01-14')

INSERT INTO #TEMP VALUES ('ABC','LBW',75,'2014-01-18')

INSERT INTO #TEMP VALUES ('ABC','XXX',36,'2014-01-20')

INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',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','LBW',12,'2014-02-10')

INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',93,'2014-02-22')

INSERT INTO #TEMP VALUES ('ABC','XXX',101,'2014-02-22')

INSERT INTO #TEMP VALUES ('ABC','XXX',150,'2014-02-23')

INSERT INTO #TEMP VALUES ('ABC','XXX',510,'2014-02-24')

INSERT INTO #TEMP VALUES ('ABC','LBW',175,'2014-02-25')

INSERT INTO #TEMP VALUES ('ABC','Hit Wicket',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 ix_one ON #TEMP (PlayerName,CreatedDate,DismissFormat) INCLUDE (StrickRate)

CREATE INDEX ix_two ON #TEMP (DismissFormat) INCLUDE (CreatedDate,PlayerName,StrickRate)

CREATE INDEX ix_three ON #TEMP (PlayerName,DismissFormat,CreatedDate)

-- Grab all rows where DismissFormat = 'LBW'.

--Inline to these rows, grab the most recent row (for the same PlayerName) where DismissFormat = 'Catch Out' or 'Hit Wicket'.

-- Grab rows where DismissFormat = 'Hit Wicket' and there isn't a following row with DismissFormat = 'LBW' for the same PlayerName.

SELECT

t.PlayerName,

t.DismissFormat,

t.CreatedDate,

StrickRate = CASE WHEN t.DismissFormat = 'LBW' THEN x.StrickRate ELSE t.StrickRate END

FROM #TEMP t

OUTER APPLY (

SELECT TOP 1 StrickRate

FROM #TEMP ti

WHERE ti.PlayerName = t.PlayerName AND t.DismissFormat = 'LBW'

AND ti.CreatedDate < t.CreatedDate

AND ti.DismissFormat IN ('Catch Out','Hit Wicket')

ORDER BY ti.CreatedDate DESC

) x

WHERE

t.DismissFormat = 'LBW'

OR (

t.DismissFormat = 'Hit Wicket'

AND NOT EXISTS (

SELECT 1

FROM #TEMP ne

WHERE ne.PlayerName = t.PlayerName

AND ne.DismissFormat = 'LBW'

AND ne.CreatedDate > t.CreatedDate

)

)

--ORDER BY t.PlayerName, t.CreatedDate