Select query with case when logic showing critical flag for one record only

1

Declare @Sample table (activity_id int, parent_id int, due_date datetime, done_date datetime)
insert @Sample
select '168',522,'09-Sep-2016', '11-sep-2016' union all
select '173',522,'12-sep-2016', NULL union all
select '176',522,'14-sep-2016', NULL union all
select '182',522,'15-sep-2016', NULL union all
select '173',522,'16-sep-2016', NULL;

select * from @Sample;

i want to show the next critical activity record, which has the lowest due_date and the done date is null, for that parent_id, for that record i need to show critical=1(true) and remaining records want to show critical=false(0)

critical is a surrogate column with the above case logic. how to put the case logic in select query. little confused.

my final result to appear with following columns last column critical is based on case.

select activity_id, parent_id, due_date, done_date, critical;

thank you very much for the helpful info.

2

For the given data, with SQL2012 and above you can use the LAG windowed function:

SELECT * 
   ,CASE
        WHEN done_date IS NULL
            AND LAG(done_date) OVER(PARTITION BY parent_id ORDER BY due_date) IS NOT NULL
        THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END AS Critical
FROM @Sample
ORDER BY parent_id, due_date;
3

I am on sql server 2008 and the lag is not getting recognized. what would be the alternative to lag on sql server 2008 please.

Thanks a lot for the helpful info.

4 
WITH RowOrder
AS
(
    SELECT *
        ,ROW_NUMBER() OVER (PARTITION BY Parent_id ORDER BY due_date) AS rn
    FROM @Sample
)
SELECT R1.*
    ,CASE
        WHEN R1.done_date IS NULL
            AND R2.done_date IS NOT NULL
        THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END AS Critical
FROM RowOrder R1
    LEFT JOIN RowOrder R2
        ON R1.parent_id = R2.parent_id
            AND R2.rn = R1.rn - 1;