Suppose you have 3 people and each has...

What Jeff wrote is enough to scare me away from even trying this approach.  Using internal locking could enable end-users to do things which produce unpredictable performance.  You wrote "users...

When there no uncommitted transactions do the EMpbasetable and  EmpIncTable have the same number of rows?

Here's a similar way that's maybe simpler.

with x_cte as (
select
  *,
  row_number() over (partition by recid order by docdate desc) row_num 
from 
  #tmptbl
where
...

Here is another option:

   With groupedDates
     As (
 Select t.recid
      , t.docdate
   ...

declare
  @docdatedate='2019-08-09';

with
range_cte(recid, docdate, nxt_dt, nxt_dt_diff) as (
select
  t.*,
  lead(t.docdate, 1) over (partition by recid order by docdate desc) nxt_dt,
  datediff(dd, lead(t.docdate, 1) over...

• This reply was modified 5 years ago by  Steve Collins. Reason: added sorted by recid, docdate descending
• This reply was modified 5 years ago by  Steve Collins. Reason: Wasn't giving the correct output in some cases

Ok issue is the initial nxt_dt_diff is not equal to 1.  Or the code doesn't handle that properly now.  I'll update it.

drop table if exists #tmptbl;
go
create table #tmptbl(
  recidint,
  docdatedate,
  constraint unq_tmptbl_recid_dt unique(recid, docdate));
go

insert into #tmptbl values 
(1, '11/16/19'),(1, '11/15/19'),(1, '11/14/19'),(1, '11/13/19'),(1, '10/29/19'),(1, '10/27/19'),
(1, '10/26/19'),(2,...

• This reply was modified 5 years ago by  Steve Collins. Reason: Got rid of unnecessary declared variable
• This reply was modified 5 years ago by  Steve Collins.

The two CTE's could be consolidated into one.

with
avg_cte as (
select
  t1.PID,
  t1.SID,
  avg(isnull(t1.TValue/T2.BPrice, 0)) avg_volume
from
  #tblData1 t1
 join
  #tblData2 t2 on t1.SID=t2.SID
...

• This reply was modified 5 years ago by  Steve Collins. Reason: fixed typo in code

The weighted average price is still the price.  Are you looking for average volumes?  Unique constraints on (SID, PID) to tables t2 and t3 are valid for your situation?  Assuming...

The article says: "The typical usage of collections is a multi-valued argument for functions and procedures."  True but other solutions exist and are quite useful in comparison to a spatial...

Merge statements don't have WHERE clauses which is why the target is typically defined in a CTE.  In this case there's no CTE so you're merging against the entire DVDB1.Raw.LinkOpportunity...

Besides assigning items like couch, lamps, bed to rooms, ... this UI is a tool to enforce which columns other UI's or database clients can see?  So maybe it makes...

The problem is that the requested parameters are dynamic over the time.  And honestly, I can't imagine, in 2019, a DB design requiring view, SP and table update when...

• This reply was modified 5 years ago by  Steve Collins. Reason: Added unique to room_number

Is the value column in the T_ROOM_ATTRIBUTES table necessary?  Why not delete row(s) (in T_ROOM_ATTRIBUTES) if a room no longer has attribute(s)?

Do you want the target output to always have...