Jeff Moden

Forum Replies Created

Viewing 15 posts - 30,316 through 30,330 (of 59,101 total)

RE: pivot by year using datetime column

polkadot (4/22/2012)
Jeff, can I use cross tabs (or pivot) to aggregate by both Year and Quarter horizontally and by application vertically?
I would say that it would create such a wide...
--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)

April 22, 2012 at 11:51 pm

#1477281
RE: HIERARCHYID, Select data in topological order but also order by name within direct subordinates

Let it be known right up front that I don't use the HierarchyID datatype because I prefer other methods. That, notwithstanding, the way to do the sort you want...
--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)

April 22, 2012 at 5:58 pm

#1477228
RE: a very COMPLEX aggregation query

I have to ask... does "RESOLVED" and "CLOSED" actually mean the same thing? Legally speaking, I would think not.
--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)

April 22, 2012 at 5:42 pm

#1477227
RE: SQL Server 2012 - Column Store Index

I have to ask... since you have to drop and recreate the ColumnStore Index to update the underlying tables, does anyone have any specs on how long it takes to...
--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)

April 22, 2012 at 4:49 pm

#1477220
RE: Huge amount of index_size and unused in syscolpars

Yowch! What on this good green Earth made you look for such a thing? I don't have an answer but I'd be interested in an answer on this...
--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)

April 22, 2012 at 4:38 pm

#1477219
RE: pivot by year using datetime column

polkadot (4/22/2012)
There was only one problem. I was trying to use YEAR function within the PIVOT operator. I can get the same results with count(workitem_id) as in:
select
application
, [2010]
,...
--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)

April 22, 2012 at 4:31 pm

#1477218
RE: pivot against two different columns

You might want to consider the alternative if, for nothing else, reasons of performance. Please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/63681/
--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)

April 22, 2012 at 4:28 pm

#1477217
RE: Modified DateTimeStamp - No Default on record creation

capn.hector (4/10/2012)
i to prefer to eleminate as many nulls in the data as possible. however it has to make sense. a last modified date IMHO is the inserted...
--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)

April 22, 2012 at 11:40 am

#1477203
RE: New to SQL

dwain.c (4/18/2012)
nhutlu,
Having been in your shoes about 1 year ago, I think I'm qualified to respond to you.
First of all, you need to gain a conceptual understanding of what 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)

April 22, 2012 at 11:37 am

#1477201
RE: grouped running totals

greg.bull (4/22/2012)
Thanks to you all for your comments and feedback. Jeff, I understood the purpose of your excellent article - but I'd probably have to use a very simple syntax...
--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)

April 22, 2012 at 10:56 am

#1477194
RE: Calculating Work Days

Apologies for the delayed response. Thank you for the feedback and for posting the function you built. It might help others having the same problem.
--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)

April 22, 2012 at 10:28 am

#1477191
RE: Bye Bye to the RAISERROR Function?

calbimonte.daniel (4/3/2012)
RAISERROR is nice, but MS had some problems with it and the try catch clauses and severities. If you are using them and you have sql 2k12, you should...
--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)

April 22, 2012 at 10:24 am

#1477190
RE: Best way to select a large amount of data?

ricardo_chicas (3/21/2012)
Thanks to all
we actually have a need for all that data, I solved it using custom batches
A least one other person asked what this means. Two way street...
--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)

April 22, 2012 at 10:20 am

#1477188
RE: Add varchar columns with colon in between to show time

I've been looking at this thread for a couple of days now and have decided that I don't know what the requirements are anymore. First, it start off with...
--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)

April 22, 2012 at 10:15 am

#1477187
RE: Are the posted questions getting worse?

The Dixie Flatline (4/20/2012)
venoym
Dixie: Ever heard of Empire?
Back in the DOS days, I wasted many an hour on Empire. Lately, my turn-based...
--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)

April 22, 2012 at 9:49 am

#1477183

Viewing 15 posts - 30,316 through 30,330 (of 59,101 total)