SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sorting Months By Number (SQL Spackle)


Sorting Months By Number (SQL Spackle)

Author
Message
pierre-702284
pierre-702284
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 143
Thanks for including my solution in the test results :-) I know it's not the fastest but it feels more "real world" because you usually need to take the year in to account as well as the month when producing monthly statistics. Nice article though :-)
Brad Schulz
Brad Schulz
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 299
Hi Jeff...

I appreciate you doing the time tests. Obviously I have to eat my words talking about "half the CPU". When I did comparisons between what I posted and the original code, that is, in fact, what I saw (in Profiler)... but it was very informal and was not as scientific as what you posted.

As I said, I hadn't read through the last 3 years of posts (I came into this because SQLServerCentral had your SQLSpackle post at the top of their email blast of Friday... it wasn't until I clicked on the link that I found the article was 3 years old). But it's interesting to see the myriad of alternate solutions that have come in. Now that you posted your recent time tests, I'm going to have a look at them all.

Best...

--Brad
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88436 Visits: 41128
pierre-702284 (8/5/2013)
Thanks for including my solution in the test results :-) I know it's not the fastest but it feels more "real world" because you usually need to take the year in to account as well as the month when producing monthly statistics. Nice article though :-)


Actually, I included yours because it was one of the few that included the year. I scaled it back to only use the same year of data as the others so we were comparing apples-to-apples for data but including the year is generally, as you say, a more real world solution. Thanks for posting it.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88436 Visits: 41128
Brad Schulz (8/5/2013)
Hi Jeff...

I appreciate you doing the time tests. Obviously I have to eat my words talking about "half the CPU". When I did comparisons between what I posted and the original code, that is, in fact, what I saw (in Profiler)... but it was very informal and was not as scientific as what you posted.

As I said, I hadn't read through the last 3 years of posts (I came into this because SQLServerCentral had your SQLSpackle post at the top of their email blast of Friday... it wasn't until I clicked on the link that I found the article was 3 years old). But it's interesting to see the myriad of alternate solutions that have come in. Now that you posted your recent time tests, I'm going to have a look at them all.

Best...

--Brad


Very cool. Thanks for stopping by, Brad. Your post reminnded me that I was lonng overdue on doinng such a test (which MVJ suggested quite a while back). A lot of good folks, including yourself, stepped up to the plate on trying to make things faster on this one. Ya just gotta love this commmunity! :-)

Hopefully, I'll find some time to resubmit the article with a link to the test post.

Thanks, again, Brad.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
aobermueller
aobermueller
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 1
I'd just alter the Order By and call it good.

---------------------------------------------------------

SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY DATENAME(mm,SomeDateTime)
ORDER BY MAX(DATEPART(mm,SomeDateTime))
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88436 Visits: 41128
aobermueller (8/7/2013)
I'd just alter the Order By and call it good.

---------------------------------------------------------

SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY DATENAME(mm,SomeDateTime)
ORDER BY MAX(DATEPART(mm,SomeDateTime))


Not sure what MAX will do to performance. You might want to test it using the test harness I posted a couple of posts back.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
aobermueller
aobermueller
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 1
Yeah, I can't seem to get the profiler to work on my machine, so i don't have a good way to test it.
mohit.sharma 99294
mohit.sharma 99294
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1
We can achieve same with date part.

SELECT [Month] = DATENAME(mm,Created_Date), Sale
FROM
(
SELECT Created_Date = '2012-12-01', Sale = 10000
UNION
SELECT Created_Date = '2012-02-01', Sale = 1000
UNION
SELECT Created_Date = '2012-03-01', Sale = 2500
UNION
SELECT Created_Date = '2012-04-01', Sale = 12000
UNION
SELECT Created_Date = '2012-05-01', Sale = 5500
UNION
SELECT Created_Date = '2012-06-01', Sale = 6500
UNION
SELECT Created_Date = '2012-07-01', Sale = 7500
UNION
SELECT Created_Date = '2012-08-01', Sale = 20000
UNION
SELECT Created_Date = '2012-09-01', Sale = 1500
UNION
SELECT Created_Date = '2012-10-01', Sale = 15000
UNION
SELECT Created_Date = '2012-11-01', Sale = 4000
UNION
SELECT Created_Date = '2012-01-01', Sale = 50000

) T1
ORDER BY DATEPART(MM,Created_Date)
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1339 Visits: 2090
-- another method, this time with datediff. (don't think it was covered before....apologies if it was.)

SELECT Created_Date, DATENAME(mm,Created_Date), Sale
FROM
(
SELECT Created_Date = '2012-12-01', Sale = 10000
UNION
SELECT Created_Date = '2012-02-01', Sale = 1000
UNION
SELECT Created_Date = '2012-03-01', Sale = 2500
UNION
SELECT Created_Date = '2012-04-01', Sale = 12000
UNION
SELECT Created_Date = '2012-05-01', Sale = 5500
UNION
SELECT Created_Date = '2012-06-01', Sale = 6500
UNION
SELECT Created_Date = '2012-07-01', Sale = 7500
UNION
SELECT Created_Date = '2012-08-01', Sale = 20000
UNION
SELECT Created_Date = '2015-09-01', Sale = 1500
UNION
SELECT Created_Date = '2012-10-01', Sale = 15000
UNION
SELECT Created_Date = '2012-11-01', Sale = 4000
UNION
SELECT Created_Date = '2012-01-01', Sale = 50000

) T1
ORDER BY DATEDIFF(m,0,Created_Date) % 12
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88436 Visits: 41128
mohit.sharma 99294 (8/13/2013)
We can achieve same with date part.

SELECT [Month] = DATENAME(mm,Created_Date), Sale
FROM
(
SELECT Created_Date = '2012-12-01', Sale = 10000
UNION
SELECT Created_Date = '2012-02-01', Sale = 1000
UNION
SELECT Created_Date = '2012-03-01', Sale = 2500
UNION
SELECT Created_Date = '2012-04-01', Sale = 12000
UNION
SELECT Created_Date = '2012-05-01', Sale = 5500
UNION
SELECT Created_Date = '2012-06-01', Sale = 6500
UNION
SELECT Created_Date = '2012-07-01', Sale = 7500
UNION
SELECT Created_Date = '2012-08-01', Sale = 20000
UNION
SELECT Created_Date = '2012-09-01', Sale = 1500
UNION
SELECT Created_Date = '2012-10-01', Sale = 15000
UNION
SELECT Created_Date = '2012-11-01', Sale = 4000
UNION
SELECT Created_Date = '2012-01-01', Sale = 50000

) T1
ORDER BY DATEPART(MM,Created_Date)


Yep. For single rows, that will work just fine. Try your code with a GROUP BY and a SUM on the Sale column and see what happens. ;-)

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search