August 2, 2013 at 6:09 pm
I'm afraid I didn't read through the last 3 years of comments, but when I'm faced with this kind of thing, I do the following, which takes half the CPU of the solution posted in the article (because it doesn't have to do the CASTing conversions... and perhaps because it GROUPs BY a date instead of a string):
select [Month]=datename(month,FirstOfMonth)
,Amount=sum(SomeAmount)
from #MyHead
cross apply (select FirstOfMonth=dateadd(month,datediff(month,'19000101',SomeDateTime),'19000101')) F_Mth
where SomeDateTime>='2010' and SomeDateTime<'2011'
group by FirstOfMonth
order by FirstOfMonth
The CROSS APPLY introduces a new column called FirstOfMonth, which is just the first of the month of the date.
Then you can use that to do the GROUP BY and the ORDER BY, and just do the DATENAME() in the SELECT.
And this will work across more than just a year... just add DATENAME(year,FirstOfMonth) to the SELECT.
--Brad
August 3, 2013 at 3:25 pm
janis.l.murphy (8/2/2013)
I really appreciated this post. I don't think I've ever needed to do this sort of thing, but the solution was a lot cleaner than what I might have come up with on my own. So, thanks for sharing. One thing I've never seen is theCAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME)construct. I didn't know that you could use CAST to change a month and year string to DATETIME. That was my key take away from this.
Thanks for stopping by and the "takeaway", Janis.
It's actually pretty amazing what kind of character based date you can throw as SQL Server and have it auto-magically translate to a DATETIME datatype. For the most part just about any format listed under CONVERT will be translated without extra code and oddities like "January 2013' will also be automatically translated. Of course, big problems occur if you mix "base" date formats such as when you give it something that follows a mix of MDY and DMY formats.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2013 at 4:19 pm
Wow! How time flies. I can't believe it's been almost 3 years since I started writing these short "SQL Spackle" articles and that people are still participating in the discussion after all of that time.
And, as with many articles, the "Discussion" is where all of the goodies truly lay. I've been meaning to update the article with one of the faster methods (you learn a lot in 3 years) but just never got around to it.
Without further ado, I setup a test harness for most of the methods that you good folks offered and thought I'd share the results.
First, here's the original data setup from the article.
--=============================================================================
-- Create the test data. This is NOT a part of the solution.
-- Usually takes something less than 12 seconds to generate.
--=============================================================================
--===== Conditionally drop the test table to make reruns easier.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
;
GO
--===== Create the table and populate it on the fly.
-- This builds a table with random dates and amounts for 20 years starting
-- in the year 2005.
SELECT TOP (1000000)
SomeDateTime = RAND(CHECKSUM(NEWID()))*7305 + CAST('2005' AS DATETIME),
SomeAmount = CAST(RAND(CHECKSUM(NEWID()))*100 AS MONEY)
INTO #MyHead
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Add a clustered index to the table just to keep it from being a heap
-- and to help with speed a bit by sorting the data.
CREATE CLUSTERED INDEX IX_#MyHead_SomeDateTime
ON #MyHead (SomeDateTime)
;
I also setup the special lookup table that Peter Cox needed for his code including the clustered index he later mentioned.
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 Values ('February',2,11)
Insert into Months Values ('March',3,12)
Insert into Months Values ('April',4,1)
Insert into Months Values ('May',5,2)
Insert into Months Values ('June',6,3)
Insert into Months Values ('July',7,4)
Insert into Months Values ('August',8,5)
Insert into Months Values ('September',9,6)
Insert into Months Values ('October',10,7)
Insert into Months Values ('November',11,8)
Insert into Months Values ('December',12,9)
CREATE CLUSTERED INDEX IX_Months ON dbo.Months (MonthName)
Here's the test harness that I used. I ran SQL Profiler while this was running. The profiler was setup to only capture info from the SPID I was running from (on a quiet laptop) and captured the usual stuff for RPC COMPLETE like Duration, CPU, Reads, Writes, RowCounts, etc. The "Baseline" run is just "prime the pump" and to give some idea of what we might shoot for. The DATEADD should probably be removed because that's actually a form of "formatting" in this case but I didn't want people to be so concerned when they saw non-dates.
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Baseline Aggregate Only ==========================================================
SELECT [Month] = DATEADD(mm, DATEDIFF(mm,'1753',SomeDateTime),'1753'),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY DATEDIFF(mm,'1753',SomeDateTime)
ORDER BY DATEDIFF(mm,'1753',SomeDateTime)
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Original Query from the Article =================================================
SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY DATENAME(mm,SomeDateTime)
ORDER BY CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME)
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by N. North (corrected) ================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1020596.aspx)
SELECT [Month] = DATENAME(mm, DATEADD(mm, MONTH(SomeDateTime), 0) -1),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY MONTH(SomeDateTime)
ORDER BY MONTH(SomeDateTime)
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by Pete Cox ===============================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1022014.aspx)
SELECT Amount, [Month]
from
(
SELECT
SUM(SomeAmount) AS Amount,
DATENAME(mm,SomeDateTime) AS [Month]
FROM
#MyHead
WHERE
SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY
DATENAME(mm,SomeDateTime)
) daa
INNER JOIN
months
ON
[monthname] = [Month]
ORDER BY
months.monthcalendarsequence
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by hugo-939487 ============================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1020625.aspx)
SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY DATENAME(mm,SomeDateTime), MONTH(SomeDateTime)
ORDER BY MONTH(SomeDateTime)
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by Robert Dudley ==========================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1020762.aspx)
SELECT
[Month]
,[Amount] = SUM([Amount])
FROM
(
SELECT
[SortMonth] = MONTH(SomeDateTime)
,[Month] = DATENAME(mm,SomeDateTime)
,[Amount] = SomeAmount
FROM #MyHead
WHERE YEAR(SomeDateTime) = 2010
) a
GROUP BY [Month], [SortMonth]
ORDER BY [SortMonth]
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by Natalya Bankova ========================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1020819.aspx)
SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY DATENAME(mm,SomeDateTime), DATEPART(MM, SomeDateTime)
ORDER BY DATEPART(MM, SomeDateTime)
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by Michael Valentine Jones ================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1020835.aspx)
select
[Month] = datename(mm,SomeDateTime),
Amount = sum(SomeAmount)
from
#MyHead
where
SomeDateTime >= '2010' AND SomeDateTime < '2011'
group by
datename(mm,SomeDateTime),
dateadd(mm,datediff(mm,0,SomeDateTime),0)
order by
dateadd(mm,datediff(mm,0,SomeDateTime),0)
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by kristinam =============================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1020858.aspx)
SELECT [Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY DATENAME(mm,SomeDateTime), MONTH(SomeDateTime)
ORDER BY Month(SomeDateTIme)
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by hallidayd ============================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1020888.aspx)
SELECT [Month] = DATENAME(mm,SomeDateTime)
, Amount = SUM(SomeAmount)
FROM #MyHead --Changed name of table to what was being tested in the article
WHERE SomeDateTime >= '2010'
AND SomeDateTime < '2011'
GROUP BY
DATENAME(mm,SomeDateTime)
ORDER BY
CHARINDEX(DATENAME(mm,SomeDateTime), 'JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember');
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by the apatel62 ==========================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1480386.aspx)
SELECT [Year] = DATENAME(yyyy,SomeDateTime),
[Month] = DATENAME(mm,SomeDateTime),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011' --changed dates to same as elsewhere
GROUP BY DATENAME(yyyy,SomeDateTime),DATEPART(m,SomeDateTime),DATENAME(mm,SomeDateTime)
ORDER BY 1,DATEPART(m,SomeDateTime)
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by the pierre-702284 ======================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1480425.aspx)
;WITH [Data] AS (
SELECT
[SomeMonth] = DATEADD(month, DATEDIFF(month, 0, [SomeDateTime]), 0),
[SomeAmount]
FROM [#MyHead]
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011' --add this to be same as others
)
SELECT
[Month] = RIGHT(CONVERT(VARCHAR(11), [SomeMonth], 106), 8),
[Amount] = SUM([SomeAmount])
FROM [Data]
GROUP BY [SomeMonth]
ORDER BY [SomeMonth]
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by the sqlist =============================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1480433.aspx)
SELECT [Month] = datename(mm,min(SomeDateTime)),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY month(SomeDateTime)
ORDER BY month(SomeDateTime)
;
GO 10
--=======================================================================================
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO
--===== Query by the Brad Schulz ========================================================
-- ()
select [Month]=datename(month,FirstOfMonth)
,Amount=sum(SomeAmount)
from #MyHead
cross apply (select FirstOfMonth=dateadd(month,datediff(month,'19000101',SomeDateTime),'19000101')) F_Mth
where SomeDateTime>='2010' and SomeDateTime<'2011'
group by FirstOfMonth
order by FirstOfMonth
;
GO 10
Notice that each piece of code was executed 10 times. The first run would return times as if the code were not in cache and the other 9 times run as if it was. On today's decent systems, the system will likely keep such code in cache if there's regular usage. All times are in milli-seconnds in the following results.
Then I ran the following code to rank the code by "Total Duration": Here's the code followed by the sorted results.
SELECT Ranking = RANK() OVER (ORDER BY SUM(Duration)/1000),
CodeBy = SUBSTRING(TextData,1,47),
MinDur = MIN(Duration)/1000,
AvgDur = AVG(Duration)/1000,
MaxDur = MAX(Duration)/1000,
TotalDur = SUM(Duration)/1000,
MinCPU = MIN(CPU),
AvgCPU = AVG(CPU),
MaxCPU = MAX(CPU),
TotalCpu = SUM(CPU)
FROM dbo.SortMonth_Trace
WHERE RowCounts = 12
GROUP BY SUBSTRING(TextData,1,47)
ORDER BY TotalDur
;
Results from the code above...
Ranking CodeBy MinDur AvgDur MaxDur TotalDur MinCPU AvgCPU MaxCPU TotalCpu
------- ----------------------------------------------- ------ ------ ------ -------- ------ ------ ------ --------
1 --===== Query by N. North (corrected) ========= 15 16 22 161 15 17 32 171
2 --===== Baseline Aggregate Only =============== 15 23 77 231 15 15 16 156
3 --===== Query by the sqlist =================== 22 23 30 239 15 23 32 234
4 --===== Query by the pierre-702284 ============ 25 26 34 269 16 26 31 265
5 --===== Query by hallidayd ================== 25 27 31 270 15 25 32 250
6 --===== Original Query from the Article ======= 26 28 50 285 15 28 32 281
7 --===== Query by Pete Cox ===================== 25 30 75 309 15 26 32 266
8 --===== Query by hugo-939487 ================== 34 35 40 357 31 34 47 343
9 --===== Query by the Brad Schulz ============== 35 37 43 372 31 32 46 328
10 --===== Query by kristinam =================== 34 37 57 373 31 39 47 390
11 --===== Query by Natalya Bankova ============== 34 40 85 401 31 34 47 344
12 --===== Query by the apatel62 ================ 45 46 52 460 46 46 47 468
13 --===== Query by Michael Valentine Jones ====== 49 54 95 546 46 49 62 499
14 --===== Query by Robert Dudley ================ 92 133 312 1331 140 190 233 1903
(14 row(s) affected)
Of course, we also want to know the results by "TotalCPU" for the 10 runs so here's the code and the results for that.
SELECT Ranking = RANK() OVER (ORDER BY SUM(CPU)),
CodeBy = SUBSTRING(TextData,1,47),
MinDur = MIN(Duration)/1000,
AvgDur = AVG(Duration)/1000,
MaxDur = MAX(Duration)/1000,
TotalDur = SUM(Duration)/1000,
MinCPU = MIN(CPU),
AvgCPU = AVG(CPU),
MaxCPU = MAX(CPU),
TotalCpu = SUM(CPU)
FROM dbo.SortMonth_Trace
WHERE RowCounts = 12
GROUP BY SUBSTRING(TextData,1,47)
ORDER BY TotalCpu
;
Results from code above:
Ranking CodeBy MinDur AvgDur MaxDur TotalDur MinCPU AvgCPU MaxCPU TotalCpu
------- ----------------------------------------------- ------ ------ ------ -------- ------ ------ ------ --------
1 --===== Baseline Aggregate Only =============== 15 23 77 231 15 15 16 156
2 --===== Query by N. North (corrected) ========= 15 16 22 161 15 17 32 171
3 --===== Query by the sqlist =================== 22 23 30 239 15 23 32 234
4 --===== Query by hallidayd ================== 25 27 31 270 15 25 32 250
5 --===== Query by the pierre-702284 ============ 25 26 34 269 16 26 31 265
6 --===== Query by Pete Cox ===================== 25 30 75 309 15 26 32 266
7 --===== Original Query from the Article ======= 26 28 50 285 15 28 32 281
8 --===== Query by the Brad Schulz ============== 35 37 43 372 31 32 46 328
9 --===== Query by hugo-939487 ================== 34 35 40 357 31 34 47 343
10 --===== Query by Natalya Bankova ============== 34 40 85 401 31 34 47 344
11 --===== Query by kristinam =================== 34 37 57 373 31 39 47 390
12 --===== Query by the apatel62 ================ 45 46 52 460 46 46 47 468
13 --===== Query by Michael Valentine Jones ====== 49 54 95 546 46 49 62 499
14 --===== Query by Robert Dudley ================ 92 133 312 1331 140 190 233 1903
(14 row(s) affected)
Some interesting things there. I corrected N.North's code so that it would return the correct month name. I'm pretty happy I did that instead of just ignoring it as "code that didn't produce the correct answer".
Also and in all fairness to Robert Dudley, he did come right out and say that his code wasn't going to be the most efficient but that he likes doing things the way he did. Ya gotta have fun doing this type of thing or you'll go nuts. 😛
I also ranked CPU time of the "first run" for each code snippet using the following code...
WITH
cteEnumerate AS
(
SELECT CodeByRowNum = ROW_NUMBER() OVER (PARTITION BY SUBSTRING(TextData,1,47) ORDER BY RowNumber),
CodeBy = SUBSTRING(TextData,1,47),
Dur = Duration/1000,
CPU
FROM dbo.SortMonth_Trace
WHERE RowCounts = 12
)
SELECT Ranking = RANK() OVER (ORDER BY Cpu),
CodeBy,
Dur,
CPU
FROM cteEnumerate
WHERE CodeByRowNum = 1
ORDER BY Ranking
;
Here are the results for that...
Ranking CodeBy Dur CPU
------- ----------------------------------------------- --- ---
1 --===== Query by N. North (corrected) ========= 22 15
2 --===== Baseline Aggregate Only =============== 77 16
2 --===== Query by hallidayd ================== 31 16
2 --===== Query by Pete Cox ===================== 75 16
5 --===== Query by the pierre-702284 ============ 34 31
5 --===== Query by hugo-939487 ================== 40 31
5 --===== Original Query from the Article ======= 50 31
8 --===== Query by Natalya Bankova ============== 85 32
8 --===== Query by the sqlist =================== 30 32
8 --===== Query by the Brad Schulz ============== 43 32
11 --===== Query by the apatel62 ================ 52 47
11 --===== Query by kristinam =================== 57 47
13 --===== Query by Michael Valentine Jones ====== 95 62
14 --===== Query by Robert Dudley ================ 312 172
(14 row(s) affected)
I did these tests using SQL 2008 (NOT R2) Developer's Edition (sp3) on a VAIO laptop with 4 core (i5) running at 2.5GHz, 6GB ram, and Windows 7 Home Premium (sp1).
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2013 at 4:24 pm
Brad Schulz (8/2/2013)
I'm afraid I didn't read through the last 3 years of comments, but when I'm faced with this kind of thing, I do the following, which takes half the CPU of the solution posted in the article (because it doesn't have to do the CASTing conversions... and perhaps because it GROUPs BY a date instead of a string):
select [Month]=datename(month,FirstOfMonth)
,Amount=sum(SomeAmount)
from #MyHead
cross apply (select FirstOfMonth=dateadd(month,datediff(month,'19000101',SomeDateTime),'19000101')) F_Mth
where SomeDateTime>='2010' and SomeDateTime<'2011'
group by FirstOfMonth
order by FirstOfMonth
The CROSS APPLY introduces a new column called FirstOfMonth, which is just the first of the month of the date.
Then you can use that to do the GROUP BY and the ORDER BY, and just do the DATENAME() in the SELECT.
And this will work across more than just a year... just add DATENAME(year,FirstOfMonth) to the SELECT.
--Brad
Hi Brad. Thanks for stopping by.
In light of the test results I got and published in the post above, I'd have to see your test harness to figure out why you and I got such a difference. Despite the fact that the article code uses character based concatenation, the tests I did still show it beating the CROSS APPLY method you were kind enough to take the time to post.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2013 at 4:38 pm
David McKinney (8/2/2013)
Jeff,Before my post, I did in fact (quickly) read through all the comments...and the month function was mentioned a number of times already. I didn't see any added value in requoting it....but yes, I do realise that you also have to include it in the 'group by' if that's what you're referring to.
One of your previous comments refers to the sort not happening correctly when using the month function...in fact that's not the case...the sort works fine. In the post you were referencing, it was in fact the month in the select which was incorrect (showing February for January etc..) hence resulting in what appeared to be an incorrect sort.
I should state that my post was an attempt to respond to your very first post, where you ask 'why the low score?' (My post wasn't specifically supposed to be about the month function.)
I should say first that I haven't rated the article today, and don't recall whether I saw / rated the article first time around. But I don't believe I would have scored it poorly - I'd probably have abstained.
I would have abstained, because of the huge respect I have for you on this and other sites. I always love to read what you have to say. For the quality, content, completeness and sheer volume of your articles and forum replies. It's no secret that you're held in uniquely high esteem, by most people who frequent this site. However, it's not because you're often considered a sql god, that I have to worship you, or that I'll be blaspheming if I dare to disagree or challenge. I reserve the right to find some of the things you say, on rare occasions, off the mark - and to say so!
So please take no offence at my 'criticism'...I was attempting just to answer the question you asked.
Hope this helps to clarify.
David.
Thanks for the kind feedback, David. Gosh... I hope no one thinks of me as an "SQL God". I'm just a "Joe bag-o-donuts" trying to payback the community that has enriched my life so much in so many ways. I learn more from the discussions that people have on my articles than I could ever hope to learn by writing them. So many people with good ideas.
Just to close the circle and, perhaps, reiterate... my question about the points wasn't about the points. It was because there was absolutely no feedback as to why (I even included that in the post). I hate "drive-by shootings". No one learns that way and that's the ultimate goal of these articles even when someone comes up with a better idea.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2013 at 2:07 am
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 🙂
August 5, 2013 at 10:51 am
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
August 5, 2013 at 12:30 pm
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
Change is inevitable... Change for the better is not.
August 5, 2013 at 12:35 pm
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
Change is inevitable... Change for the better is not.
August 7, 2013 at 9:11 am
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))
August 7, 2013 at 3:25 pm
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
Change is inevitable... Change for the better is not.
August 7, 2013 at 3:38 pm
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.
August 13, 2013 at 12:20 am
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)
August 13, 2013 at 12:28 am
-- 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
August 13, 2013 at 7:30 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 76 through 90 (of 97 total)
You must be logged in to reply to this topic. Login to reply