Jeff Moden

Forum Replies Created

Viewing 15 posts - 39,766 through 39,780 (of 59,072 total)

RE: How do I pass a value in a field to the next row where the field is null in one or more in the following rows

lmu92 (2/14/2010)
@ Jeff: at a second thought I kinda disagree with your clustered index...
Shouldn't it be on (EmployeeID, SalaryDate) instead of RowID?
What would happen with rows like:
SELECT 1, 12, '20050101',...
--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 14, 2010 at 6:52 pm

#1118642
RE: How do I pass a value in a field to the next row where the field is null in one or more in the following rows

Do it all at once and without a triangular join...
CREATE CLUSTERED INDEX IX_SalaryHistory_RowID
ON #SalaryHistory (RowID)
DECLARE @PrevSalaryAmount MONEY
UPDATE #SalaryHistory
SET @PrevSalaryAmount...
--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 14, 2010 at 5:30 pm

#1118636
RE: Problem in calculating missing periods

As a side bar, I probably wouldn't ever have a period table based on the last whole day of the month... if "times" ever creep into the statement table, then...
--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 14, 2010 at 10:28 am

#1118599
RE: Problem in calculating missing periods

lmu92 (2/14/2010)
Looks like I'm getting too addicted to CTEs... 😛
No... don't think that for a minute. The CTE's you wrote are a great exhibition of "Divide'n'Conquer". They run...
--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 14, 2010 at 9:40 am

#1118588
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

Viewing 15 posts - 39,766 through 39,780 (of 59,072 total)