Jeff Moden

Forum Replies Created

Viewing 15 posts - 1,906 through 1,920 (of 59,082 total)

Reply To: Need to do a mass update - taking days

Jeffrey Williams wrote:
You can probably get a significant boost to performance by removing FORMAT and converting those JOINS to a single CROSS APPLY.
It appears you are looking up the anonymized user...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 16, 2023 at 1:23 am

#4150299
Reply To: Need to do a mass update - taking days

Heh... Ed... as Jeffrey Williams also recommended, my sincere and very friendly recommendation is that you stop using FORMAT. It's 17 times slower that just about any other rabbit you...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 16, 2023 at 1:15 am

#4150297
Reply To: Alternative to row_number in natively compiled stored procedure

You are at the edge of a cliff. I strongly recommend you turn around and go another way.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 9:37 pm

#4150263
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

Heh... I did that with the count 1 to a million thing. IIRC, I posted that little gem that said the rCTE that it created (which I also told it...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 9:02 pm

#4150259
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

Jonathan AC Roberts wrote:
Jeff Moden wrote:
Thanks for taking the time to do these experiments, Jonathan.
The reason it got the Zip right is because it's in quotes. You also had to realize its mistakes...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 8:56 pm

#4150245
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

And, in the process of making the primary key, it made yet another mistake (at least according to my personal standards). If it's a #TempTable, then never name the PK...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 8:49 pm

#4150242
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

Thanks for taking the time to do these experiments, Jonathan.
The reason it got the Zip right is because it's in quotes. You also had to realize its mistakes on the...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 8:44 pm

#4150241
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

Heh... where's "Clippy" when you need him? I can just see him saying "It looks like your trying to design a table to hold CDRs (Call Detail Records). Do you...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 6:45 pm

#4150204
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

Jonathan AC Roberts wrote:
Jeff Moden wrote:
Jonathan AC Roberts wrote:
Then after you wrote this I thought I'd see what ChatGPT could do to create a table with the data:
So I pasted the table in the question...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 6:35 pm

#4150200
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

Jonathan AC Roberts wrote:
Then after you wrote this I thought I'd see what ChatGPT could do to create a table with the data:
So I pasted the table in the question into ChatGPT...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 5:33 pm

#4150101
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

kaj wrote:
Unless the "with the matching dates" is to be interpreted as a date range selection, in which case Jeff Moden's integer math would be needed to ensure that the...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 5:24 pm

#4150097
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

@Wecks ,
~~We still need an answer to my questions about the required rounding. Thanks.~~
EDIT: Never mind... forgot to refresh before posting this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 5:14 pm

#4150092
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

@Wecks ...
Since these are CDRs, do you need to obey such rules as the classic "Whole minute rounding (up) per call" rule or any other some-such rules? Either way, which...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 4:27 pm

#4150073
Reply To: Aggregate Select Results and convert Secs to Hours Mins.
Jonathan AC Roberts wrote:
Create some test data:
```
DROP TABLE IF EXISTS #temp
GO
SELECT *
INTO #temp
FROM (VALUES 
(001, CONVERT(DATE,'2023-02-23'), '123', 'a@a.com', '10:23:00.000', 500, 'W'),
(002, '2023-02-23', '246', 'b@b.com', '09:36:00.000', 456, 'W'),
(003, '2023-02-22', '123',...
```
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
February 15, 2023 at 4:24 pm

#4150072
Reply To: Aggregate Select Results and convert Secs to Hours Mins.

Unless I'm missing something in the requirements, "awesome SQL" isn't required here... just a GROUP by on the columns you mention, a COUNT(), and a SUM() of the CallDuration column. ...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

February 15, 2023 at 3:14 pm

#4150051

Viewing 15 posts - 1,906 through 1,920 (of 59,082 total)