Jeff Moden

Forum Replies Created

Viewing 15 posts - 41,821 through 41,835 (of 59,102 total)

RE: Calculating the Number of Business Hours Passed Since a Point of Tme

I ran your code from the previous post... here it is, again...
select getdate()
select dbo.CalcTimeBetweenTwoDates('2004-11-16 15:30', '2007-11-21 07:31')
select getdate()
Here's what I get for an error message from that code...
Msg 4121, Level...
--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)

September 24, 2009 at 5:51 pm

#1058321
RE: Calculating the Number of Business Hours Passed Since a Point of Tme

Thanks for the feedback, Mazharuddin... I'll take a look.
--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)

September 24, 2009 at 5:41 pm

#1058319
RE: Very slow SQL query.

David Kranes (9/24/2009)
Here is the DDL. Thank you.
There real problem is that the "Active_Address_ID" has no index on it nor is it a part of any other index, yet...
--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)

September 24, 2009 at 5:19 pm

#1058315
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Almost forgot... if you're just playing with a Tally table, then TempDB is fine. If you want one for a production table, then Hugo is correct there... TempDB is...
--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)

September 24, 2009 at 5:02 pm

#1058313
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

DavidL (9/24/2009)
A reasonably sized (12000 rows) tally table took care of
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
--===== Find the min and max dates in the range of data
SELECT...
--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)

September 24, 2009 at 4:59 pm

#1058311
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Hugo Kornelis (9/24/2009)
The error occurs because SQL Server has to calculate the result of the DATEADD expression for each row in the Tally table, and with your extra huge amount...
--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)

September 24, 2009 at 4:57 pm

#1058310
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Hugo Kornelis (9/24/2009)
Hi David,
Based on visual inspection of your code, I fail to see any way that this code could cause "gaps" in the tally table. I think what you...
--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)

September 24, 2009 at 4:52 pm

#1058308
RE: Adding varchar (as Hours:Minutes:Seconds) to a datetime field

Lynn Pettis (9/23/2009)
Also, the DATE data type does make sense in those areas where time is not needed, such as DateHired, DateTerminated. Who cares what the time is there.
Heh......
--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)

September 24, 2009 at 2:34 pm

#1058277
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

DavidL (9/24/2009)
Additionally, cutting and pasting your last example into my ssms and running it as is
--===== Presets
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '2008-01-01 06:00',
...
--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)

September 24, 2009 at 2:25 pm

#1058272
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

I also need to know how many rows you actually have in your Tally table, 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)

September 24, 2009 at 2:19 pm

#1058268
RE: The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
--===== Find the min and max dates in the range of data
SELECT @DateStart = MIN(SalesDate),
@DateEnd...
--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)

September 24, 2009 at 2:18 pm

#1058267
RE: how can i show date column headers in a matrix report even when there are no rows for the specified date

Gosta Munktell (9/23/2009)Quick and dirty?
Heh... very... it's quite easy to do exactly the same thing in T-SQL.
--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)

September 23, 2009 at 4:14 pm

#1057806
RE: Very slow SQL query.

David Kranes (9/23/2009)
Sorry for the delay in providing this. I have attached the DDL.
Where? Not on that post...
--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)

September 23, 2009 at 4:11 pm

#1057805
RE: Adding varchar (as Hours:Minutes:Seconds) to a datetime field

amos-870870 (9/23/2009)They split the time the way they did I believe because it was the straight forward way to accomplish what they wanted to do with their front end application....
--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)

September 23, 2009 at 4:10 pm

#1057804
RE: The Dynamic Tally or Numbers Table

RBarryYoung (9/23/2009)
Great article, Lynn! I might finally be ready to stop cross-joining master..syscolumns. 🙂
I already quit... heh... I use Master.sys.All_Columns now. 😛
--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)

September 23, 2009 at 4:02 pm

#1057801

Viewing 15 posts - 41,821 through 41,835 (of 59,102 total)