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 6 years, 7 months ago by Steve Collins. Reason: added sorted by recid, docdate descending
• This reply was modified 6 years, 7 months 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 6 years, 7 months ago by Steve Collins. Reason: Got rid of unnecessary declared variable
• This reply was modified 6 years, 7 months 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 6 years, 7 months 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 6 years, 7 months 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...

Yes one query.  Run multiple...

Nope, just wrong.  Multi-version read consistency does not introduce phantom rows - it gives a consistent view of data at a single point in time.  Databases are state machines...

Right they won't see the 11th row because it's no longer the 11th row and displaying it as such is no longer accurate.  They will see that row if...