Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910»»

Sorting Months By Number (SQL Spackle) Expand / Collapse
Author
Message
Posted Monday, August 5, 2013 2:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 6, 2014 4:34 AM
Points: 40, 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
Post #1480830
Posted Monday, August 5, 2013 10:50 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:45 PM
Points: 20, Visits: 236
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
Post #1481005
Posted Monday, August 5, 2013 12:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1481037
Posted Monday, August 5, 2013 12:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1481040
Posted Wednesday, August 7, 2013 9:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 3:26 PM
Points: 2, 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))
Post #1481929
Posted Wednesday, August 7, 2013 3:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1482079
Posted Wednesday, August 7, 2013 3:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 3:26 PM
Points: 2, 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.
Post #1482086
Posted Tuesday, August 13, 2013 12:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 10:22 PM
Points: 2, 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)



Post #1483575
Posted Tuesday, August 13, 2013 12:28 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:54 AM
Points: 648, Visits: 1,876
-- 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
Post #1483582
Posted Tuesday, August 13, 2013 7:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 35,372, Visits: 31,919
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1483728
« Prev Topic | Next Topic »

Add to briefcase «««678910»»

Permissions Expand / Collapse