Jeff Moden

Forum Replies Created

Viewing 15 posts - 36,046 through 36,060 (of 59,102 total)

RE: Sorting Months By Number (SQL Spackle)

sdorris-1150234 (11/15/2010)
All I can say is that when Jeff talks, I tend to listen. Thanks for the post, Jeff.
You bet. That's the best compliment someone could hope for. I'm...
--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)

November 16, 2010 at 12:19 am

#1251129
RE: Sorting Months By Number (SQL Spackle)

ejoell 66477 (11/15/2010)
IE would not permit horizontal scrolling to display either of your queries so I have no idea of what you are proposing. Shame too as I was...
--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)

November 16, 2010 at 12:13 am

#1251125
RE: Sorting Months By Number (SQL Spackle)

Robert Dudley (11/15/2010)
I made use of the built-in datepart functions available in SQL, as many of you also demonstrated. I used a sub-query to obtain the same results. If 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)

November 16, 2010 at 12:08 am

#1251123
RE: Sorting Months By Number (SQL Spackle)

Ola L Martins-329921 (11/15/2010)
Steve Jones - SSC Editor (11/15/2010)
I'm not sure why someone woud vote this down if they knew this technique. Are you saying that all articles about things...
--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)

November 16, 2010 at 12:01 am

#1251121
RE: Sorting Months By Number (SQL Spackle)

Michael Valentine Jones (11/15/2010)
There have been a lot of alternative suggestions posted, so maybe it’s time for a performance test… 🙂
FYI: I never vote on anything, so don't blame...
--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)

November 15, 2010 at 11:57 pm

#1251120
RE: Sorting Months By Number (SQL Spackle)

manub22 (11/15/2010)
How about this:
select datename(M,convert(varchar,months)+'/01/2010') as days
from (select 1 as months union
select 2 as months union
select 3 as months union
select 4 as months union
select 5 as months union
select 6 as...
--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)

November 15, 2010 at 11:55 pm

#1251119
RE: Sorting Months By Number (SQL Spackle)

Dugi (11/15/2010)
sdorris-1150234 (11/15/2010)
All I can say is that when Jeff talks, I tend to listen. Thanks for the post, Jeff.
I vote for this ... Jeff, thanks for the nice article!...
--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)

November 15, 2010 at 11:51 pm

#1251117
RE: Sorting Months By Number (SQL Spackle)

Dean Cochrane (11/15/2010)
I don't know why people would mark this article down, but people are funny sometimes.
Even though I probably won't use this technique (I can't think of a time...
--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)

November 15, 2010 at 11:44 pm

#1251116
RE: Sorting Months By Number (SQL Spackle)

Geoff A (11/15/2010)
Kristian Ask (11/15/2010)
hugo-939487 (11/15/2010)
Another variaton, using the MONTH function:
SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
...
--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)

November 15, 2010 at 11:39 pm

#1251112
RE: Sorting Months By Number (SQL Spackle)

Grant Fritchey (11/15/2010)
Sweet. I like it. I like the format and I think it's perfect that it was lead out of the gate by Jeff. Nice, direct and to 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)

November 15, 2010 at 11:30 pm

#1251109
RE: Sorting Months By Number (SQL Spackle)

Trey Staker (11/15/2010)
Ola L Martins-329921 (11/15/2010)
Maybe the low ratings is due to the fact that most programmers and db-developers "know" this solution already: The old "sort numbers stored as text...
--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)

November 15, 2010 at 11:28 pm

#1251108
RE: Sorting Months By Number (SQL Spackle)

hugo-939487 (11/15/2010)
Another variaton, using the MONTH function:
SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >=...
--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)

November 15, 2010 at 11:26 pm

#1251106
RE: Sorting Months By Number (SQL Spackle)

Kristina-868114 (11/15/2010)
You can also add a grouping on MONTH(SomeDateTime) without adding it to the SELECT list. It won't actually change the grouping at all, since grouping on month name...
--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)

November 15, 2010 at 11:21 pm

#1251105
RE: Sorting Months By Number (SQL Spackle)

james.wheeler10 (11/15/2010)
What happens when you're reporting on a period greater than a year?
Won't both of the overlapping month's figures will be aggregated into a single month aggregate total?
Absolutely correct....
--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)

November 15, 2010 at 11:14 pm

#1251104
RE: Sorting Months By Number (SQL Spackle)

Henk Schreij (11/15/2010)
Jeff Moden (11/15/2010)
Still, it would be nice to hear back from those folks that gave the lower ratings . . .
I didn't give a low rating, but...
--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)

November 15, 2010 at 11:07 pm

#1251101

Viewing 15 posts - 36,046 through 36,060 (of 59,102 total)