So, they came up with a workaround as below.

- Introduced a new column as BIGINT column.

- Made the bigint column as NOT NULL IDENTITY(1,1) with clustered index on it

-...

Good idea but I'm hoping that you don't think that I didn't know that. 😀  It doesn't matter as much as it did before but there used to be...

Ha, nice Jeff.  @@DATEFIRST returns 7 on my test instance so no worries there.  In the tvf it seems possible to factor out a common table expression and the UNION...

I'd be real careful with that.  It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations.  I avoid its...

This 🙂

Would we need to?  A single math calc vs iterating thru string...

I've amended your code. I think this should be a few times faster as it removes the need for a tally of 7 week days and just calculates the...

Here's another version which replaces the second dbo.fnTally tvf with an explicit values table (0-6) and which eliminates the outer WHERE clause by adding the row filter to the final...

• This reply was modified 4 years, 4 months ago by Steve Collins.

Thank you Jeff.  It's my pleasure of course.  Please let me know if there's anything else I could do

You ran a comparison?

Fwiw in 2021.  In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month.  Then in the WHERE clause filter...

It's a lot going on.  The query splits the rows into "gap and island" groupings labelled 'grp'. Within each grouping the CTE 'top_c_cte' selects the largest CA value when Stat='C'. ...

It's not really a conclusive test.  Maybe you're missing a JOIN condition on location?  Is there a column in the SCPOMGR.DMDUNIT table which correlates to the 'LOC' column in the...

Maybe the INNER JOIN between SCPOMGR.FCSTPERFSTATIC AS F and SCPOMGR.DMDUNIT AS D on the 'DMDUNIT' column could be causing row expansion.  Would you expect this query to return zero rows?

Vielleicht sowas

with upd_cte(SUM_KBABenoetigteZeit, pm_extended_id) as (
    select sum(TCompl.usr_KBABenoetigteZeit), PMProc.pm_extended_id
    from rm_task_completion TCompl 
        ...

Again due to gaslighting on the other major technical Q&A forum I'm making pointless public statements