Jeff Moden

Forum Replies Created

Viewing 15 posts - 21,046 through 21,060 (of 59,098 total)

RE: Datepart split week over month issue

acrutchley (4/16/2015)
Hi Jeff,
I did try the ISO_WEEK option but I was still getting overlaps with the months.... which caused duplicate week number entries because I was grouping by the month...
--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 16, 2015 at 3:45 pm

#1791504
RE: Datepart split week over month issue

acrutchley (4/16/2015)
Hi Get me?,
Many thanks for your response, I agree the calendar approach would be the best. However, due to time constraints I have managed to get round the issue...
--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 16, 2015 at 8:41 am

#1791369
RE: Copying data between tables

Using your original idea, just add a bit of code to detect if there's an identity column or not. If there is, then use the SET IDENTITY_INSERT command to...
--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 16, 2015 at 8:28 am

#1791362
RE: Dropping NON Clustered Indexes

Only the clustered index has anything to do with the actual data in the table. NCIs are their own ball of wax and won't affect any data.
--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 15, 2015 at 10:31 pm

#1791238
RE: SQL DBA Scripts for the Beginer to professional

kiran.sharc (4/15/2015)
Hi Folks,
I am new to SQL DBA world and looking for the SQL DBA scripts which frequently use. My apologies which...
--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 15, 2015 at 10:19 pm

#1791237
RE: Return Serial Number As Per change Records

itsgaurav (4/15/2015)
Sir
I have a five rows such AS
pd_id pd_name
11 Prod-1
22 ...
--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 15, 2015 at 9:43 pm

#1791234
RE: QA for Data

We have stringent rules at work for the promotion of all code. It doesn't change at all just because there's a "data emergency" of the nature you speak except...
--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 15, 2015 at 9:37 pm

#1791233
RE: string query help

ztoddw (4/14/2015)
This way is probably more efficient-
declare @STRING varchar(1000);set @STRING='100010111100010001';
select pos=ltrim(str(n))+'-'+ltrim(str(len(sub)-len(ltrim(replace(replace(sub,' ','~'),'0',' ')))+n-1))
from (select n=number, sub=substring(@STRING,number,len(@STRING))
from master.dbo.spt_values where type='P'
and (number between 1 and len(@STRING))) t
where (n=1 or substring(@STRING,n-1,1)<>'0')
and...
--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 14, 2015 at 11:11 pm

#1790990
RE: Separate drives for mdf / ldf on same storage

Shifting gears a bit, I really miss HS-LASD (Hot Swappable Large Arrays of Small Disks) with a really good controller. Not only could you split MDF/LDF and TempDB, 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 14, 2015 at 12:09 pm

#1790928
RE: Removing consecutive characters from the middle of a string

sKreetz! (4/14/2015)
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle 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 14, 2015 at 11:53 am

#1790926
RE: Two max of two peaks.

Agreed. That's why I asked for some real test data. 😀
--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 14, 2015 at 11:24 am

#1790919
RE: DELETING AN ENTRY

To add to what Sean posted, you should never do "just" a DELETE because you don't know what to expect. You should always do a SELECT first to see...
--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 14, 2015 at 10:59 am

#1790911
RE: Using Case Within PIVOT portion of Crosstab query

skilly2 (4/14/2015)
Jeff Moden (4/13/2015)
Stop using Pivot altogether. Just use a CROSSTAB, which is driven by CASE statements to begin with. See the following article for a primer.
http://www.sqlservercentral.com/articles/T-SQL/63681/
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)

April 14, 2015 at 9:29 am

#1790856
RE: Two max of two peaks.

Enumerate each value with ROW_NUMBER() and do a +1 offset self join. Then look for where the "lo" value is greater than the "hi" value (the +1 rownumber). ...
--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 14, 2015 at 8:58 am

#1790843
RE: Send Database Mail only once

Heh... as a bit of a sidebar, when there's a failure, I "SPAM" folks every 2 minutes to establish a sense of urgency. 😀
--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 14, 2015 at 8:23 am

#1790820

Viewing 15 posts - 21,046 through 21,060 (of 59,098 total)