Jeff Moden

Forum Replies Created

Viewing 15 posts - 211 through 225 (of 59,076 total)

Reply To: Using ROW_NUMBER() to increment every time a value occurs

It would be easier if the first row were numbered "0" instead of 1 so that we could use modulo all by itself to do this but, it can still...
--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)

January 10, 2025 at 10:32 pm

#4513530
Reply To: Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

AnzioBake wrote:
@jeffmoden I have not spent much time on SSC for a good few years. Searching for something else, I came across this article (and the follow up) written by...
--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)

January 10, 2025 at 4:36 pm

#4513338
Reply To: Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

Y.B. wrote:
isaac.arellano wrote:
Hi, excellent post, I have taken it and put it into practice, and it works very well for me to redo the whole nested set, But I want...
--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)

January 10, 2025 at 4:34 pm

#4513335
Reply To: Count of Total Appointments within 30 days of each Appointment Date

Just to ask the question, I haven't tested any of the solutions and so this question is directed at Reh23 ...
Do any of the solutions, so far, do what 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)

January 10, 2025 at 4:18 pm

#4513324
Reply To: Count of Total Appointments within 30 days of each Appointment Date

Jeff Moden wrote:
First of all, please stop posting graphics for the test data. See the article at the first link in my signature line below for why and one method for...
--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)

January 10, 2025 at 4:17 pm

#4513323
Reply To: Count of Total Appointments within 30 days of each Appointment Date

Reh23 wrote:
Thanks Jeff. Its SQL Server 2017.
Your other post is on an SQL Server 2022 forum.
--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)

January 10, 2025 at 4:13 pm

#4513322
Reply To: The Lesser Used Functions

Just to be sure, I would never obfuscate numeric data. If it's worth protecting, it's worth being properly encrypted.

--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)

January 10, 2025 at 4:11 pm

#4513321
Reply To: Send results of a SQL query in Outlook email body

Jonathan AC Roberts wrote:
Jeff Moden wrote:
Lord Slaagh wrote:
Hi SSC team,
I wanted to leave the answer here just in case anyone else needs it. This works. Only caveat is that you need to use dts.vaiables...
--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)

January 10, 2025 at 2:34 am

#4513012
Reply To: The Lesser Used Functions

I use IIF a whole lot to replace the longer CASE expression in CROSSTABs. It just makes for shorter code when there are only Yes/No conditions.
I have used some of...
--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)

January 10, 2025 at 2:26 am

#4513011
Reply To: Count of Total Appointments within 30 days of each Appointment Date

First of all, please stop posting graphics for the test data. See the article at the first link in my signature line below for why and one method for producing...
--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)

January 9, 2025 at 5:42 pm

#4512787
Reply To: Count of Total Appointments within 30 days of each Appointment Date

Just to be sure... you're in an SQL Server 7/2000 forum... which release of SQL Server are you actually using?
And, yeah... It's really important because there's a whole lot missing...
--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)

January 9, 2025 at 5:30 pm

#4512784
Reply To: Send results of a SQL query in Outlook email body

Lord Slaagh wrote:
Hi SSC team,
I wanted to leave the answer here just in case anyone else needs it. This works. Only caveat is that you need to use dts.vaiables instead of...
--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)

January 9, 2025 at 4:20 pm

#4512741
Reply To: why no suggested indexes in estimated exec plan?

stan wrote:
thx jeff. eventually i did do the divide and conquer thing. it took the query down from 6 minutes to 19 seconds. i turned it over in that state...
--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)

January 9, 2025 at 1:12 am

#4512331
Reply To: why no suggested indexes in estimated exec plan?

Shifting gears to the dark side, it may be that the query was written in such a fashion that the optimizer just can't figure out if an index would help...
--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)

January 8, 2025 at 4:17 pm

#4512133
Reply To: create multiple output files based on query

Ok... so you don't need the xp_CmdShell example then.
--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)

January 6, 2025 at 4:06 pm

#4510922

Viewing 15 posts - 211 through 225 (of 59,076 total)