Count number of previous records using T-SQL

I have a SQL table where I need to get the number for the final row with a date and count the number of previous rows even if they don't have a date in either.

e.g

02/01/2011
03/01/2011
09/01/2011
NULL
10/10/2011
NULL

This table should return the number 5 for the 5th record

NULL
NULL
NULL
09/01/2011
N开发者_运维技巧ULL
10/10/2011
NULL

This table should return 6

Thank you in advance

J

----- Update ------ Just a little more information

The table its self represents units of work complete (milestones) and links to a parent table which represents units of work. In the milestone table it contains dates, Parent Work Id and a milsestone ID.

From the first example

ParentID    MilestoneID    Date
1234        123            02/01/2011
1234        124            03/01/2011
1234        125            09/01/2011
1234        126            NULL
1234        127            10/10/2011
1234        128            NULL

Hope this helps

----- Update 2 -----

The closest I got was this

SELECT TOP 1
    Num
FROM
    (
    SELECT
        ROW_NUMBER()OVER(ORDER BY ParentID) AS Num,
        Date

    FROM
        Milestone
    WHERE
        Milestone.ParentID = 1234
) AS MilestoneStones

WHERE Date IS NOT NULL ORDER BY Num DESC

But with a large data set and other things attaching to it it got very slow Was hoping that I could get something better

Thank you

J

I am going to assume some ordering column called ord and a date column called dt:

Naively:

SELECT COUNT(*)
FROM tbl
WHERE ord <= (SELECT MAX(ord) FROM tbl WHERE dt IS NOT NULL)

With your updated data, see how this performs:

https://data.stackexchange.com/stackoverflow/q/109223/

DECLARE @tbl AS TABLE (ParentID INT, MilestoneID INT, [Date] DATETIME);
INSERT INTO @tbl VALUES (1234,        123,            '02/01/2011');
INSERT INTO @tbl VALUES (1234,        124,            '03/01/2011');
INSERT INTO @tbl VALUES (1234,        125,            '09/01/2011');
INSERT INTO @tbl VALUES (1234,        126,            NULL);
INSERT INTO @tbl VALUES (1234,        127,            '10/10/2011');
INSERT INTO @tbl VALUES (1234,        128,            NULL);

WITH LastCompleted AS (
    SELECT ParentID, MAX(MilestoneID) AS MAXMilestoneID
    FROM @tbl AS Milestone
    WHERE [Date] IS NOT NULL
    GROUP BY ParentID
)
SELECT LastCompleted.ParentID, COUNT(*) AS NumMilestones
FROM LastCompleted
INNER JOIN @tbl AS Milestone
    ON LastCompleted.ParentID = Milestone.ParentID
    AND LastCompleted.MAXMilestoneID >= Milestone.MilestoneID
GROUP BY LastCompleted.ParentID;

I'm not sure what your implied sort order is. Is it the order the items are entered? If so, try adding an identity field. It will be incremented for each row you add. To figure out the row number for a particular row, just do a count of the records whose identity field are less than or equal to the target row.

If there's some other sort order you're intending, it's not clear from your example.

Need to have an Order by and your natural sort my be ParentID, MilestoneID and the syntax is trickier than you may have guessed. Without a an Order By clause there is no guaranteed sort (even if the table has a clustered PK). Cade I like you answer and I up ticked it but I could not post a code sample in a comment.

    SELECT COUNT(*)
    FROM tbl
    WHERE ParentID <= (SELECT MAX(ParentID) FROM tbl WHERE dt IS NOT NULL) 
    Or ( ParentID = (SELECT MAX(ParentID) FROM tbl WHERE dt IS NOT NULL)  
         And MilestoneID <= (Select MAX (MilestoneID) FROM tbl  
             where ParentID = (SELECT MAX(ParentID) FROM tbl WHERE dt IS NOT NULL)
             And dt IS NOT NULL)
        )
    Order By ParentID, MilestoneID

I know there is a temptation to simplify this but I do lots of multi column sorts where I need to grab the next X rows and there are subtle ways to get burnt. For your simple count yes you may be able to simplify but I also think this will give you the correct answer.