There are...

BWAAAAA-HAAAA-HAAAA-HAAAA!!!  I don't recall Itzik ever using the word TALLY in any of his code.  At least I'm not the only one that it's mis-quoting.

February 7, 2023 at 7:15 pm

#4146706

You should have just asked it "How would Jeff Moden do it" - LOL

I'm officially "dead" with ChatGPT.  It never asked me for a password when I first setup my login.  I logged out and told it I forgot my password when...

• This reply was modified 2 years, 7 months ago by Jonathan AC Roberts. Reason: added final answer

I just tried pasting the entire question, including the "Hi"...

So it would seem!  Thanks, Jonathan.  I'll check later for things like CPU, Reads, Parallelism, and what happens with different indexes.

I take it that the PK you used was...

You're saving me lot's of time, Jonathan.  What are the conclusions of your tests?

p.s. I...

Here is something to set up about 500k rows of test data

If (OBJECT_ID('tempdb..#tbSourceData') is not null) Drop Table #tbSourceData;
--Create Table #tbSourceData (EventId int, EventStatus varchar(20), RowNum int,...

Or maybe even using LEAD:

Yes, nice solution Jeff.

Here is an alternative solution:

SELECT datOdd.EventID,
       datOdd.EventStatus  AS InactiveEvent,
       datOdd.UpdatedDate ...

Several things wrong.

1. JSON is missing a bracket at the end '}'
2. Some of the declared variables have no space between the name and the type.
3. sp_executesql is not passing...

If you want to post SQL in your reply then you should paste it into a code box.

;WITH ctevalues(recp_name, ing_name, qty) AS 
(
    SELECT * 
      FROM (VALUES
        ...

• This reply was modified 2 years, 7 months ago by Jonathan AC Roberts.

If you want to avoid an error if zero is passed in you can use this with an IIF:

ROUND(T.C, 6 - FLOOR(IIF(T.C=0, 0, LOG10(ABS(T.C)))))

You might be able to do it with SSIS and VB.NET or C#

https://stackoverflow.com/questions/58683425/how-to-use-pdf-file-as-data-source-in-ssis