Jeff Moden

Forum Replies Created

Viewing 15 posts - 2,791 through 2,805 (of 59,098 total)

Reply To: Pivot columns to get 0 /1 flag columns

To get the nice-warm-fuzzies on that, though, start a new session and run the following code... It should run just fine in the new session where you haven't set 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)

September 11, 2022 at 10:58 pm

#4086432
Reply To: Pivot columns to get 0 /1 flag columns

masterelaichi wrote:
Jeff Moden wrote:
Jeffrey Williams wrote:
Phil Parkin wrote:
IF you add the statement
SET DATEFORMAT DMY;
before you attempt the INSERT, the error will go away and the INSERT runs successfully.
Although this is an option -...
--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)

September 11, 2022 at 10:51 pm

#4086431
Reply To: Pivot columns to get 0 /1 flag columns

masterelaichi wrote:
Unless you meant conversion at the datasource ?
As Jeffrey Williams is implying, that would be the best solution.
--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)

September 11, 2022 at 6:35 pm

#4086410
Reply To: Pivot columns to get 0 /1 flag columns

Jeffrey Williams wrote:
Phil Parkin wrote:
IF you add the statement
SET DATEFORMAT DMY;
before you attempt the INSERT, the error will go away and the INSERT runs successfully.
Although this is an option - don't do...
--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)

September 11, 2022 at 6:33 pm

#4086409
Reply To: query take long time when execute it so how to solve issue ?
ahmed_elbarbary.2010 wrote:
```
SELECT f1_AllCount = COUNT(f1.PartId)
,f1_UnqCount = COUNT(DISTINCT f1.PartID)
FROM Technology.Receipe ft
JOIN ExtractReports.dbo.TechnologyPlPartsFeaturValuesOrg f1 ON ft.featureid = f1.featureid
WHERE ft.operatorid = 1;
```
result is
```
f1_AllCount         f1_UnqCount
124140 ...
```
--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)
September 11, 2022 at 6:29 pm

#4086400
Reply To: Pivot columns to get 0 /1 flag columns

masterelaichi wrote:
jcelko212's post got me thinking
I tried to change the datatype of the paydate column to date by using the alter command but ended up getting this error "Conversion failed...
--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)

September 11, 2022 at 5:03 am

#4086295
Reply To: query take long time when execute it so how to solve issue ?

ahmed_elbarbary.2010 wrote:
so how to use cross apply as you write below
The optimizer attempts to SORT 65 million rows after a merge join. Yeeeesh. One way to re-write the query 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)

September 11, 2022 at 4:47 am

#4086290
Reply To: Get AD data and load to SQL

I've found that I really don't need all of that stuff for SQL Server. xp_LoginInfo and sp_validatelogins usually provide what I need. If not, then I go to the Windows...
--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)

September 10, 2022 at 9:50 pm

#4086256
Reply To: Pivot columns to get 0 /1 flag columns

jcelko212 32090 wrote:
Please learn what a table is. First, we need to fix your non-table.
CREATE TABLE Expenses (emp_id CHAR(10) NOT NULL, pay_date DATE NOT NULL, -- read ISO-8601 formats expense_type VARCHAR(30)...
--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)

September 10, 2022 at 9:09 pm

#4086249
Reply To: query take long time when execute it so how to solve issue ?
I suspect a couple of accidental CROSS JOINs is the issue. Please run the following code and post the results.
```
 SELECT  f1_AllCount = COUNT(f1.PartId)
  ...
```
--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)
September 10, 2022 at 6:16 pm

#4086227
Reply To: assigning value from sproc inside another sproc

Ah... I see what you mean. Guess I'll have to look up how to do a "StaNine" and see if I can make a function or a GP proc 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)

September 9, 2022 at 9:03 pm

#4086043
Reply To: assigning value from sproc inside another sproc

mjohnson 71479 wrote:
That procedure just calls some individual functions used to get each Stanine.
If you are looking for a formula for determining the bellcurve, we pay for studies to be done...
--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)

September 9, 2022 at 8:26 pm

#4086033
Reply To: Stored procedure getting blocked and deadlocked

SQL Guy 1 wrote:
It has one big select statement that besides tables also joins one view and one table-valued function. It does not have explicit transaction.
But what I can do about blockings?...
--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)

September 9, 2022 at 8:13 pm

#4086031
Reply To: get column names and values in each row in SQL Server

dhanekulakalyan wrote:
Thank you Phil for this idea, i will test and let you know if this is working.
It would still be nice to know the need for this particular format. ...
--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)

September 9, 2022 at 8:08 pm

#4086029
Reply To: assigning value from sproc inside another sproc

mjohnson 71479 wrote:
Excellent !!! Thank you very much!!
Any chance of us getting a peek at your "StaNine" stored procedure?
--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)

September 9, 2022 at 8:06 pm

#4086028

Viewing 15 posts - 2,791 through 2,805 (of 59,098 total)