Jeff Moden

Forum Replies Created

Viewing 15 posts - 46,456 through 46,470 (of 59,101 total)

RE: Query to Calculate Cumulative Salary

vbandlamudi (12/23/2008)
vbandlamudi (12/23/2008)
--------------------------------------------------------------------------------
Correct Answer
Select id,name, sal, cumsal=
(select sum(t1.sal)
from employee_san t1
where t1.id <= t.id)
from employee_san t
the above query took 16 seconds to process 27170 rows
and it took 51...
--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)

December 23, 2008 at 5:42 am

#916711
RE: Stopping Loop when Using tally table loop for date ranges in stored procedure

I agree... tell us what the rest of the problem is and maybe use the link in my signature to find out how to post some test data. There's...
--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)

December 23, 2008 at 5:27 am

#916704
RE: The Set-Based Limit

Steve Jones - Editor (12/22/2008)
Indeed, my guess is Jeff if handcrafting something right now in the winter snows of MI.
Heh... just waiting for the specs on the 23 character password/pin....
--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)

December 23, 2008 at 5:18 am

#916698
RE: The Set-Based Limit

peter (12/23/2008)
Jeff is quiet about my posts - too quiet....is a storm coming?
LOL
Nah... not ignoring you, Peter. Functions do normally contain some unneccessary overhead. Normally... there are exceptions....
--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)

December 23, 2008 at 5:16 am

#916696
RE: Aggregate bit operator in TSQL

I agree... CASE is almost as fast and bit easier to read.
--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)

December 23, 2008 at 5:11 am

#916691
RE: Query to Calculate Cumulative Salary

r.hensbergen (12/23/2008)
UPDATE #Results
SET @PrevCum_Sal = Cum_Sal = Sal + @PrevCum_Sal,
@PrevName = Name --Just an "anchor"
...
--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)

December 23, 2008 at 5:07 am

#916688
RE: Query to Calculate Cumulative Salary

Santhosh (12/23/2008)
vbandlamudi (12/23/2008)
Correct Answer
Select id,name, sal, cumsal=
(select sum(t1.sal)
from employee_san t1
where t1.id <= t.id)
from employee_san t
This works fine.
But the salary is not ORDERed BY ASC
It works fine for only...
--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)

December 23, 2008 at 5:04 am

#916683
RE: Query to Calculate Cumulative Salary

vbandlamudi (12/23/2008)
Correct Answer
Select id,name, sal, cumsal=
(select sum(t1.sal)
from employee_san t1
where t1.id <= t.id)
from employee_san t
Ummmm... No... that's NOT the correct answer. That makes a triangular join. Try...
--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)

December 23, 2008 at 5:00 am

#916678
RE: Aggregate bit operator in TSQL

Heh... and it works so well for these types of bitwise ops.
--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)

December 22, 2008 at 5:18 pm

#916486
RE: Are the posted questions getting worse?

Ya gotta love it when the ops cooperate.
--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)

December 22, 2008 at 5:17 pm

#916485
RE: Best Practice: Do not use wireless connections

jac (12/22/2008)
I agree that 99.9% of administrative duties can be safely used via a wireless connection, but I think if I have to run that script again (the other 0.1...
--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)

December 22, 2008 at 5:11 pm

#916483
RE: GROUP BY, then COMPUTE

Using Luke's good code example, you can label to "sum", as well...
--create test table
CREATE TABLE #test (
id INT,
amount MONEY)
--insert sample data
INSERT INTO [#test]
SELECT 1,10.00
UNION ALL
SELECT 1,20.00
UNION ALL
SELECT 2,10.00
SELECT CASE...
--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)

December 22, 2008 at 4:38 pm

#916470
RE: Best Practice: Do not use wireless connections

That's good advice if you're going to do something like that... but, most maintanence doesn't require a single connection like that. Not sure that's enough justification to give up...
--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)

December 22, 2008 at 4:32 pm

#916468
RE: Updating Identity Column

kashifyaqoob (12/22/2008)
It is really helpfull for me. Thanks.
Be very careful.... anytime you find the need to update or insert your own numbers into an IDENTITY column, you're probably doing something...
--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)

December 22, 2008 at 4:28 pm

#916466
RE: Updating Identity Column

Japie Botma (10/21/2005)
Assuming it is not a live table and you know what and why you want to do this, you can remove the identity from the column, update 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)

December 22, 2008 at 4:26 pm

#916465

Viewing 15 posts - 46,456 through 46,470 (of 59,101 total)