Jeff Moden

Forum Replies Created

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

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
RE: Is Networking Important?

Steve Jones - Editor (9/23/2009)
Don't know. That sounds like employment for life! Might be THE place to work.
BWAA-HAAA!!! Hadn't thought about it THAT way! You could be right!
--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 11:41 am

#1057674
RE: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Thanks Bradley and JJ. Just in case you're interested, "Part 2" can be found at the following URL:
http://www.sqlservercentral.com/articles/Crosstab/65048/
It covers how to make/automate dynamic crosstabs for reporting purposes.
--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 9:25 am

#1057592
RE: The Dynamic Tally or Numbers Table

marco-870908 (9/22/2009)
Anyway it is my firm belief that such protections should be validated once where user-input comes into the system
Roger that! One of my favorite sayings is that "If...
--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 22, 2009 at 9:17 pm

#1057264
RE: The Dynamic Tally or Numbers Table

Lots'o good code coming out of this discussion... Haven't tested that last one for speed on the same machine that I've been testing on, but it'll gen more numbers than...
--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 22, 2009 at 9:14 pm

#1057263

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