Jeff Moden

Forum Replies Created

Viewing 15 posts - 39,796 through 39,810 (of 59,098 total)

RE: Creating Views without Compile

SQL Compare does something in the background to check dependencies. You could probably do something similar but, I have to ask, why don't you want to use SQL Compare...
--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 13, 2010 at 10:43 pm

#1118527
RE: Problem in calculating missing periods

Heh... looks like Lutz was right.
In that case, we can keep this really short...
;
WITH cteAllDates AS
(
SELECT s.Dept, s.Acct, DATEADD(mm,DATEDIFF(mm,0,p.Period),0) AS PeriodStart, p.Period, 0 AS Credit, 0 AS Debit
...
--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 13, 2010 at 10:26 pm

#1118526
RE: Perfect is the Enemy of Good

"Best Practices"... heh... all I'm going to say about that subject is "It Depends". 😉
--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 13, 2010 at 8:36 pm

#1118522
RE: Creating Views without Compile

Ummmm.... so what do you want to do if the underlying tables don't exist?
--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 13, 2010 at 7:03 pm

#1118510
RE: dividing a value by rowcount in fewest statements as possible?

haggisns (2/8/2010)
Hi,
I have the following basic data:
rowid orderid cost
1 1 10
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 13, 2010 at 6:56 pm

#1118509
RE: Problem in calculating missing periods

surya-434952 (2/12/2010)
... i need to insert credit and debit values as '0'
That's a bit of an ambiguous statement as it is... WHERE do you want to insert the zero values?...
--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 13, 2010 at 5:23 pm

#1118504
RE: Problem in calculating missing periods

Lutz,
I'm possibly missing the bubble here... why wouldn't the following two entries be added together for a single "period"?
SELECT '2','2','20090205','200','80' UNION ALL
SELECT '2','2','20090207','200','80'
--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 13, 2010 at 4:13 pm

#1118498
RE: Temp DB Issue

You bet... thanks for the feedback, UT.
--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 13, 2010 at 2:54 pm

#1118495
RE: Where are you?

mark.pleasance (2/11/2010)
Hamilton, Ontario, Canada . . . Born & raised!
http://maps.google.ca/maps?f=q&source=s_q&hl=en&geocode=&q=Hamilton,+Ontario,+Canada&sll=43.256692,-79.869053&sspn=0.832135,1.978912&ie=UTF8&hq=&hnear=Hamilton,+Hamilton+Division,+Ontario&t=h&z=10&iwloc=A
(http://tinyurl.com/yb5py2c)
I'm not a lumberjack, or a Furtrader,
and I don't live in an igloo, or eat blubber or own a dogsled.
And 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 13, 2010 at 2:36 pm

#1118493
RE: Temp DB Issue

As yet another side bar, an "accidental cross join" can also be in the form of what is known as a "Triangular Join". Take a look at the following...
--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 13, 2010 at 12:20 pm

#1118478
RE: Temp DB Issue

UT- (2/12/2010)
Jeff Moden (2/12/2010)
Accidental cross joins are actually quite common and are a typical cause of TempDB going nuts.
Thanks Jeff, can you please give me an example of "Accidental" 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 13, 2010 at 12:05 pm

#1118477
RE: Single column to multiple columns

I haven't tried his, but Steve's looks like it'll work just fine (as he says, not in 2000), as well. He does it by building an on-the-fly Tally table...
--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 13, 2010 at 10:37 am

#1118468
RE: query question

Jakke (2/12/2010)
Hi Markus
The "Quirky Update" did the trick 🙂
I tested it against about 20000 records and the outcome was satisfying and quite fast as well.
Next, i will put it on...
--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 12, 2010 at 5:43 pm

#1118375
RE: query question

Jakke (2/12/2010)
Hi Markus
The "Quirky Update" did the trick 🙂
I tested it against about 20000 records and the outcome was satisfying and quite fast as well.
Next, i will put it on...
--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 12, 2010 at 5:39 pm

#1118372
RE: strange query to get data

AndrewSQLDBA (2/12/2010)
I think that I have figured out a way by using a temp table
Thanks
Andrew SQLDBA
Cool... can you share it please?
--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 12, 2010 at 5:32 pm

#1118371

Viewing 15 posts - 39,796 through 39,810 (of 59,098 total)