Jeff Moden

Forum Replies Created

Viewing 15 posts - 1,336 through 1,350 (of 59,082 total)

Reply To: List Month End Dates Between Two Dates

Ok. Here's an iTVF that will do the trick for you in which ever way you might want to solve your problem. As with most things, the details are in...
--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)

July 10, 2023 at 12:45 am

#4231038
Reply To: List Month End Dates Between Two Dates

It would appear that a simple definition of what you want is as follows:
Return the last weekday of the month (regardless of any holidays).
To help others understand that,...
--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)

July 9, 2023 at 7:07 pm

#4230889
Reply To: Find spaces between First and Last Name in Microsoft SQL
Nice.... you don't even need the CTE with that method.
```
SELECT
Customername
,PATINDEX('%[ ][a-zA-Z]%', c.CustomerName) - PATINDEX('%[a-zA-z][ ]%', c.CustomerName) CountSpaces
FROM #CustomerData c
```
--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)
July 6, 2023 at 4:24 am

#4228124
Reply To: Use BCP and a format file to export data to a text file

Emperor100 wrote:
Jeffrey Williams wrote:
Just curious - but if you had to transfer PII/PCI/PHI for a conversion (for example) where the unencrypted data is required by the receiving party, how would...
--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)

July 5, 2023 at 7:04 pm

#4227788
Reply To: Find spaces between First and Last Name in Microsoft SQL

kajaldigital8700@gmail.com wrote:
In Microsoft SQL, you can find the spaces between the first and last name using various string manipulation functions. One approach is to use the CHARINDEX function, which returns...
--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)

July 5, 2023 at 7:00 pm

#4227769
Reply To: Use BCP and a format file to export data to a text file

Jeffrey Williams wrote:
You are making a lot of assumptions here that are incorrect.
Nope. Not making any assumptions here. I know and stated exactly what I'd do. People that do otherwise...
--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)

July 5, 2023 at 6:57 pm

#4227768
Reply To: Query performance comparison

Also, never use SET STATSTICS if the code involves ANY Scalar UDF. It'll make the code look a serious amount slower than it actually is. Please see the following article...
--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)

July 5, 2023 at 1:12 pm

#4227547
Reply To: Query performance comparison

This will make some people get all scratchy but I use SQL Profiler... usually with a SPID filter.
--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)

July 5, 2023 at 6:59 am

#4227265
Reply To: Use BCP and a format file to export data to a text file
Jeffrey Williams wrote:
Jeff Moden wrote:
Heh... you're seriously going to export SSNs to a file in an unencrypted manner along with all that nice PII. What company did you say you work for?
Just...
- This reply was modified 3 years, 1 months ago by Jeff Moden.
- This reply was modified 3 years, 1 months ago by Jeff Moden.
--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)
July 5, 2023 at 6:41 am

#4227255
Reply To: Use BCP and a format file to export data to a text file

Heh... you're seriously going to export SSNs to a file in an unencrypted manner along with all that nice PII. What company did you say you work 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)

July 3, 2023 at 11:32 pm

#4226238
Reply To: Column Pivot

kaj wrote:
One comment: I hope the example table isn't an exact replica of your production table, because to use that many columns defined as nvarchar(max) just to be 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)

July 3, 2023 at 6:31 pm

#4226062
Reply To: DELETE with SELECT and join in SQL SERVER

kdaniapam wrote:
Hi
I have resolved the issue and doesn't need your comment or answer. Your comment is too late.
You just got done saying "I tried your suggestion and I have...
--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)

July 3, 2023 at 6:26 pm

#4226040
Reply To: Column Pivot

gjoelson 29755 wrote:
Jeff, Perfect ! thanks so much for the help, I will run with this and see how it goes when moving to the next month.
but this looks great.
Thanks 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)

July 3, 2023 at 5:18 pm

#4226013
Reply To: Column Pivot

p.s. I fogot to do the exclusion if the start of the run is in the month before the end of the run. That's easy to do but let's first...
--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)

July 3, 2023 at 4:22 am

#4225467
Reply To: Column Pivot

Ok... the following provides what you asked for. Give me 3 more Office names and I'll give you what I think they'll ask you for in the future and it'll...
--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)

July 3, 2023 at 4:14 am

#4225465

Viewing 15 posts - 1,336 through 1,350 (of 59,082 total)