August 13, 2013 at 7:50 am
Thanks for following up and testing the different solutions Jeff - makes for interesting reading. The personal lesson for me from this is "test your code before posting". That way I could now be "N. North" rather than "N.North (corrected)".
Nick
August 13, 2013 at 8:40 am
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. 😉
...below is my latest in the 'compatible' format - perhaps you can add to your collection, Jeff. Thanks....David.
--===== Query by dmckinney ==========================================================
-- (http://www.sqlservercentral.com/Forums/FindPost1480386.aspx)
SELECT
[Month] = DATENAME(m,DATEADD(m,DATEDIFF(m,0,SomeDateTime)%12,0)),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011' --changed dates to same as elsewhere
GROUP BY DATEDIFF(m,0,SomeDateTime)%12
ORDER BY DATEDIFF(m,0,SomeDateTime)%12
August 13, 2013 at 10:22 pm
Yes, agree
April 13, 2019 at 2:42 am
How about adding the month(SomeDateTime) for the sorting correctly without affecting the group by?
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);
April 13, 2019 at 7:36 am
As this article has been resurrected from the distant past, how about amending the article to show the rather more efficient and slightly more obvious looking use of the MONTH() function instead?
As already shown in the much earlier comments by earlier commenters,
SELECT
DATENAME(mm,DATEFROMPARTS(1900,MONTH(SomeDateTime),1)),
Amount = SUM(SomeAmount)
FROM #MyHead
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
GROUP BY MONTH(SomeDateTime)
ORDER BY MONTH(SomeDateTime);
DATEFROMPARTS() is rather newer than the original article, however a similar trick was possible just less clear. Like anything, there are doubtless many ways of doing this and I expect there's a cleaner method that someone will demonstrate shortly...
April 14, 2019 at 3:31 pm
Thanks for the feedback folks.
To be honest, I should rewrite the whole, very old article. Using MONTH (which returns on 1-12 regardless of year) or anything else that doesn't observe the year of the data is just asking for trouble especially because the computational errors produced by a span of years will be totally silent.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2019 at 9:59 am
In my experience people abuse tempdb, please consider:
select [Month] = DATENAME(mm,SomeDateTime),
--[mm] =Â datepart(mm, SomeDateTime),
Amount = SUM(SomeAmount)
from
(
select  TOP (10000000)
SomeDateTime = RAND(CHECKSUM(NEWID()))*7305 + CAST('2005' AS DATETIME),
SomeAmount  = CAST(RAND(CHECKSUM(NEWID()))*100 AS MONEY)
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
) t
WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
group by DATENAME(mm,SomeDateTime), datepart(mm, SomeDateTime)
order by datepart(MONTH, SomeDateTime)
same result but faster and - for me - simplier
bye
April 18, 2019 at 1:13 pm
@S.bellini
Yes, I appreciate that a lot of people abuse TempDB and thanks for the feedback there. For these type of articles, though, I can't presume that it's safe to sometimes drop or even create a table and so I use Temp Tables for this type of testing.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 91 through 97 (of 97 total)
You must be logged in to reply to this topic. Login to reply