This might not be the most efficient way of solving the puzzle, but it does generate the correct results - which I always put before performance or code sophistication:
DECLARE @SAMPLE_DATA TABLE
(
XBit INT NOT NULL,
CreateDate DATE NOT NULL,
DesiredRank INT NOT NULL
);
INSERT INTO @SAMPLE_DATA (XBit,CreateDate, DesiredRank)
VALUES
(1, '3/15/2014', 1)
,(1, '3/14/2014', 2)
,(0, '3/9/2014', 1)
,(0, '3/8/2014', 2)
,(0, '3/6/2014', 3)
,(1, '3/4/2014', 1)
,(0, '2/28/2014', 1)
,(0, '2/20/2014', 2)
,(0, '2/15/2014', 3)
,(0, '2/10/2014', 4)
; WITH CTE AS (
SELECT *,
grp = ROW_NUMBER() OVER(ORDER BY CreateDate) - ROW_NUMBER() OVER(PARTITION BY XBit ORDER BY CreateDate)
FROM @SAMPLE_DATA
)
SELECT *,
NewRank = ROW_NUMBER() OVER(PARTITION BY grp ORDER BY CreateDate DESC)
FROM CTE
ORDER BY CreateDate DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden