Jeff Moden

Forum Replies Created

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

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
RE: Sorting Months By Number (SQL Spackle)

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 as numbers"......
--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 10:52 pm

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

Pete Cox (11/15/2010)
Hi Jeff,
I might consider using something like this approach
CREATE TABLE [dbo].[Months](
[MonthName] [nvarchar](10) NOT NULL,
[MonthCalendarSequence] [int] NOT NULL,
[MonthFinancialSequance] [int] NOT NULL
) ON [PRIMARY]
Insert into Months Values ('January',1,10)
Insert into Months...
--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 10:48 pm

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

N.North (11/15/2010)
You could go for casting the month numbers to names in the select rather than in the ORDER BY clause, as it allows you to use MONTH in most...
--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 10:30 pm

#1251097

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