The design is annoying but maybe not unfixable if you could encapsulate access (queries, views, etc.). Imo the bigger issue is whether the table(s) store when things happened (datetime per...

Hi Pieter,

Once you unpivot into (Symptom, Grade, Causality, Relatedness) you’ve gotten rid of the original “slot” number (the 1, 2, 3 suffixes). Maybe that matters? It might if you later...

Maybe the issue is the query could possibly be more efficiently evaluated if expressed as two queries.  It always depends on the actual situation with cardinalities and indexes etc.  If...

1.  It's not allowed.
2. SQL Server wants to guarantee a single parent delete cannot reach the same target row by more than one cascade route.  Multiple cascade paths are not...

Sure redesign if possible.  As far as the task at hand it depends on the number of rows really.  If there are a good many rows then allocating a temp...

Ha yeah I was reluctant to test it out.  Sorry for the syntax issues.  Originally I had a table CREATE statement but then switched to SELECT INTO because that's more...

How about creating a giant temp table from a CROSS JOIN using 2 fnTally functions.  Then join the temp table to itself forcing a HASH JOIN with tiny memory grants...

Assuming this is dev or the table is empty, DROP and recreate with the proper column type and default.  Something like this

drop table if exists dbo.latest_info;
go
create table...

You're looking to set the DEFAULT value of the publish_date column to be the current Unix timestamp?  If so the proper column type would be BIGINT.  SQL Server’s native datetime...

Summary
+ Input: long varchar text; max line width W.
+ Walk left-to-right, one word at a time.
+ Separators: space; CR/LF are hard line breaks.
+ For each word:
 ...

If there are alternate code paths which depend on the client then spare a thought for the optimizer. This pattern implies parameter sniffing imo.  To the extent there are separate...

Ah yes, I see that now.  Thanks Jeff.  In my head I did check to make sure the dates within the partition were unique and then told myself "nothing to...

• This reply was modified 10 months, 2 weeks ago by Steve Collins.

Yes I agree pietlinden has the correct approach imo.  SUM OVER provides a running total without recursion

select *, sum(OpenOpps+CreatedOpps+WonOpps+LostOpps) over (partition by Division order by [Date]) running_total
from...

• This reply was modified 10 months, 2 weeks ago by Steve Collins.
• This reply was modified 10 months, 2 weeks ago by Steve Collins.

DROP TABLE IF EXISTS #data_table;
GO
CREATE TABLE #data_table (
    Id INT PRIMARY KEY,
    Model INT,
    RecordType INT,
  ...

It took some testing to get the recursion working.  From the reference paths it's not clear how to proceed tho.  Maybe you're looking for the maximum level for each ProcedureName?