Hi parth, try this query select ,(select count() from TableName where Grp = t.Grp and Vacation = 'Y' and ID <= t.ID) from TableName t

Hi Rami,

Thanks for your reply. But I am already doing so. But it requires inline query executed each time. Is there any other way to do so?

Oh.. How stupid I was. It was so easy..

;WITH tmp (Id, Vacation, Grp) AS
(
    SELECT 1, 'N', 1
    UNION ALL
    SELECT 2, 'Y', 1
    UNION ALL
    SELECT 3, 'N', 1
    UNION ALL
    SELECT 4, 'Y', 1
    UNION ALL
    SELECT 5, 'N', 1
    UNION ALL
    SELECT 6, 'Y', 2
    UNION ALL
    SELECT 7, 'N', 2
    UNION ALL
    SELECT 8, 'N', 3
),
tmp1 AS
(
    SELECT Id, Vacation, Grp,
    VacationOrder = ROW_NUMBER() OVER (PARTITION BY Vacation ORDER BY Id),
    RowNo = ROW_NUMBER() OVER (ORDER BY Id)
    FROM tmp
)
SELECT Id, Vacation, Grp, 
TotalVacationTillDate = CASE WHEN Vacation = 'Y' THEN VacationOrder ELSE RowNo - VacationOrder END
FROM tmp1 ORDER BY Id;