Jeff Moden

Forum Replies Created

Viewing 15 posts - 24,511 through 24,525 (of 59,099 total)

RE: using count with select case

Basically, you're trying to do formatting and calculations at the same time and that's causing a really nasty sort if you look at the execution plan not to mention 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)

February 2, 2014 at 10:11 pm

#1685386
Reply To: Finding “Active” Rows for the Previous Month (SQL Spackle)

Thanks for the feedback and the Tweet, Dwaine.
I guess enough people have made the mistake of allowing NULLable StartDates (seriously??? :blink:) and/or EndDates that it warrants a follow up article...
--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 2, 2014 at 9:25 pm

#1685384
RE: View or function is not updatable because the modification affects multiple base tables

Imran Nazir (2/1/2014)
You need to use INSTEAD OF UPDATE trigger as well.
I realize that you and I are responding to an 8 year old post but I wouldn't do this...
--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 2, 2014 at 9:00 pm

#1685383
RE: Need to query db to return all instances for Canada. (Country, State, City)

peerless (2/1/2014)
What should I do?
I'm with Chris on this one. Stop making people try to eat a rolling donut by changing your code from post to post. 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)

February 2, 2014 at 8:44 pm

#1685382
RE: How to convert char 10/2013 to use the filter >=

Hmmmm... there IS a way to do this in a SARGable fashion (although fixing the column is still the best idea). I just need to know how Jan thru...
--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 2, 2014 at 8:31 pm

#1685380
RE: How to convert char 10/2013 to use the filter >=

Golden_eye (2/2/2014)
Hello,
I have a probllem with one part of my script which is:
where BillingMonth = '10/2013'
It is working fine now, but I need to change it to
where BillingMonth >=...
--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 2, 2014 at 8:26 pm

#1685379
RE: Exclude Weekends and Holidays

You're going to continue to have problems with date calculations for these types of things. My recommendation is to save yourself a bit of headache and create a Calendar...
--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 2, 2014 at 7:47 pm

#1685376
RE: FINDING THE DATE WHICH IS NEAREST TO CURRENT DATE

pietlinden (2/2/2014)
What you want is probably something like
SELECT TOP 1 SomeDateColumn
FROM MyTable
WHERE SomeDateColumn<=GETDATE();
GETDATE() returns the current date...
I believe that's missing an ORDER BY.
--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 2, 2014 at 5:45 pm

#1685369
RE: Query to get the CPU Usage,memory usage details of server

sharadkkw (1/30/2014)
Can someone help me in providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will...
--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 2, 2014 at 4:10 pm

#1685364
RE: Query to get the CPU Usage,memory usage details of server

sqlsniper (2/2/2014)
You could also take a look at Windowstooling, like perfmon and the typeperf tooling.
Perfect! How do you store that in a table in SQL Server? 😉
--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 2, 2014 at 3:44 pm

#1685361
RE: how to deploy single ssis package in ssis 2012 .

Koen Verbeeck (2/2/2014)
asranantha (2/2/2014)
Hi friends, in my project having 10 packages with shared connection used.here i need deploy only single ssis package please provide how to solve this issuse ,any...
--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 2, 2014 at 3:42 pm

#1685360
RE: Finding “Active” Rows for the Previous Month (SQL Spackle)

davoscollective (2/2/2014)
Just thinking a bit further, is the clustered index enough to really take advantage of those end and start date intersections? You bring up the non-Sargable point, I'm just...
--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 2, 2014 at 12:32 pm

#1685357
RE: Help finding an old post

sqldriver (2/1/2014)
Yes, Jeff, now give me something to spend all this DBA money on that wasn't bottled and corked by a guy in a kilt.
BWAAA-HAAAA!!!!! I know that guy!...
--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 2, 2014 at 8:52 am

#1685346
RE: Help finding an old post

Michael Meierruth (2/2/2014)
Jeff Moden (2/1/2014)
There is a method that'll blow the doors off of even LAG but a lot of people are afraid of it.
Thus are you referrring to CROSS...
--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 2, 2014 at 8:46 am

#1685345
RE: group by column as sub header

oliver.morris (1/28/2014)
Hi,
Thanks for your help in advance, much appreciated.
I have a table which I would like to format like so:
From this:
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[header] [varchar](50) NULL,
[citation] [varchar](200)...
--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 1, 2014 at 9:16 pm

#1685330

Viewing 15 posts - 24,511 through 24,525 (of 59,099 total)