WITH C (id, pt, gm, dr) as (SELECT cid, ctime, cgm, dense_rank() over (partition by cid, cgm order by cid, ctime) as drFROM testsql)SELECT id, pt, gm, CASE WHEN gm IS NULL THEN LAG(gm, cast(c.DR as int)) OVER (PARTITION BY id ORDER BY id, pt) ELSE gm END as gm2FROM CORDER BY id, pt, gm

There's one result towards the end that is still NULL, rather than being filled in with a LAG value, like the rest in the CASE column are.

OK, just saw your use of LAG which means SS20012.But you're making completely wrong use of it.If you delete one of the rows with cgm='5' from your data and run your script the results become a lot worse.In your final select display the dr column and you'll see this right away.Also, no dense_rank needed here. Rank is sufficient.

Michael Meierruth (1/31/2014)OK, just saw your use of LAG which means SS20012.But you're making completely wrong use of it.If you delete one of the rows with cgm='5' from your data and run your script the results become a lot worse.In your final select display the dr column and you'll see this right away.Also, no dense_rank needed here. Rank is sufficient.

It's not completely wrong - it's getting me all the test data except one row how I want it. Do you have another suggestion?

Switching from dense rank to rank doesn't change output, so I'm not sure what you mean.

You didn't understand my remark regarding removing one of the rows with cgm='5'.If you do that your solution will now show even more incorrect results.

I understand what you are trying to do but unfortunately the rank values being generated make your approach invalid.I can't see a way a to generate the rank values the way you would like them.

Thus here is my solution which takes a more classic approach in dealing with arbitrarily embedded groups.It also will handle some strange cases.Thus a more generic problem definition is:within every cid group, update every group of nulls with the cgm value of the row immediately before the null group (if it exists)

Here is the script:

with

cte1 as -- change cgm nulls to 0 to avoid confusion with other types of nulls later on ( select cid,ctime,isnull(cgm,0) cgm from testsql ),

cte2 as -- find cid,cgm values before and after each row (-1 is returned for the first and last row) ( select cid,ctime,cgm, lag(cgm,1,-1) over (order by ctime) cgmbef,lead(cgm,1,-1) over (order by ctime) cgmaft, lag(cid,1,-1) over (order by ctime) cidbef,lead(cid,1,-1) over (order by ctime) cidaft from cte1 ),

cte3 as -- find first and last element of each null group ( select cid,ctime,cgmbef,cgmaft, case when cid<>cidbef or cgm<>cgmbef then 1 else 0 end isfirst, case when cid<>cidaft or cgm<>cgmaft then 1 else 0 end islast from cte2 where cgm=0 ),

cte4 as -- extract only the beginning of each group ( select cid,ctime,cgmbef,rank() over(partition by cid order by ctime) nullgroupid from cte3 where isfirst=1 ),

cte5 as -- extract only then end of each group ( select cid,ctime,cgmbef,rank() over(partition by cid order by ctime) nullgroupid from cte3 where islast=1 )