Jeff Moden

Forum Replies Created

Viewing 15 posts - 27,406 through 27,420 (of 59,101 total)

RE: UDF Help

TheSQLGuru (2/27/2013)
You may THINK you want to do a Scalar UDF, but you probably don't. I wrote a chapter for the SQL Server MVP Deep Dives 2 book entitled...
--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)

February 27, 2013 at 6:54 am

#1591536
RE: Tally OH! An Improved SQL 8K “CSV Splitter” Function

ChrisM@Work (2/27/2013)
mister.magoo (2/27/2013)
ChrisM@Work (2/27/2013)
...
About once a week, mate. Now I'm an old git I can blame it on declining memory. When I was a sprightly youngster it was more embarrassing.
I...
--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)

February 27, 2013 at 5:40 am

#1591503
RE: Spilt List Function

eagb (2/14/2013)
Thank you that worked perfectly!
Any chance of us getting a peek at your split function?
--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)

February 26, 2013 at 11:26 pm

#1591394
RE: Loading T-SQL Statements from a database

Kevin.roberts25 (2/20/2013)
Ok I have 26 reports to create, the end result would look something like this
Description, Period1, Period2, Period3
Average Sales, 25, 45, 67
Totals Team 1, 53, 74, 27
Totals Team 2,...
--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)

February 26, 2013 at 9:54 pm

#1591378
RE: How to compare parent child data in SQL

AhmetEmre90 (2/20/2013)
Hi;
I have two SQL tables like below;
T1: relationID, meterID, parentID, childID
T2: dataID, meterID, date, amount
Sample data of tables;
T1 ...
--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)

February 26, 2013 at 9:49 pm

#1591377
RE: T SQL statement issue

chandrakant_gaurav (2/21/2013)
Its embarassing :blush: . Thank you!!
If you'd like to learn more about this "CROSSTAB" technique (which is also known as a PIVOT), please see the following articles...
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
--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)

February 26, 2013 at 9:43 pm

#1591376
RE: Can't Kill SPID “Transaction Rollback in Progress”

There are no easy methods to kill the damned things. The 0% rollback is actually a documented problem and, IIRC, has an open/active CONNECT item against it.
There are...
--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)

February 26, 2013 at 9:00 pm

#1591371
RE: change a result into one record

dwain.c (2/26/2013)
I did have to make an assumption (the 0 in the second row of the set up data) as the OP only provided 4 data columns in that row...
--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)

February 26, 2013 at 8:44 pm

#1591369
RE: change a result into one record

dwain.c (2/26/2013)
Like this?
Looks like it. Heh... we make a hell of a tag team. I'll flush out the requiremments, you write the 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)

February 26, 2013 at 8:29 pm

#1591366
RE: get the first and last day of any Year/Month

Just to emphasize the performance differences between using a string conversion or not for date conversions, let's do a little test... a race, really.
Here's the test data. Since all...
--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)

February 26, 2013 at 8:13 pm

#1591362
RE: get the first and last day of any Year/Month

Michael Valentine Jones (2/26/2013)
Jeff Moden (2/25/2013)
You can also cheat for performance with a little integer math. The 22800 is the year (1900*12). The "0" in the BOM forumula...
--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)

February 26, 2013 at 6:20 pm

#1591356
RE: change a result into one record

dmarz96 (2/26/2013)
That is not going to work for me.
So, what is it that you actually want? which of the two row do you want returned or do you want...
--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)

February 26, 2013 at 1:35 pm

#1591307
RE: Size of temp db

mah_j (2/26/2013)
Hi
Is there any effect on performance if I empty the temp db by restarting SQL server,and increase the size of it and also outo growth,or will be worse?
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)

February 26, 2013 at 12:50 pm

#1591296
RE: get the first and last day of any Year/Month

candide (2/26/2013)
Hi,
Jeff's solution works great:-P
exactly what I needed
thanx
Thank you for the kind feedback. I just want to make sure because you're the one that will have to support it....
--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)

February 26, 2013 at 8:01 am

#1591199
RE: get the first and last day of any Year/Month

ben.brugman (2/26/2013)
And another alternative solution, based on 'strings'
declare @Month int = 2, @Year int = 2013
declare @ThisDate datetime = convert(varchar(8),@year*10000+@month*100+01)
select CONVERT(varchar(7),@thisdate,121)+'-01'
select CONVERT(varchar(7),dateadd(mm,1,@thisdate),121)+'-01'
ben brugman
Just be aware that string conversions...
--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)

February 26, 2013 at 7:59 am

#1591197

Viewing 15 posts - 27,406 through 27,420 (of 59,101 total)