SQL Server 2014 – Set Value Based on Number of Records with Certain Values

You can calculate the Delay in a cross apply and use dateadd() to add it to st_date in the main query.

select D1.Split3_ID,
       D1.CU_ID,
       D1.order_id,
       D1.st_date,
       D1.sku,
       D1.Priority,
       D1.Delay,
       dateadd(day, D2.Delay, cast(D1.st_date as date)) as CourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.st_date = D2.st_date and
                    D1.Priority > D2.Priority
              ) as D2(Delay)

SQL Fiddle

Update:

I am not really sure how you want to deal with different st_date within a order_id. The above query groups on order_id and st_date but I guess an alternative would be to use the lowest st_date per order_id in the dateadd function.

select D1.Split3_ID,
       D1.CU_ID,
       D1.order_id,
       D1.st_date,
       D1.sku,
       D1.Priority,
       D1.Delay,
       dateadd(day, D2.Delay, cast(isnull(D2.st_date, D1.st_date) as date)) as CourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0), 
                     min(D2.st_date)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.Priority > D2.Priority
              ) as D2(Delay, st_date)
order by D1.Split3_ID

Update 2:

The update statement for the second query could look like this.

with C as
(
select D1.CourseDate,
       dateadd(day, D2.Delay, cast(isnull(D2.st_date, D1.st_date) as date)) as NewCourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0), 
                     min(D2.st_date)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.Priority > D2.Priority
              ) as D2(Delay, st_date)
)
update C
set CourseDate = NewCourseDate

Update 3:

Or even like this if st_date is guaranteed to be the same within one order_id.

update D1
set CourseDate = dateadd(day, (select isnull(sum(D2.Delay), 0)
                               from dbo.set_dates as D2
                               where D1.order_id = D2.order_id and
                                     D1.Priority > D2.Priority), D1.st_date)
from set_dates as D1

You will be able to avoid a lot of your race conditions by performing many of your steps in a single statement. By using a TOP() cluse will be able to set the flag on at most one row. By using the OUTPUT cluse you can return this to the application automically.

I define a simple test table and populate it:

create table dbo.T(id int, IsProcessing bit default 0);

insert dbo.T(id) values (1),(2),(3),(4);

The output clause needs a table variable to receive the changed values:

declare @U table (id int);

A bit of debug code to make the "before" and "after" states obvious:

select 'Before' as B, * from dbo.T;

And the statement itself:

update top (1) dbo.T
set IsProcessing = 1
OUTPUT INSERTED.id
INTO @U(id)
where IsProcessing = 0;

And the results:

select 'After' as A, * from dbo.T;
select * from @U;

This is the output from the second execution of the above.

B       id  IsProcessing
Before  1   1
Before  2   0
Before  3   0
Before  4   0

A       id  IsProcessing
After   1   1
After   2   1
After   3   0
After   4   0

id 
2

The row with id=2 had gone from IsProcessing=0 to IsProcessing=1 and that id is returned in the table variable.

With this trivial data the rows are processed in the sequence they were created. In a more complex environment the optimizer may choose any row that matches the where clause. If you have a requirement to process rows in, say, time sequence further qualification will be required.

I haven't thought it all the way through but I believe his will work at any isolation level with or without explicit transactions.

It is of course possible that there are no rows with IsProcessing=0 at the time the statement runs. In this case the table variable will have zero rows.

To fully isolate each transaction from the other you could try sp_getapplock. This will add overhead and reduce concurrency. You have to be careful to release the app lock as soon as you can in both success and failure scenarios.