Jeff Moden

Forum Replies Created

Viewing 15 posts - 1,891 through 1,905 (of 59,067 total)

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
Reply To: LOJ followed By LOJ - What is this and is it best practices

No. I don't avoid it. I never write things that way but I don't change them if I come across them. As you say, it doesn't cause any issues except...
--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 14, 2023 at 10:39 pm

#4149808
Reply To: LOJ followed By LOJ - What is this and is it best practices

The only time I've seen one like that is when someone use the "Query Builder" functionality and only in what I refer to as a "Skip Join" where Table A...
--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 14, 2023 at 7:18 pm

#4149686
Reply To: PIVOT, is there a better way to do this?

ARPRINCE wrote:
ScottPletcher wrote:
ARPRINCE,
Have you looked at the query plans for the two queries? The LEFT JOIN method is doing a full table scan per join. That could be a (big)...
--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 14, 2023 at 4:37 pm

#4149611
Reply To: options for querying external data sources from SQL Server

Be careful of "old" tutorials on this one. They HAVE changed it, especially starting in 2019 (according to the MS documentation) and some of the "old" methods may no longer...
--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 14, 2023 at 4:34 pm

#4149609
Reply To: Dealing with super huge table which is partially partitioned.

Interesting... the first time I clicked on it, it took me to a page selling hair-care products with that picture on it. I just clicked on it again and, you're...
--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 14, 2023 at 4:30 pm

#4149593

Viewing 15 posts - 1,891 through 1,905 (of 59,067 total)