Jeff Moden

Forum Replies Created

Viewing 15 posts - 7,321 through 7,335 (of 59,091 total)

Reply To: Case when with a Pivot

Since you attempt at code and what you posted as your desired final output have several difference between the two, I split the difference and can derived the following code. ...
--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)

April 10, 2020 at 8:57 pm

#3742117
Reply To: Select return no records

A better idea is to use the correct datatype. If the column is supposed to only handle digits, then changing the datatype of the column to be an INTEGER 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)

April 10, 2020 at 6:45 pm

#3742106
Reply To: Query time after specified date

jham55 wrote:
I need help with a query. If I have a list of people each with a service date and I want to sum their spend one year and two...
--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)

April 10, 2020 at 6:33 pm

#3742097
Reply To: Convert date value

Recreating such a thing in yet another "string format" would go against my grain. Why not convert it to a proper DATE or DATETIME datatype?

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

April 10, 2020 at 4:11 pm

#3742040
Reply To: Limiting Specific User Connections

If you have the enterprise edition, you could use the Resource Governor. I've not done a deep read on the trigger you wrote but that would also work.
You should give...
--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)

April 10, 2020 at 4:08 pm

#3742039
Reply To: Slow Query when add 1 additional field

detanto wrote:
dear all,
i have simple query that related several tables which 1 table have millions of rows. i have created index on each table that i will relate to master...
--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)

April 10, 2020 at 1:31 pm

#3741986
Reply To: Select return no records

Is it a leading space or a leading TAB character?
--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)

April 9, 2020 at 9:48 pm

#3741901
Reply To: Saving integers as varchar so that I can choose '' (empty) as content

Joe, if the indexing is setup correctly, wrapping the ENDDATE in a COALESCE can cause a range scan (at best) instead of a SEEK because it makes that part 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)

April 9, 2020 at 7:07 pm

#3741866
Reply To: bcp format file and import fixed width text file

frederico_fonseca wrote:
this is based on the file he supplied on his last post. https://www.sqlservercentral.com/wp-content/uploads/2020/04/PRV414WSW_2.txt
Ah... gotcha... I missed that he reposted. Thank you. I'll take a look at this after work...
--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)

April 9, 2020 at 6:11 pm

#3741850
Reply To: bcp format file and import fixed width text file
frederico_fonseca wrote:
I think the record layout is correct - its just that column "provider name" should be further divided into LastName, FirstName, Middle Initial (probably)
```
if object_id('tempdb..#data') is...
```
--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)
April 9, 2020 at 6:01 pm

#3741847
Reply To: bcp format file and import fixed width text file

smattiko83 wrote:
I've attached a sample text and the import specs. Thanks!
Ok... again, there's something wrong with the sample data you provided. Here's the data using the fixed width font 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)

April 9, 2020 at 4:36 pm

#3741825
Reply To: The Coping Thread

My only trouble is that management feels compelled to force engagement between people with virtual lunches, virtual happy hours, group puzzle solving, sharing workspace pictures, sharing pictures of 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)

April 9, 2020 at 4:12 pm

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

To be sure, I was just kidding about Redgate. It was pretty obvious which company you were talking about. As for Grant... Kind of like real brothers (we both survived...
--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)

April 9, 2020 at 3:27 pm

#3741802
Reply To: Saving integers as varchar so that I can choose '' (empty) as content

Jonathan AC Roberts wrote:
Jeff Moden wrote:
Jonathan AC Roberts wrote:
Jeff Moden wrote:
Jonathan AC Roberts wrote:
Ralph Kimball has a method of not having any nulls in dimension tables. Instead, on the dimension table there are rows with negative keys: -1...
--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)

April 9, 2020 at 2:15 pm

#3741782
Reply To: Partitioning table techniques which one is best way?

Saravanan_tvr wrote:
I am sorry - >5GB and predicted partition key which we have configured in configuration table.
I have to agree with Frederico... 5GB is very small and not worth 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)

April 9, 2020 at 2:10 pm

#3741780

Viewing 15 posts - 7,321 through 7,335 (of 59,091 total)