It's a one time only type query or it's intended to be run frequently?  The id value in the last row of the sample data should be 5, no?

O sorry, I was thinking you meant 2nd from the end.  I seemed to have missed the date range part too

declare @StartDate DATE = '2021-09-24', @EndDate DATE...

You could try using ROW_NUMBER twice.  Once to find the most recent row within the MyIdentifier partition and then again to find the 2nd oldest row (also within the MyIdentifier...

Call it what you want, using a GUID to "represent the PK" or vice versa makes no sense to me.   Both columns are fixed width (unless you've done row...

Maybe it was incorrect to label the GUID column 'guid_id' because it was intended as a public attribute of the primary key. Maybe it would be better labelled as 'securitystamp'...

Thank you Jeff.  On some projects we've had logical/physical GUID issues (or idk what to call it, let's say it's the subject of ongoing analysis) which we've sort of managed...

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, 11 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?