Jeff Moden

Forum Replies Created

Viewing 15 posts - 6,991 through 7,005 (of 59,091 total)

Reply To: Query single column data into three columns

Nothing like stating the obvious, Joe.
--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)

June 3, 2020 at 4:29 pm

#3760174
Reply To: Custom Pivot Table
SQL Espo wrote:
My solution was
```
select AllergyName,
MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleStart else 0 end) AS [LowStart],
MAX(CASE WHEN ScaleValue = 'Low' THEN ScaleEnd else 0 end) AS [LowEnd],
MAX(CASE...
```
--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)
June 3, 2020 at 2:47 pm

#3760129
Reply To: Are the posted questions getting worse?

Lordy... there's a "bot" running with the handle of "Stanley" and it's posting spam every 5 minutes. Between that and another bot that was active only for a while, I'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)

June 3, 2020 at 4:32 am

#3759808
Reply To: Query single column data into three columns

stephen.aa wrote:
I played around with the query a little and added percentage columns.
The math works, but it seems to default to six decimal places. 100% - 100.000000. Is there 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)

June 3, 2020 at 1:53 am

#3759708
Reply To: Custom Pivot Table

Mine is similar but I took advantage of IIF. It still renders out as CASE expressions behind the scenes so no performance gain or anything like that. I also think...
--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)

June 3, 2020 at 12:46 am

#3759681
Reply To: Integer convert to percent

nymets86 wrote:
Your suggestion worked. Thanks. I am just getting the hang of this but will post the working formula even though there is probably a better way to do this.
CONVERT(DECIMAL...
--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)

June 2, 2020 at 10:07 pm

#3759615
Reply To: Custom Pivot Table

SQL Espo wrote:
Thank you both so much! I was trying to be cute and figure out how to use the PIVOT operation, but going back to the basics and using 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)

June 2, 2020 at 10:04 pm

#3759614
Reply To: SQL account for restoring database

These probably shouldn't be "scripts". They should be stored procedures (possibly in a permanent "utility" database) and then you can do what Scott has suggested to keep things safe.

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

June 2, 2020 at 5:49 pm

#3759539
Reply To: Integer convert to percent

nymets86 wrote:
Having trouble with the below formula. Technically I want to display it by a percentage with two decimal points but when I run the below I get it with...
--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)

June 2, 2020 at 5:40 pm

#3759537
Reply To: Index fill factor

ScottPletcher wrote:
MS has actually provided complex formulas for years. But it's so difficult to come up with accurate numbers to go into the formulas, and the result is only as...
--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)

June 2, 2020 at 4:48 pm

#3759432
Reply To: Inserting an incremental range of decimal numbers into a table

My example was one of many. My point is instead of using exact points to try to hit an inclusive BETWEEN, use ranges that have an exact start and 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)

June 2, 2020 at 12:48 pm

#3759346
Reply To: Index fill factor

ScottPletcher wrote:
As to reorganization, I believe it's still best practice to use reorg for columnstore tables (I'm on SQL 2016). Full rowgroups won't be affected at all by this process,...
--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)

June 2, 2020 at 1:51 am

#3759260
Reply To: Index fill factor

ScottPletcher wrote:
There's no magic formula to tell you the best fillfactor for 3,000 indexes. Or for 10 indexes, for that matter.
Actually, for non partitioned rowstore indexes, there is (it takes...
--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)

June 2, 2020 at 1:21 am

#3759258
Reply To: Estimate a count

Ok... now that I agree with. Thanks for your time, Rune.
--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)

June 1, 2020 at 6:57 pm

#3759202
Reply To: Inserting an incremental range of decimal numbers into a table

So why not make it truly easy on yourself and use a "Open/Closed" bit of code and setup. For example, instead of say something like 2.01 to 3.oo as 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)

June 1, 2020 at 6:55 pm

#3759201

Viewing 15 posts - 6,991 through 7,005 (of 59,091 total)