There are examples on the internet of how to recursively assign HIERARCHYID.  A while ago on this forum:

Using hierarchyID

My reply in the SSC forum was based on this Stack...

"a continuous year without any gaps" might mean different things.  This assumes it to mean (any) 365 days in a row

If the EXP_DATE is null does that mean the account...

• This reply was modified 8 months, 1 weeks ago by Steve Collins.

Hey there why so hasty?  If you'd fixed the script we might've seen some JM t-SQL  It's been a while afaik :).  The query seems nicely done tho.  Presumably uniqueness...

There are...

Afaik there are only two possible values for the Category column: { 'Lessthan8', 'greatherthan8' }.  If that's so the query could rely on EXISTS to handle any duplication

            
April 24, 2025 at 10:26 pm
            
#4583779

            
            
                                                
        

Regarding the precision of the data types

cast(Price as decimal(9,7)) Calculation

The line above CAST's the price (Price) to DECIMAL(9, 7).  Imo this is not a good practice.  When...

My query was insufficient because it applied the factor percentage to the lagged price and not to the lagged calculated price.  It seems recursion is unavoidable.  This query uses a...

Why are you using these data types with this numerical precision?  By making @Factor DECIMAL (9,7) and applying this percentage to a price it adds significance to decimal places to...

One way to unpivot unique values (of columns [Emp_1], [emp_2], [Emp_3]) could be to CROSS APPLY their UNOIN. The syntax might appear to be unusual because the subquery doesn't contain...

with stub_cte as (
    select BName, StartDate, StartTime, 
           lag(StartDate) over (partition by v.stub...

The @dateChoice variable is passed in from a dropdown box and the WHERE clauses between the two queries are different.  Syntax-wise you could try IF @dateChoice = 'DOS' SELECT ... ...

The only relational operator which seems required is a partial CROSS JOIN of #tmp (WHERE 'parent_id' IS NULL) to #tmp (WHERE 'parent_id' IS NOT NULL) ON 'uniqueid'.  Then it's a...

In your JSON the outermost delimiter is an array bracket which means it's an anonymous array.  To reference the only record in the array you could provide the 'path' to...

In the function JSON_VALUE 'path' is a required parameter and in the tvf OPENJSON 'path' is optional. The implementation of 'json path' in SQL Server is a limited subset of...

Where's your sample data?  A calendar table determines the workdays?  This is asking a bit.  Using AVG OVER can only accept literal numbers in the RANGE PRECEDING and afaik it...